Building an Interactive Excel Dashboard

In this lesson I'm going to show you how you can build this interactive Excel dashboard you see behind me in less than 15 minutes. Now while the pace will be quite fast the idea is that you watch me build it first and then download the Excel file and watch the video again pausing and rewinding as you build it yourself. You're going to be amazed at what you can do in such a short period of time when your data is structured correctly. I'll be building the dashboard on this sheet called dashboard. It's just a regular sheet, you don't need any add-ins or anything special. You can see I've got a header and I've formatted some of the column widths, but that's really just to save me time. Now the data I'm using is some sales data spanning 2017 through to 2019. Plus I have some new data here for 2020 and I'm going to add that at the end to show you how quickly you can update the report. Now the key here is that the data is structured in a tabular format with columns for the different data types and this is suitable for analyzing in pivot tables as well as formulas like sumifs and countifs. Before I can get started with the analysis I'm going to format the data in an excel table using the keyboard shortcut ctrl t. It's asking me if my table has headers and we can see the headers up there so I'll click ok. Formatting it in a table will enable me to reference it using the dynamic structured references which automatically include any new data that I add to the table. If we look at the table design tab you can see the table's been given the name table 1. You can give it a name that's more useful. I'm going to leave it at that just in the interest of time. I'm ready to start analysing the data. I'm going to insert a pivot chart. We're going to pop it on a new worksheet so I'll click OK. You can see I've got a pivot table placeholder and a pivot chart placeholder and if we look at the field list instead of columns we've got legend series and instead of rows we've got axis categories. So all I need to do is build my pivot table. I also want a slicer for the year so I'm going to right click add a slicer. We'll just pop that there for now. Now the pivot chart has long category labels and this would be better suited to a bar chart so let's change the chart type and we'll choose bar chart and click ok i'll resize it slightly and we'll do a bit more resizing once we put it on the dashboard the next thing i want to do is right click and remove the value field buttons and the axis fill buttons because they just take up way too much space i'm going to leave these expand and collapse buttons because they allow the user to drill up and down on the face of the chart Let's also go in here and I'm going to get rid of the grid lines and the legend. I'm going to give it data labels and that means I don't need my horizontal axis. So let's get rid of that. Now notice that the labels don't have any comma separators. So I want to change the formatting and the best place to do that is in the pivot table. I'm going to right click and go into value field settings. and by changing the number formatting here it's going to feed through to the pivot chart so use comma separator and those decimal places and i'll click ok and ok again you can see it's been applied notice that my categories and products are sorted a to z but in the pivot chart they're in reverse order so let's fix that with the axis selected i'm going to control one and go in and format the axis to have categories in reverse order While I'm here, I'm going to make these columns a bit wider. So I want to change the gap width to say 50%. We'll close that. Let's give the chart a title. And I'm going to move the title across to the left because I want to actually place my slicer up here in this space. I need to reformat it a bit, so let's give it four columns. Remember there's another year to come. And I've set up this format here with no border. So I'm going to select that. Let's resize the slicer. Now I need to get rid of this header. It's self-explanatory what it is. So right click, right at the bottom, slicer settings, turn off display header, click OK. All right, let's move it up into the top of the chart. Now it looks like it's been there all along. So with the slicer selected, hold down shift and select the chart as well. Control X to cut. and I'm just going to Ctrl V to paste it into my dashboard. Let's resize it slightly, holding down Alt I'm just going to resize it so it snaps to the grid behind and we can probably move the slicer over a little bit. Okay so there's my first chart done. Let's go back to this sheet, I'll call it workings. Now normally I would have one sheet for each pivot table but in the interest of time I'm going to put them on one sheet. and that'll help you see them in context as well. I'm just going to copy this pivot table and paste it and we'll modify it rather than building one from scratch and that way the number formatting will be retained. So this one I want to see categories across the columns and years in my row labels. This one is going to be a line chart because it's showing data over time that's suited to line charts. We'll choose this one here with markers and click ok. Now here I don't want any of the fill buttons so I'm going to hide all fill buttons in one go. Let's go ahead and we'll give our chart a title, get rid of the grid lines and the legend. Now I'm going to add a legend at the very end. I want to make these lines span the width of the chart so with the axis selected, control 1, and here I want the axis position on tick marks and you can see now that the lines go right to the end of the horizontal axis. Let's give this chart a better title. represents sales, so let's name it accordingly. Now the next four charts, I want them to be filtered based on the category. So we need to add a slicer for the category. Again, let's format it to have four columns. We'll give it this new style with no border, and let's make it a bit wider. I'll right click, go into the settings and we'll turn off the header. It's self-explanatory what it represents, so we don't need that as well. So with that selected, holding down shift I select the chart, control x and let's paste it into the dashboard. Now the slicer is just going to sit up here for now. Let's resize this chart holding down alt and we're ready to create the next chart. Before we do that though, at the moment this slicer is connected to this chart and I don't want that. So let's select the slicer, right click, report connections. and I'm going to deselect this pivot table and click ok. So now this slicer only filters this chart and this slicer only filters this chart. Alright let's go ahead and we'll copy this pivot table for the next chart. This one is going to show the ratings instead of the sales and I want to go into the value field settings and change it to show the average and because it's a percentage let's format it accordingly. click ok and ok again and i'm ready to insert my pivot chart again this is showing data over time so we're going to go with the line chart i'm going to right click and hide all the fill buttons let's give the chart the title get rid of the grid lines and the legend let's format the axis so that it's positioned on the tick marks let's give the chart a more useful title and control x to cut and control v to paste it in I'm going to hold down alt while I resize it and we're ready for our last two charts. So back in the workings I'm going to copy these pivot tables and just paste them over here. Let's move across. Now what I want to show here is the year on year change. So with any of the values selected I'm going to right click, show values as, percentage difference from. And here we want the base field to be the year, we're showing the year on year change, but the base item needs to be previous. the previous year. Now I'm just going to format the fields so that the number format is showing as a percentage with no decimal places. Click ok and ok again. This one here needs to do the same thing so right click show values as percentage difference from and here we want previous. So we're ready for our charts let's go ahead and insert them. Cluster column is fine for this purpose. Again, let's get rid of all the fill buttons. We're going to use data labels for this, so I'm going to turn off my vertical axis, that would just be duplication. We'll give it a title, we'll give it some labels, let's get rid of the grid lines and the legend. Now I also want to format this axis so that the labels are positioned low. And that just future proofs if in the event we have negative growth, this axis will always be at the bottom of the chart. So let's change the chart title to Sales Change Year on Year and I can Ctrl X to cut it out, Ctrl V, let's resize it using the Alt and we're ready for the last chart. Again, this one will also be a column chart. We're going to turn off the fill buttons, we're going to get rid of my vertical axis, give it a title, add labels, get rid of grid lines and legend. Let's format this so that it's positioned low. give it a title. Am I ready to cut it out? Ctrl X, Ctrl V, holding down Alt while I resize it. So we've got all of our charts ready to go. What we're missing is a legend for these charts. Now, because they all contain the same data, I don't want to repeat the legend on them, that would just be noise. So what I want is a dummy chart that's just going to be for my legend. So with this one selected I'm going to Ctrl D to duplicate it. I'm going to strip it down and get rid of everything except I'm going to add a legend at the bottom. Now here I'm just going to resize the chart. because I'm going to use it up here in my header. And we'll set it up. Let's make it a bit smaller so it fits in that space. And I want to just format it so that it sits at the very back and that way my slicer sits on top I just need to resize the slicer so that it covers the chart behind but not the legend so with a little messing about it fits in just right and now as I select items in this slicer you can see the legend updates all my charts down here update but I'm not duplicating that legend information all over this dashboard. So my dashboard's done and it's interactive. But what happens when you want to update it? Well, because we formatted the data in an Excel table, all I need to do is add my new data to the very next empty row and you can see here this little marker indicates the end of my table so let me go and grab the data I'm just going to copy it and ctrl V to paste it in you can see the table formatting being copied down and we've got the indicator in the bottom right to show me the end of the table now all i need to do is go back to the dashboard and refresh all the pivot tables so keep an eye on these four charts here because these horizontal axes will have 2020 added to them all i need to do is go to the data tab and click refresh all and in one click all of my charts in my dashboard are up to date you can see this one now includes 2020 data as do these four charts here and it's still fully interactive. I've literally copied and pasted my new data and that's it. So there you go in less than 15 minutes I've created a fully interactive Excel dashboard and it's all possible because my data is formatted in an Excel table which has allowed me to leverage pivot tables and pivot charts and the slices for my interactivity. You can download the excel file for this lesson from the link here. I hope you can make use of these techniques. If you like this video please give it the thumbs up and subscribe to my channel for more. And why not share it with your friends who might also find it useful. Thanks for watching.

