Make an Interactive Excel Dashboard in 4 Simple Steps!

in this video we'll make this awesome interactive Excel dashboard in four simple steps first we'll set up the dashboard structure second will analyze all the data using pivot tables third will create all the key kpis visuals and tables and finally we'll make everything Dynamic using slicers so let's get into it step one is the structure so first let's take a look at what we're working with and over here you can see we have a blank dashboard Tab and then we have the data tab where we have our full data set which you can download for free using the link in the description below suppose we work for Coca-Cola and we have this data set which tells us all of the retailers that we work with so we can make a dashboard for our us retailers so to set up the structure firstly in the dashboard tab we're just going to stretch this out to something like a width of 25 and then we can select that column and this is basically going to be our side header so let's go ahead and fill it in something like a dark blue we also want to get rid of all of these grid lines which you can do by going to the view Tab and unticking on grid lines from here we want to add a few different shapes that are going to be the title and right below we're going to have all of the different kpis so we can go over to insert and under shapes here we're going to go for this rectangle over here and let me just stretch it out roughly from here to um down over here now let's say we want it to be the same matching color as the side here we want it to have no outline and as a shape effect we want it to have an outer Shadow like so now let me fast forward how I do the other ones if you want to duplicate this just control shift and drag down this way you've created a second one so we want to add four kpis so you can see here I have these shapes laid out and now for this one I'm just going to make it lighter blue like so and these are going to be the four kpis now let me fast forward how I add a few of the titles over here awesome we now have the title and for kpis below and then right below that around row 9 we want to add two different tables so the first one is going to be the sales by beverage run and then the second one over here is going to be the sales by retailer so with that let's go ahead and format one together so here I'm just going to fill it in a dark blue like we have and we want the font color to be in white we're going to Bolden that and then to Center you can either merge and center or the other way is to go to this drop down under alignment and horizontal here we're just going to Center across selection this way what it does is you can still type things in the other cells doesn't just make it all one cell so once we have this area we can simply select it use the format painter and then just paste it over here to the side and we have the same thing on both sides in terms of formatting right below we're going to add all the subheaders so let me fast forward that awesome this is what it's looking like we have the brand the sales for a 2-year period and the variance which is simply the difference between the two then down below maybe around row 20 we're going to add another line this one's going to be the sales and operating profit margin by quarter and let me format this one as well so I'll take H H that's the fill color there and I'm going to go for the same dark blue and then alt hfc that's going to be the font color I'm going to make it in white again and I'm also going to do the merge and center like before but using this uh this format so Center across selection and hit on okay there awesome the structure is starting to take place now we just need to add a few of the images so we'll have a logo of Coca-Cola up over here and we'll also have the flag of the US over here to the side for this I'm just going to go to insert and under pictures go ahead and find them so let me fast forward that all right now you can see that we have both logos for Coca-Cola and the flag of the us and we're done with the overall structure so that's 25% of the way and the next step is going to be the data analysis so we actually need to find the data and do something with it so let's go over to the data tab over here and what we're going to do is first make sure it's a table by hitting contrl T if it isn't in your case if it is a table you should find this table design tab to the side to analyze it the fastest way is probably using pivot tables so we'll go over to the insert Tab and click on pivot table so we can just hit on okay let's go ahead and rename this worksheet maybe analyze so we know what it's about so first if you recall in the dashboard here the top part is going to be all of the kpis so let's go ahead and find those the first one is going to be the total sales so we can just add that under values and you'll find it over here to the side calculated then we also have the unit sold which we can also add in there and what else we have the price per unit but keep in mind that the price per unit shouldn't be the sum that doesn't make too much sense it can just be under value field settings there on that drop down we can change that to the average which makes a lot more sense so we have an average price of 45 cents per unit and we also might want to add the operating profit so what's our profit there awesome that's one pivot table done and we can work on the next ones which are going to be the two different tables that we have over here so what we want to do first is copy this whole pivot table so crl a to select it contrl C to copy and contrl + V to paste it down below and over here we just want to remove all of these areas that we've just added and instead what we want to have is going to be on one side having the beverage brand Under The Rose and then on the other side we want to have all of our sales so the total sales under the values we want these to be split by year so let's go ahead and add the invoice date we're going to put that under the columns now we don't want the full date breakdown instead we only want the years so we can remove these and just put the years in there for now let me do the same for the other table nice now we're just missing one piece of data which is going to be the chart over here for the sales and operating profit margin by quarter so we'll also add another pivot table and just paste it down over here and for this one we said we want the sales and we also want the operating profit margin so let's go ahead and drag that and drop it over here but we don't want the sum of it we're just going to get the average instead so under value field settings let's go ahead and click on the average we can remove the years from here as we're actually going to put them in the rows so we can get rid of the retailer as well and here on the on the side we want to put it by quarter and put the year on top so we we're going to put it on top there and we have all of the years and the quarters inside of them now we don't want it in this grouped format so instead we can go over to design and under report layout we want it to be show in tabular form this way we're going to see the full split so when we open that up you can see that we have the year on one side and then we have the quarters we also don't want any of these subtotals so we can go over to subtotal and do not show subtotals this is because we're going to make it a chart later so it's easier to do it this way when it comes to charts and pivot tables it's actually easier if we make it separately so over here to the side we're just going to link it by going to equals and linking uh the year first and just dragging that all the way across down and across so contrl R to drag to the right and contrl D to drag down now we have all the data but not in pivot table format instead just linked and we'll use this for the chart after if you find this a bit too fast to follow along but you realize just how much of an edge being good at Excel would be for the workplace you can consider checking out our Excel for business and finance course to become proficient in Excel with our comprehensive curriculum we cover everything you need to know ranging from formatting best practices and shortcuts to building awesome visual dashboards creating large Dynamic Financial models and much more this is basically the course I wish I had before I started working in an Excel heavy corporate job if all of that sounds interesting check out the link in the description below and if you want more than just Excel we also have a ton of other courses including powerbi Finance evaluation and much more all right back to the video all right that's 50% done which takes us to our third step which is going to be adding kpis visuals and tables so going over to the dashboard Tab and first we're going to work on these tables over here so for the sales by beverage brand we're simply going to link those to the ones in the analyze tab so we can just go to equals and we have that table over here so hit enter there then we'll drag it down and across with crlr and contrl D this last part is the total right now which we don't want it like that instead we want to do equals and the current year minus the previous year and hit enter you could also do the percentage change if you wanted in this case I'm just going to go for a total we'll put the total as the bottom here and this is fine as is you could also calculate it with with alt equals and then just dragging that across awesome to reformat these numbers we can just select them go to controll one there and under number we're going to want a number format with comma separators to make it more readable and no decimals are needed so just hit on okay that's what it's looking like for this total here we could also format it by adding say a top and a bottom border and we could also choose a fill color like this yellow one and Bolden it to make it stand out a bit awesome now let me do this other one nice now for the variances over to side we could add some kind of conditional formatting to make them a bit more easy to view so under data bars we could go ahead and pick something like the variance let's say we go for a green color same thing over here and go ahead conditional formatting data bars and let's go ahead and pick this green color now we can see what's been the biggest increase overall it's all looking good as it's green if it were negative like let's say I go ahead and put a zero here you'll notice how it gives us a red line which is nice to see the difference there now let's work on the kpis that we have up top and for each of these we're going to add a separate text box so under insert we would go under shapes and select the text box which is the first part there and let's just put it over inside of this area and then we'll do the same for all the other ones we can reformat this by removing the fill so no fill and removing the border so no outline there what we want to do is once we have it selected we just want to go to the formula bar and go to equals and we can't actually just link it by clicking on the cell you'll notice that it's going to give us an error there instead the right way to do that is just going to be to selecting on it going to the formula bar hitting equals and just manually typing where it's located so it's an anal analyze and cell A4 and hit enter now you see that the number is exactly the same now to format this number we can go ahead and change the color and make it bigger over here but to change the formatting there we we should do that over here so for these two we're just going to go ahead and make it a number format and add the separator same thing goes with this other one so I'm just going to select it and paste it over here and this one it's the average price so let's go ahead and just make it around2 s places and now you'll notice that it's being updated here now for all these other ones we can just control shift and drag now this one's no longer A4 but rather B4 and hit enter there if you lose the formatting you can always go back to the original click on the format painter and paste it to the next one to add the actual headers for each box you can simply type once you have the box selected we can choose the total sales here for example and now we can format it nice now for the average price here it might be good to put a dollar sign in front we can do that by Under the average price per unit once we have it selected we can go to this drop-down under more number formats which is the same as heading control one we can go to currency here and let's say I'm just going to put a dollar there and hit on okay now you can see that we have a dollar sign in front finally we can work on the chart down here in the bottom and for this we'll go over to the analyze tab all the way to the bottom here let's first get rid of this and you remember that we made this table to the side so let's go ahead and select this whole area and then go to insert under recommended charts let's just go for this first one over here and hit on okay you'll notice that we do have a series 2 but we can't actually see it that's because one side is in values which is this one and the other one is in percentages so it's really small a small number so we can right click there and go to change chart type now if we go over to the bottom under combo we want to make this one a line which is fine as is but we want it to be on the secondary axis now we can see a lot better and hit on okay there we can also change the series names by right clicking on them and going to select data so this one we're going to edit as a series one it's simply going to be the sales and hit on okay and the second one is going to be the operating profit margin and hit on okay again now we're ready to move this chart so control X and under dashboard crl V let me fast forward how I edit this you can see I've gotten rid of the title and I've stretched it out another thing we can do these numbers are kind of hard to read so we can right click and under format access we can go under number and here instead of General we can go to number zero decimal places and we want to add that separator and that's all we need there so we can close out of that we can also make this a bit nicer to match our Style by changing the fill color to our dark blue and for the second one we could add some markers so right click format data series and under this fill here we're going to go for line first and a solid line that's going to be in our red color let's say and we also want to go to the markers all the way to the side here and under marker options we want a buil-in marker that's going to be let's say a circle in size five is fine and we want that to have a solid fill color that's going to be let's say in white so under border down below we're going to go for a solid line and let's make that a red line we can make that a bit thicker of a border let's see what that's looking like great we can get rid of the spoiler around it so under no fill and we're going to go for no border as well now that's looking a lot cleaner so let's scroll back up to see what it's looking like we can collapse the sub ribbon if it's bothering us by hitting on collapse the ribbon now we can see a bit more of the dashboard awesome now we're 75% done and the next step is going to be to make it Dynamic using some slicers so at the moment if we want to change let's say by different regions so filter by that or maybe change by years we can't quite do that yet yet so we're going to go over to the analyze Tab and let me just bring back this this um ribbon so I'm going to click on any tab deselect the collapse the ribbon and there we go so now we can just select on any pivot table and what we want to do is under pivot table analyze click on insert slicer this is basically a filter that's going to make the dashboard Dynamic let's suppose that we want one by all the different regions so we want to be able to filter by region we can then move that with CR X and then bring it to the dashboard let me fast forward how I add this awesome you can see what that's looking like to the side let's suppose I go for the Midwest you'll notice all these numbers change that said nothing else is changing over here that's because the slicer isn't linked to the other pivot tables so under the analyze tab we want to go to the next one and then under pivot table analyze we want to filter connect ctions this is going to make it linked to that slicer so we want to take on that let me fast forward how I take them for the other ones awesome so now that it's linked when I go back to the dashboard and select let's say the Midwest you'll notice that all the values are changing same thing with the chart down below you'll see how that's moving as well awesome now let's click on this x there to the filter we could add a second filter as well let's say maybe for the years so we can do that by up over here let's say selecting the first pivot table and under pivot table analyze insert slicer again and we'll do one for the years so it's not going to be the invoice date up here but rather the years on the bottom and hit on okay you'll notice that we do have these strange ones that are basically dates that don't exist for us we only have 2022 and 2023 so if you want to get rid of these three that are kind of useless we can just go to right click and under slicer settings you'll see here Hide items with no data so we want to take on that and hit on okay now that's looking better awesome at the moment this one's only linking to the kpis and maybe it makes sense to leave it that way as the tables here are not going to have any values for 2022 and same thing with the chart down below let me X that to get rid of it overall not sure if a year slicer is the most useful but you get the idea of how useful they can be now one final feature that that's probably nice to have is going to be a date where we updated it so we can just add a simple text box on the bottom just going to copy and paste this one maybe put it down over here it's simply going to say whenever we updated it so you can see there that it says last update January 20124 it's just a bit of a best practice thing so let's get rid of this top part and collapse the ribbon there just to see what things look like so we've made this fully Dynamic dashboard in just a few simple steps now to make more advanced visuals like these that Goldman Soxs makes you can check out this video over here or take our Excel course over here hit the like and the Subscribe and I'll catch you in the next one

Channel: Kenji Explains

Views: 179,980

Keywords: excel dashboard, simple excel dashboard, excel dashboard steps, excel dashboard in 4 steps, interactive excel dashboard, dashboard in excel, beautiful excel dashboard, dynamic excel dashboard, interactive dashboard in excel, excel dashboard with pivot tables, excel visuals for dashboard, excel chart for dashboard, excel dashboard structure, excel dashboard in 15 minutes, make an excel dashboard in 15 minutes, make an excel dashboard in 20 minutes, excel dashboard tutorial

Id: GW6ZjK_9H6A

Length: 19min 41sec (1181 seconds)

Published: Tue Jan 02 2024