Secrets to Building Excel Dashboards in Under 15 Minutes!

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 analyzing 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 access 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 i'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 fill buttons and the access 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 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 nodes decimal places and i'll click ok and okay 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 ctrl 1 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 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 slice the 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 slices selected hold down shift and select the chart as well ctrl x to cut and i'm just going to control 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 will 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 a line chart so 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 title get rid of the grid lines in the legend 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 ctrl 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 and 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 slice 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 all right 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 valley field settings and change it to show the average and because it's a percentage let's format it accordingly i'll click ok and okay again and i'm ready to insert my pivot chart again this is showing data over time so we're going to go with 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 and 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 classic 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 control x to cut it out control 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 the 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 is 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

Channel: MyOnlineTrainingHub

Views: 1,011,082

Keywords: Excel Dashboards, dashboards

Id: 9p6tWCHbtPQ

Length: 13min 20sec (800 seconds)

Published: Wed Aug 19 2020