Today I want to show you
four hidden Excel tips and tricks that will help you create beautiful Excel dashboards or Excel reports really fast. (lively music) I'm really curious though
to know which of these tips, if any, are new for you and which of these you're
already using in your files. Comment below and let me know. And if you also have any
hidden Excel tips or tricks, share it with us below as well. But before we get started, I'd like to quickly thank Skillshare for sponsoring this video. Skillshare is an online learning community for life-long learners like myself, and probably yourself since
you're watching this video. I'm going to chat more about
them towards the end though, so stay tuned for that. Now let's get to our four tips. Tip number one. Convert SmartArt to shapes. So let's say this is the
canvas of my dashboard. What I want to do is add in some shapes that are connected to cell values. So let's say these are
important information, I want to highlight them
and bring attention to them on my dashboard. Now, my numbers are sitting
on the Calculations tab. I have sales information
for three different apps. On the dashboard side, I want to show the sales
for each of these app for the first half of the year. Now, I could go and insert some shapes from the Illustrations group here but I can also insert really nice shapes by going to the SmartArt feature. Here I get access to more professional and more complex shapes. I'm inside the All tab here. So let's go to one of these subcategories and insert one of these shapes. I'll go with this one and click on OK. Now, I get a new tab pop
up here on the ribbon for SmartArt Design. I have the ability to make
some adjustment to this. So let's actually update the color and use a nicer color combination. The disadvantage of using SmartArt is that you can't connect
it to cell values. So I can't click on this box here and then directly go to the formula bar and type in an equals sign and then put in a cell reference. But what I can do is to
convert this to shapes. So just click on the border of the SmartArt, right mouse click, select Convert to Shapes. This becomes a grouped shape, so if you want to ungroup it, go ahead, right mouse click, Group and then Ungroup. Now, these become individual shapes. Now, because they're no longer SmartArt, I can connect them to
a specific cell value. So now with the box selected, directly go to the formula bar, type in an equal, then go to the cell where you want the value from. So I'm going to get this value and press Enter. Let's do the same for
the first shape here. So just click on the border of the shape, don't click inside the
shape, just on the border, then directly go to the formula bar, type in an equals and then select what you want. Now we can format these as we like. Just click on the border and adjust the font as you see fit. And then you can organize this as you like on your dashboard. Tip number two. Hide unused rows and columns. When you finalize your dashboard, it's good to restrict the view for the user so that they can't
use the cells on the side. So let's say you've put
all the information here and you just want their
attention to stay here. You can hide anything that's unused. So select the first
column that you don't need and then use the shortcut
key Control + Shift and the right arrow. That's going to select all the columns, right mouse click and hide these. Now you've restricted the columns to only this area. They can't click away and they can't scroll to the side. Do the same for the rows. Control + Shift + down,
right mouse click, and Hide. Number three, select objects
from the Selection Pane. Once you have a lot of
objects on your dashboard, sometimes it becomes difficult to select them from here. What you can do is bring
up the Selection Pane by going to Home, Find &
Select, Selection Pane. Here you can see all the
shapes that you have. You can also hide shapes from view by clicking on this I icon. So let's say you're testing if something looks better here or whether this looks better here, you can position them where you want and then hide the object and see how it looks. It's also easier to
select multiple objects by using the Control key on here. So if I want to select a few shapes here, I can select it from the Selection Pane and then hold down Control and select the other shapes as well. Tip number four. Use a linked picture to overcome cell boundaries. Let's say we want to
add a dropdown to here where we can select the different apps. And what we want to see on the side here is the sales trend. So basically, we want
to see this trend based on the app that the user selected. We're going to do that with a sparkline. Let's set that up really quickly. I'm just going to go to
insert a data validation here. So let's go to Data, Data Validation, List and for Source, that's going
to be these three apps and OK. Now when someone selects Blend, I want to see the sales
trend for Blend right here. Well, first off, on the calculation side, I need to somehow figure out what the user selected. So let's say this is equals whatever the user selected here. Now that I have that information, I can grab the data that I need from my source data. So let's go old school and use the INDEX function. The array is basically
the range of my answers. That can be any of these. I don't need to fix this because when I pull this across, I want it to shift as well. Now, for the row argument, I need to figure out how
many rows to move here. So basically, what the user selected. Here I can use MATCH, my lookup_value is this. And I need to fully fix this because I don't want
that reference to shift. My lookup_array is right here. Again, I need to fully fix this and my match_type is zero. Close bracket and press Enter. We need to have a second bracket. Let's go with Yes and that's our value. Now, when I pull this across, I get the correct values for Blend here. Now that I have that set up, let's add a sparkline. Go to Insert, from the Sparkline options, let's go with line. My data range is this one. Location is good and go with OK. That's the standard sparkline. I can update this by
making it a bit thicker and also adjusting the color. So let's go with a yellow version. Now we can also add a low point and a high point here and adjust the colors for those as well. So for the High Point, I
want it to be light green and for the Low Point, let's go with red. Okay, so we want to have
this sparkline shown on our dashboard. But we don't want it to be
restricted to the cell size here on the dashboard because let's assume I
can't adjust the size here. I need this cell to be this size. I might want to have a table down here and I can't expand this
appropriately for the sparkline. So what I'm going to do is
to insert this sparkline as an image. I'm just going to copy it, so Control + C, go to the dashboard side and then go to the Paste options here and insert it as a linked picture. Now I'm independent to
the size of the cell because it's now an object,
a picture that's sitting on top of the cell. But the good thing is this is dynamic. It's not just a one-time picture but it's basically a live picture that reflects whatever is in this cell. Okay, so when I change
this now again to Voltage, I can see the trend for Voltage and then I'm flexible enough to position this where
I like in the dashboard. As an alternative, you can
of course use a line chart but it will take some more
time to set up the min and max dynamic formatting. Using sparklines and linked pictures can save you some time here. These were my tips and tricks when it comes to designing dashboards. Now, for me personally, good design doesn't come naturally. I don't really have that feeling of what works good together. I had to educate myself. Now, if you'd like to learn more about best practice data
visualization techniques, I can recommend the class Introduction to Data Visualization: From Data to Design,
which is on Skillshare. The course is by Nicolas Falton who is an information designer. Now, he also mentions in the class that what separated him
from the other people who also had technical skills were his design skills. My favorite lectures
are the ones on color, typography, and designing the layout. So check it out if you're interested to improve your data visualization skills. I have a special link for
you in the description of the video. Click that link, become a member and you're going to get two free months of premium membership to all the classes on Skillshare, so not just this one. After the two months are over, if you'd like to continue
with your premium membership, it costs less than $10 a month if you get the annual subscription. You have a huge variety
of topics to choose from. For example, you have graph design, you have web development, marketing, productivity, office
tools and so much more. Thanks to Skillshare for
sponsoring this video and supporting my channel. If you enjoyed this, give it a thumbs up and if you want to improve your skills, consider subscribing to this channel. Thank you for watching
and I'm going to see you in the next video. (lively music)