I'll be building the dashboard on this sheet called dashboard. It's just a regular sheet, you don't need any add-ins or anything special. You can see I've got a header and I've formatted some of the column widths, but that's really just to save me time.

Now the data I'm using is some sales data spanning 2017 through to 2019. Plus I have some new data here for 2020 and I'm going to add that at the end to show you how quickly you can update the report. Now the key here is that the data is structured in a tabular format with columns for the different data types and this is suitable for analyzing in pivot tables as well as formulas like sumifs and countifs. Before I can get started with the analysis I'm going to format the data in an excel table using the keyboard shortcut ctrl t. It's asking me if my table has headers and we can see the headers up there so I'll click ok.

Formatting it in a table will enable me to reference it using the dynamic structured references which automatically include any new data that I add to the table. If we look at the table design tab you can see the table's been given the name table 1. You can give it a name that's more useful. I'm going to leave it at that just in the interest of time. I'm ready to start analysing the data.

I'm going to insert a pivot chart. We're going to pop it on a new worksheet so I'll click OK. You can see I've got a pivot table placeholder and a pivot chart placeholder and if we look at the field list instead of columns we've got legend series and instead of rows we've got axis categories.

So all I need to do is build my pivot table. I also want a slicer for the year so I'm going to right click add a slicer. We'll just pop that there for now.

