How to Create a Beautiful Interactive Dashboard in Microsoft Excel | 2021
In this topic, we’ll discuss How to Create a Beautiful Interactive Dashboard in Microsoft Excel using multiple PivotTables, Pivot Charts and PivotTable tools to create a dynamic dashboard.
You can create a beautiful Dashboard in 4 simple steps:
1. Create Individual charts
2. Create Dashboard Background
3. Create Dashboard
4. Add Slicers
Today we will be building a dashboard which looks beautiful and professional. All you need is Excel and PowerPoint.
This is an interconnected dashboard where you can select from the bottom and the chart changes. You can even multi select the options and you can see it it's gonna change after you select and we can even select by country as well. I have provided the timestamps in the description.
Alright, so let's get started. I have the data here with me all the data is from 2013 to 2016. It is divided into different categories over here. So the first thing that we need to do is create a table from this data the way you do that is by selecting the entire data from top to bottom and creating a table. I'll have already done that so I'm not going to do it again. But I'm going to give you an example on how to do it. Let's say you have a data over here, and you select it from top to bottom. And you click on Insert. And you click on table and click on OK over here. And on the top, under this table name you can type your table name have in my previous data set that I showed you earlier, I had named it t so in the same way you can name it as well. Alright, so let's get started. And let's create our first chart.
So the first thing that you need to do is you need to insert a pivot table over here. And the table that I have created in the first sheet I have named as P and I'm going to reference that each time I am going to create an individual chart. So I'm going to click on OK over here. So the entire data is over here. So the first track we are going to create is by year. We bring the year, month and we're going to reference it by sales. And we're going to clean up the data if there are any irregularities. So we're going to uncheck this blank. And we're going to click on OK. Now, next thing we need to insert a pivot chart. So for this we will be selecting a line chart. And perfect This looks good. So over here, we're going to remove all these buttons. I'm just going to right click on them and hide all field buttons on chart and I'm going to remove all these so that the chart looks much cleaner. And these lines as well. Yes, perfect. Now it looks much better.
So let's create our second chart. And we're going to do the same process again. We're going to click on Insert we want to bring in the pivot table, the one that we created earlier. I have named T just want to select okay, and this time we are going to create a chart by country. So I'm going to bring country in the column section and the sales in the value section over here. And I'm going to clean the data over here as we did before. So the problem with bringing a map into Excel is that if we select this over here and if you go to insert, it's not going to bring the map it's going to give us an error message. So there is a way around for the so what we're going to do is we're going to select the country's first and we're going to bring them over here and then we're going to bring some of sales. And over here we're going to use the get function. So we just type equal sign. Just click on the tab and that's it. So equal sign select this box and press the Tab key on your keyboard. Equal sign select the box, press the Tab key on a keyboard Alright, alright, so now that we have created our separate data, we'll be gonna select it entirely and then click on maps. And there we go. So the first time when you select it, it's going to ask you to accept we're going to click on Accept and let's remove all this. Let's remove the title as well. Alright, perfect. Let's create our third chart. We're going to do the same process again. Click on insert, click on Pivot Table, reference our table. Click on OK. And over here the third chart will be by product sales by product. So we're going to select product over here. And we're going to select sales over here.
Now we're going to clean the data. And now we're going to bring in you guessed it, right oh you're going to bring in the pivot chart. So the pie chart, and over here we're gonna select Yeah, this looks good. And we're going to select this and over here. Yep, let's remove all these buttons and remove this box over here. Yeah, that looks fine. And the other thing that we're going to also do is include the data labels. And we're going to format the data labels so that it shows the percentage. What exactly is the percentage is we're gonna remove the value from here and we're going to select the category so that that stock the sales are 20% Mobile, the sales were 17% Okay, perfect. Now that we have created our three individual charts, now we are going to create the background for our dashboard, click on View and remove these gridlines Alright, so the first thing that we are going to do is we are going to bring in a background and we're gonna select the pictures and we're going to select this background
Once we have done that, the next thing that we're going to do is we're going to bring in a shape a rectangle shape and we're going to align it to the center. And once we have done that, let's format the shape. We're going to change the color to white. Perfect, and we're going to remove the line. Alright, perfect. So now, the next thing we're going to do is we're going to bring in gridlines we're going to bring in horizontal and we're going to bring in vertical. So now we're gonna bring in the shape, which is rectangle again. And we're going to select this area all right, and let's copy this and be Ivana that's dated. Alright, let's reduce the size. And let's bring it here. Alright, perfect.
Once we have done that, the next thing that we're going to do is we're going to bring in a shape a rectangle shape and we're going to align it to the center. And once we have done that, let's format the shape. We're going to change the color to white. Perfect, and we're going to remove the line. Alright, perfect. So now, the next thing we're going to do is we're going to bring in gridlines we're going to bring in horizontal and we're going to bring in vertical. So now we're gonna bring in the shape, which is rectangle again. And we're going to select this area all right, and let's copy this and be Ivana that's dated. Alright, let's reduce the size. And let's bring it here. Alright, perfect.
So now, what we're going to do is we're going to select this background and then we're going to select these two shapes and under the shape format section, under merge shapes, click on subtract. And what this is going to do is this is going to prove the area and it's going to create a tab. Now, what we're going to do is we're going to remove these gridlines and the next thing we're going to do is we're going to click on Gradient Fill. And over here, you're going to only select two colors. So we're going to remove these two middle ones. And over here, you can play around with the colors. We can let's say if you want on one side, and on the other side, let's select maybe red. Or let's select red on this side and purple on the side and we're going to change the angle to zero degrees. All right, and we're going to change the transparency for both of these colors to 20%. And change transparency over here as well. Let's actually make it 30% And even over here as well let's make it 30% Alright, so now our background is ready. So let's go back to our Excel sheet. So I have brought in the same background over here. From the picture section. Put in the tile design. And let's put it over here Alright, perfect. Now let's bring in all the charts that we have created so far. So let's copy this and let's bring it over here let's bring in the next shot let's bring him to the dashboard on this side. All right, let's move it a lot All right, let's bring in our last chart. Let's bring it over here. Alright, perfect. So now let's select the first chart. And let's right click on that we'll click on Format plot area and once you have done that, click on No Fill and under the border select no line and do the same thing for the other two charts as well select No Fill and select no line. Do it for the third chart, no fill and no line.
The next thing that we're going to do is we're going to make all these letters white in color. So we're going to go to Text options and we're going to change the color to white. We're gonna do the same thing over here. You're gonna change the color to white. And same thing over here as well. And even for these numbers, we're going to change the color to white. All right now this is looking good. And let's align in the middle. Alright, perfect. So now the other thing that we can do over here is we can bring in the titles. So the next thing that you're going to do is go in and click on shapes. And let's select this option over here, rectangle with the rounded corners and once you have done that, just right click on this and click on select solid fill and let's make let's change the color. Let's see over here to white and let's make the transparency 80% And we're going to remove the line as well. Alright, perfect that that looks good. So now just copy it. And we're going to bring it to the remaining chart so I'm gonna bring one over here. The second, the third one over here. And the last one, I'm gonna take it on the top. Alright, perfect. Now just insert text. We're going to insert go to the Insert tab, you can select the text box. And over here, you're going to select this area of effect. And we're going to tie we're going to change the font size. Let's make it 20.
Says sales by alright perfect and let's make this area a no fill and let's change the color to white here it is we just went on the line. And we're going to copy this text box and we're going to bring it over here as well. Same thing with the third one and the same thing with the title. So let's select the title and let's make the font size a little bit bigger. Let's make it sales report 2020 And let's set that line in the middle. All right. All right. Perfect. And let's do the same thing over here. As well as change. This is by country. This is by product. All right, perfect. Perfect. So now the other thing that you can do over here is you can even change the gradient fill over here. So you can either make the size of this you can increase the size if you like and you can even move the last box if you like this design much better. You can keep it this way. All right. So now that's that looks really nice. Alright, so now that we have created our chart, and it looks beautiful, and now let's make it interconnected. So in order to do that, what you do is you select your first shot, any one of the charts and you click on the Insert tab and you click on slicer. Over here we're going to select it by country and we're going to select it by you and let's bring these two slicers over here and one over here. Now, that design is looks horrible. So we're going to make it look really really good. So in order to do that, what we're going to do is we're going to just duplicate one of the one that's already there, and we will name it custom. Alright, and we're going to click on OK. Now, what we're going to do is we're going to right click on this custom, and we're going to click on modify, and let's select the whole slicer and let's change the settings over here, the border let's select it none the color let's select it automatic, the Fill let's select it black. Alright, let's click on OK. And let's click on OK over here. Alright, so this slicer is already selected. Now just select this custom the color changes. That's perfect. Now if you notice, we don't see the year. Let's change that. And let's go to modify the header section. And we click on format and the font size let's make it wider color. Let's click on OK. Now the year is visible Perfect. Now the other thing that we need to do is let's change this color from blue. And same thing right click on that, click on modify and select this option it says selected item with data and click on format and over here.
Says sales by alright perfect and let's make this area a no fill and let's change the color to white here it is we just went on the line. And we're going to copy this text box and we're going to bring it over here as well. Same thing with the third one and the same thing with the title. So let's select the title and let's make the font size a little bit bigger. Let's make it sales report 2020 And let's set that line in the middle. All right. All right. Perfect. And let's do the same thing over here. As well as change. This is by country. This is by product. All right, perfect. Perfect. So now the other thing that you can do over here is you can even change the gradient fill over here. So you can either make the size of this you can increase the size if you like and you can even move the last box if you like this design much better. You can keep it this way. All right. So now that's that looks really nice. Alright, so now that we have created our chart, and it looks beautiful, and now let's make it interconnected. So in order to do that, what you do is you select your first shot, any one of the charts and you click on the Insert tab and you click on slicer. Over here we're going to select it by country and we're going to select it by you and let's bring these two slicers over here and one over here. Now, that design is looks horrible. So we're going to make it look really really good. So in order to do that, what we're going to do is we're going to just duplicate one of the one that's already there, and we will name it custom. Alright, and we're going to click on OK. Now, what we're going to do is we're going to right click on this custom, and we're going to click on modify, and let's select the whole slicer and let's change the settings over here, the border let's select it none the color let's select it automatic, the Fill let's select it black. Alright, let's click on OK. And let's click on OK over here. Alright, so this slicer is already selected. Now just select this custom the color changes. That's perfect. Now if you notice, we don't see the year. Let's change that. And let's go to modify the header section. And we click on format and the font size let's make it wider color. Let's click on OK. Now the year is visible Perfect. Now the other thing that we need to do is let's change this color from blue. And same thing right click on that, click on modify and select this option it says selected item with data and click on format and over here.
The only thing that we're going to change is the film and we're going to change it to white. And let's click on okay. All right. That's good. That looks much much cleaner. Now, you see this black blank, let's clean it up. And just right click on that. Click on slicer settings click on this hide items with no data and click on OK. That's gone. Perfect. Now let's make this one the same color let's select this and click on this. Alright, perfect. Now let's change the settings for the slicer over here. Let's change the size of it. And so if you notice the columns we have, we want to let's increase the columns. So now we have four columns. Perfect and we're gonna resize it a bit. Alright, perfect. And we're gonna do the same thing for the country as well. We're gonna clean the data, hide items with no data, we're not cleaned up it and then we're going to crease the columns. So same thing. This time we are going to increase it to five columns because we have five countries. And let's change that's alright, perfect. Now the only thing that we need to do has been to have this slicer, these two slices connected to our chart. So in order to do that, we are just going to right click on it and click on report connections. And we're going to select the other two pivot tables. We're going to do the same thing in the country as well. Right click, click on report connections and we're going to select the other two pivot tables. Alright, perfect. So now that's selected. Let's arrange that Alright. So now if you notice if I select 2013 The data changes do the entire chart is now interconnected. If I select 2014 The entire data changes. I can even select country wise the entire data changes over here as well. The session as well. So now you can even multi select the option so let's say you selected 2013 over here. And let's just select this multi select option and let's select 2014 as well. So now you can see the date of 2013 as well as 2014. Let's say you want to select 2016 It's there. You got the point. Perfect. All right. So finally our beautiful dashboard has been completed. And this is very simple and very easy to create. And this is right within Excel. Excel is an amazing application in which you can build beautiful dashboards so quickly. If you have any questions, please do comment.
Microsoft 365 Business Standard: https://bit.ly/3HIFYEb
Microsoft 365 Apps for Business: https://bit.ly/3FxhJHh
Microsoft 365 Business Premium: https://bit.ly/3cxp4dn
Microsoft 365 Personal: https://bit.ly/30r9pcR
Microsoft 365 Family: https://bit.ly/30w4VCl
Website: https://www.productivitycentral.ca/
✅ Subscribe now: youtube.com/channel/UCT9eERQLGlXbew8Fjyjv8Iw?sub_confirmation=1
DOWNLOAD TUBEBUDDY: https://www.tubebuddy.com/Productivitycentral
Download Canva: https://www.canva.com/join/ltt-wvx-fjb
⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel -- the price to you is the same.
#Microsoft365
Comments
Post a Comment