4 Hidden Excel Dashboard Design Tips for Beautiful Reports

Today I want to show you four

hidden

Excel

tips

and tricks that will help you create

beautiful

Excel

dashboard

s or Excel

reports

very quickly. (upbeat music) However, I'm really curious to know which of these

tips

, if any, are new to you and which ones 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 them with us below. 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 me, and probably yourself since you're watching this video.

I'll talk more about them towards the end, though, so stay tuned. Now let's get to our four tips. Tip number one. Convert SmartArt to shapes. So let's say this is my

dashboard

canvas. What I want to do is add some shapes that are connected to the cell values. So let's say this is important information, I want to highlight it and draw attention to it on my dashboard. Now, my numbers are in the Calculations tab. I have sales information for three different applications. On the dashboard side, I want to show the sales of each of these apps 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 some really nice shapes by going to the SmartArt feature. Here I have access to more professional and more complex forms. 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 OK. A new tab now appears here in the ribbon for SmartArt Design. I have the ability to make some adjustments to this. So let's update the color and use a nicer color scheme. The downside to using SmartArt is that you can't connect it to cell values.

So I can't click on this box here and then go directly to the formula bar and type an equal sign and then put in a cell reference. But what I can do is convert this into shapes. So just click on the edge of the SmartArt, right-click and select Convert to Shapes. This becomes a grouped shape, so if you want to ungroup it, go ahead and right-click, Group, and then Ungroup. Now, these become individual shapes. Now, since they are no longer SmartArt, I can connect them to a specific cell value. So now with the box selected, go directly to the formula bar, type equals, and then go to the cell where you want to get the value from.

So I'll get this value and press Enter. Let's do the same with the first shape here. So just click on the edge of the shape, don't click inside the shape, just on the edge, then go straight to the formula bar, type equals, and then select whatever you want. Now we can format them however we want. Simply click on the border and adjust the font as you see fit. And then you can organize this however you want in your dashboard. Tip number two. Hide unused rows and columns. When you finish your panel, it is good to restrict the view for the user so that they cannot use the side cells.

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 is not used. So select the first column that you don't need and then use the shortcut key Control + Shift and the right arrow. That will select all the columns, right click and hide them. You have now restricted the columns to this area only. They cannot click or scroll to the side. Do the same with the rows. Control + Shift + Down, right mouse click and Hide. Number three, select objects from the Selection Panel. Once you have a lot of objects on your board, it is sometimes difficult to select them from here.

What you can do is open the Selection Panel by going to Start, Search and Select, Selection Panel. Here you can see all the shapes you have. You can also hide shapes from view by clicking this I icon. So let's say you're testing if something looks better here or if this looks better here, you can place them where you want and then hide the object and see how it looks. It's also easier to select multiple objects using the Control key here. So if I want to select some shapes here, I can select them from the Selection Panel and then hold Control and select the other shapes as well.

Tip number four. Use a linked image to overcome cell boundaries. Let's say we want to add a dropdown menu here where we can select the different applications. And what we want to see here is the sales trend. Basically, we want to see this trend based on the app the user selected. We will do it with a sparkline. Let's set that up real quick. I'm just going to insert a data validation here. So let's go to Data, Data Validation, List and Source, it's going to be these three applications and okay. Now, when someone selects Blend, I want to see the sales trend of Blend right here.

Well, first of all, on the calculation side, I need to somehow find out what the user selected. So let's say this is equal to what the user selected here. Now that I have that information, I can get the data I need from my source data. So let's go old school and use the INDEX function. The matrix is ​​basically the range of my answers. It can be any of these. I don't need to fix this because when I cross it, I want it to move too. Now for the row argument, I need to calculate how many rows to move here.

Basically, what the user selected. Here I can use MATCH, my lookup value is this. And I need to fix this completely because I don't want that reference to change. My lookup_array is here. Again, I need to fix this issue completely and my match type is zero. Close the square bracket and press Enter. We need to have a second support. We go with Yes and that is our value. Now when I cross this, I get the correct values ​​for Blend here. Now that I have it set up, let's add a sparkline. Go to Insert, from the Sparkline options, let's go with line.

My data range is this. The location is good and go with OK. That's the standard sparkline. I can update this by making it a little thicker and also adjusting the color. So let's go with a yellow version. Now we can also add a low crochet and a high crochet here and adjust the colors for them as well. So for the high point, I want it to be light green and for the low point, let's use red. Okay, so we want this sparkline to display in our dashboard. But we don't want it to be restricted to the cell size here on the dashboard because let's say I can't adjust the size here.

I need this cell to be this size. You might want to have a table down here and I can't expand it properly for the sparkline. So what I'm going to do is insert this sparkline as an image. I'm just going to copy it, so Control + C, go to the side of the board and then go to the Paste Here options and insert it as a linked image. Now I am independent of the size of the cell because now it is an object, an image that is on top of the cell. But the good thing is that this is dynamic.

It's not just a single image, but basically a live image that reflects what's in this cell. Okay, when I change this back to Voltage, I can see the Voltage trend and then I'm flexible enough to place it wherever I want on the board. Alternatively, you can, of course, use a line chart, but it will take more time to set up the dynamic minimum and maximum format. Using sparklines and linked images can save you some time. These were my tips and tricks when

design

ing panels. Now, for me personally, good

design

doesn't come naturally. I don't really get that feeling that it works well together.

I had to educate myself. Now, if you want to learn more about the best data visualization techniques, I can recommend the class Introduction to Data Visualization: From Data to Design, found on Skillshare. The course is taught by Nicolas Falton, information designer. Now, he also mentions in class that what separated him from the other people who also had technical skills was his design skills. My favorite lectures are those on color, typography, and layout design. Check it out if you are interested in improving your data visualization skills. I have a special link for you in the video description.

Click that link, become a member, and you'll get two free months of premium membership for all Skillshare classes, so not just this one. After the two months are up, if you want to continue your premium membership, it will cost you less than $10 a month if you get the annual subscription. You have a wide variety of themes to choose from. For example, it has graphic design, web development, marketing, productivity, office tools, and much more. Thanks to Skillshare for sponsoring this video and supporting my channel. If you liked it, please like it and if you want to improve your skills, consider subscribing to this channel.

Thanks for watching and see you in the next video. (upbeat music)