Now the pivot chart has long category labels and this would be better suited to a bar chart so let's change the chart type and we'll choose bar chart and click ok i'll resize it slightly and we'll do a bit more resizing once we put it on the dashboard the next thing i want to do is right click and remove the value field buttons and the axis fill buttons because they just take up way too much space i'm going to leave these expand and collapse buttons because they allow the user to drill up and down on the face of the chart Let's also go in here and I'm going to get rid of the grid lines and the legend. I'm going to give it data labels and that means I don't need my horizontal axis. So let's get rid of that.

Now notice that the labels don't have any comma separators. So I want to change the formatting and the best place to do that is in the pivot table. I'm going to right click and go into value field settings. and by changing the number formatting here it's going to feed through to the pivot chart so use comma separator and those decimal places and i'll click ok and ok again you can see it's been applied notice that my categories and products are sorted a to z but in the pivot chart they're in reverse order so let's fix that with the axis selected i'm going to control one and go in and format the axis to have categories in reverse order While I'm here, I'm going to make these columns a bit wider. So I want to change the gap width to say 50%.

We'll close that. Let's give the chart a title. And I'm going to move the title across to the left because I want to actually place my slicer up here in this space.

I need to reformat it a bit, so let's give it four columns. Remember there's another year to come. And I've set up this format here with no border.

So I'm going to select that. Let's resize the slicer. Now I need to get rid of this header. It's self-explanatory what it is. So right click, right at the bottom, slicer settings, turn off display header, click OK.

All right, let's move it up into the top of the chart. Now it looks like it's been there all along. So with the slicer selected, hold down shift and select the chart as well. Control X to cut.

and I'm just going to Ctrl V to paste it into my dashboard. Let's resize it slightly, holding down Alt I'm just going to resize it so it snaps to the grid behind and we can probably move the slicer over a little bit. Okay so there's my first chart done.

Let's go back to this sheet, I'll call it workings. Now normally I would have one sheet for each pivot table but in the interest of time I'm going to put them on one sheet. and that'll help you see them in context as well. I'm just going to copy this pivot table and paste it and we'll modify it rather than building one from scratch and that way the number formatting will be retained. So this one I want to see categories across the columns and years in my row labels.

This one is going to be a line chart because it's showing data over time that's suited to line charts. We'll choose this one here with markers and click ok. Now here I don't want any of the fill buttons so I'm going to hide all fill buttons in one go. Let's go ahead and we'll give our chart a title, get rid of the grid lines and the legend. Now I'm going to add a legend at the very end.

I want to make these lines span the width of the chart so with the axis selected, control 1, and here I want the axis position on tick marks and you can see now that the lines go right to the end of the horizontal axis. Let's give this chart a better title. represents sales, so let's name it accordingly.

Now the next four charts, I want them to be filtered based on the category. So we need to add a slicer for the category. Again, let's format it to have four columns. We'll give it this new style with no border, and let's make it a bit wider.

I'll right click, go into the settings and we'll turn off the header. It's self-explanatory what it represents, so we don't need that as well. So with that selected, holding down shift I select the chart, control x and let's paste it into the dashboard. Now the slicer is just going to sit up here for now.

Let's resize this chart holding down alt and we're ready to create the next chart. Before we do that though, at the moment this slicer is connected to this chart and I don't want that. So let's select the slicer, right click, report connections. and I'm going to deselect this pivot table and click ok. So now this slicer only filters this chart and this slicer only filters this chart.

Alright let's go ahead and we'll copy this pivot table for the next chart. This one is going to show the ratings instead of the sales and I want to go into the value field settings and change it to show the average and because it's a percentage let's format it accordingly. click ok and ok again and i'm ready to insert my pivot chart again this is showing data over time so we're going to go with the line chart i'm going to right click and hide all the fill buttons let's give the chart the title get rid of the grid lines and the legend let's format the axis so that it's positioned on the tick marks let's give the chart a more useful title and control x to cut and control v to paste it in I'm going to hold down alt while I resize it and we're ready for our last two charts.

So back in the workings I'm going to copy these pivot tables and just paste them over here. Let's move across. Now what I want to show here is the year on year change. So with any of the values selected I'm going to right click, show values as, percentage difference from.

And here we want the base field to be the year, we're showing the year on year change, but the base item needs to be previous. the previous year. Now I'm just going to format the fields so that the number format is showing as a percentage with no decimal places. Click ok and ok again. This one here needs to do the same thing so right click show values as percentage difference from and here we want previous.

So we're ready for our charts let's go ahead and insert them. Cluster column is fine for this purpose. Again, let's get rid of all the fill buttons. We're going to use data labels for this, so I'm going to turn off my vertical axis, that would just be duplication.

We'll give it a title, we'll give it some labels, let's get rid of the grid lines and the legend. Now I also want to format this axis so that the labels are positioned low. And that just future proofs if in the event we have negative growth, this axis will always be at the bottom of the chart. So let's change the chart title to Sales Change Year on Year and I can Ctrl X to cut it out, Ctrl V, let's resize it using the Alt and we're ready for the last chart.

Again, this one will also be a column chart. We're going to turn off the fill buttons, we're going to get rid of my vertical axis, give it a title, add labels, get rid of grid lines and legend. Let's format this so that it's positioned low. give it a title.

Am I ready to cut it out? Ctrl X, Ctrl V, holding down Alt while I resize it. So we've got all of our charts ready to go.

What we're missing is a legend for these charts. Now, because they all contain the same data, I don't want to repeat the legend on them, that would just be noise. So what I want is a dummy chart that's just going to be for my legend. So with this one selected I'm going to Ctrl D to duplicate it.

I'm going to strip it down and get rid of everything except I'm going to add a legend at the bottom. Now here I'm just going to resize the chart. because I'm going to use it up here in my header.

And we'll set it up. Let's make it a bit smaller so it fits in that space. And I want to just format it so that it sits at the very back and that way my slicer sits on top I just need to resize the slicer so that it covers the chart behind but not the legend so with a little messing about it fits in just right and now as I select items in this slicer you can see the legend updates all my charts down here update but I'm not duplicating that legend information all over this dashboard.

So my dashboard's done and it's interactive. But what happens when you want to update it? Well, because we formatted the data in an Excel table, all I need to do is add my new data to the very next empty row and you can see here this little marker indicates the end of my table so let me go and grab the data I'm just going to copy it and ctrl V to paste it in you can see the table formatting being copied down and we've got the indicator in the bottom right to show me the end of the table now all i need to do is go back to the dashboard and refresh all the pivot tables so keep an eye on these four charts here because these horizontal axes will have 2020 added to them all i need to do is go to the data tab and click refresh all and in one click all of my charts in my dashboard are up to date you can see this one now includes 2020 data as do these four charts here and it's still fully interactive. I've literally copied and pasted my new data and that's it.

So there you go in less than 15 minutes I've created a fully interactive Excel dashboard and it's all possible because my data is formatted in an Excel table which has allowed me to leverage pivot tables and pivot charts and the slices for my interactivity. You can download the excel file for this lesson from the link here. I hope you can make use of these techniques.

If you like this video please give it the thumbs up and subscribe to my channel for more. And why not share it with your friends who might also find it useful. Thanks for watching.

Transcript for:Building an Interactive Excel Dashboard

Transcript for:
Building an Interactive Excel Dashboard