Build the ULTIMATE Excel Dashboard from Scratch

in this video we're going to build this  interactive sales dashboard on Adidas that   dynamically updates based on our preferences to  do with the year the product the region and more   first we'll go over the data and create pivot  tables to analyze the relevant information then   we're going to create charts and different visuals  for the dashboard thirdly we're going to design   the actual dashboard from scratch and lastly we're  going to link everything dynamically so we have an   interactive dashboard all right let's get into it  so here's the data set we'll be working with and   you can download it in the description below for  free so over here you can see that we've got the   Alia sales database with all of the different  line items that are going to be under mainly   Financial ones and from there if you go to control  page down you're gonna see all the different tabs   where we're going to have a lot of these visuals  and on the very first tab we're going to have the   actual dashboard so first things first let's go  ahead and start analyzing a bit of this data so   we're just gonna go to insert then we're gonna  go to pivot table and from here we're going to   want to put it in an existing location and for  us that's going to be firstly let's go over to   the seeds Trend tab so we'll click over here and  let's say the location we want is right here hit   OK there and first thing we want to find is the  actual sales amount by period so in a specific   month how much do we sell now to do so we're just  gonna go ahead and select the invoice date which   is going to give us all the dates as the row and  then towards the values here what we're going   to want to put is the total sales which is the  revenue for us now let's remove the quarters over   here which automatically fills in so we'll remove  that and let's also open up the ears so we're just   going to press the plus sign over here and same  thing down below let's go ahead and reformat these   numbers so we're just going to right click here go  to number format and from there we're just going   to put it in accounting terms let's say we put  it to zero decimal places and hit OK there now   that's a lot easier to read now that we have the  data let's go ahead and create the visual for you   so we're just going to go inside the table over  here go to the pivot table analyze Tab and click   on pivot chart and over here let's go ahead and  create the line chart that's going to show us the   overall trend hit OK there and so there we have  it don't worry too much about the design of it   for now we're just gonna go ahead and analyze more  data first so going back to the data Tab and over   here we now want to see some kind of a map where  we can see the different sales depending on the   region within the United States so has there been  more sales in California relative to New York for   instance now to do this we're just going to create  a new pivot table so we'll go to insert pivot   table again existing worksheet and the location  for this for us it's going to be under the   sales regions and we're just going to put it down  over here hit okay great in here we're gonna put   the different states so we'll go ahead and select  all of the states over here on one side and then   we're gonna put the the total sales again on this  other side now we've got these two these two areas   now in order to make a map chart like we mentioned  earlier we can't just select this data and instead   we're just gonna have to link it because it can't  be in a pivot table so we're just going to link   this one over here then Ctrl C and we're just  going to drag that all the way down to the bottom   let's see that's gonna be around rule 50 so it  should be right over here Ctrl V to paste that   and then from here we're just gonna label this so  we'll label it the state and here we're going to   label it the revenue or the sales actually let  me just change the labels here a bit so Ctrl B   then we'll go to alt h and we're just going to  select say a dark blue color and then alt h f   c and this is going to be for the the background  color over here now you can see that we've got it   well labeled so once we have this data just go  to control a that's going to select everything   for us we're gonna go to the insert we're gonna  click click on maps let's go with the field map   click OK there and now this map should generate  for you press Ctrl X just because we're gonna move   it all the way up to the top and then Ctrl V over  here now this is how the chart should be looking   like for you if it doesn't generate a specific  map for instance go to right click go to format   data series and over here you should be able to  select the map area that you want maybe you only   want a specific country or a region or even the  world map in our case we're fine with automatic   as it seems to have worked okay another data point  that would be nice for us to find out is the sales   by different retailer so should we focus more on  footlocker or is Walmart actually a better seller   for us now to find out just go control page up all  the way to the data set again let's go all the way   to the top here and we're going to insert a new  pivot table so we'll go insert pivot table again   existing worksheet and this time around we want it  to be under the retailers so we're just going to   put it over here and hit OK and again we're just  going to want the retailer on one side and we're   also going to want the the sails on the other side  so now we have the sales by different retailers   and to visualize this let's create something like  a bar chart so we're just gonna go to charts again   and this time instead of a column or a line chart  we're gonna go into the bar and hit OK there   but you'll notice that these are in a very good  order it'd be nice to have them either going from   higher high to low or from low to high now to  sort this just click inside of this area go to   right click go to sort and let's say we go largest  to smallest so that's what it's currently looking   like actually it probably makes more sense to go  the other way around so go click inside it let's   go to right click sort and we're gonna go smallest  to largest and let's also right click number   format we're just gonna put this in accounting  format so it's more consistent as well zero   decimal places is fine for us and we're here okay  one last thing to do with analyzing the data set   is finding out which sales method works best for  us so is it better if we sell in-store or online   for instance now to do so we'll go to control page  up again and over here we're just gonna create a   new pivot table so we'll go to insert pivot table  again existing worksheet and this time around we   want it in the sales method so we're just going  to click over here and hit OK here we're going to   want to scroll to the bottom where it says sales  method so that's going to be on the row and then   we're going to have the total sales to be on the  values just to make it consistent now we only have   three values here so it probably doesn't make  that much sense to do something like a column   chart or a bar chart just because there's not that  much data so instead we're going to create a donut   chart to do so we'll go to Pivot chart and then  down towards the bottom you're gonna find the pie   and instead of a regular pie we want the donut  which looks somewhat better in my eyes at least   hit okay there and we're just gonna move that  over here and now that we've created the pivot   tables and the respective charts let's go ahead  and work on the design of the dashboard so we'll   go to control page up all the way to the top sheet  and that's going to be the dashboard so this is   currently what I have and it's basically an image  of a US flag over here if you ever want to insert   an image you just gotta go to insert from there go  to pictures and select where you want it from then   next to that I just made a simple line so it's  just gonna go under insert shapes and from here   I just selected a line for Align for us and then  lastly over here on the side I just did a text box   which has the relevant information for us and if  you want to learn more about financial dashboards   like the one we're creating I'd recommend checking  out Financial Edge which provides a certified   online finance courses and is also sponsoring  this video their best-selling investment banker   course goes over all the relevant skills you need  to succeed in an analyst role each lesson has an   in-depth video alongside a downloadable Excel file  for you to follow along among the key exercises   they offer are creating professional Financial  models on Excel learning the main valuation   methods and evaluating measures and Acquisitions  and if you're interested in other areas of   Finance they've also got courses on private  Equity trading portfolio management and more   so if you're interested in checking them out  go to the link in the description below where   you can get 25 off using Code Kenji 25 all right  back to the dashboard now that we have this area   filled in let's go ahead and start working on  the different shapes which are gonna hold all   of our charts so let's go to insert then from  here under shapes we'll go for this rectangular   around the rectangle so click inside that and  first I'm just gonna go ahead and drag that and   put it more or less around here and this one's  going to represent all of the different filters   that we have so up until around row 33 is going to  work for us let's close down a bit of that around   this just by clicking on the orangish area there  and let's change the colors in this case we want   it all in white so I'm just gonna select a white  fill and for the outline I'm just going to put no   outline for us now in here we're just gonna start  writing in this case we're just gonna put settings   now in case you don't see it it's probably  because it's in white right now so just go to   home and we'll change the color of that to a black  press Ctrl B to bold in it then I'm just going to   go Ctrl shift and then do the greater than sign  and that's going to make it slightly bigger for   me let's say around the 18 Mark is good enough now  that we have that one selected we're going to want   to add a bit of a shadow on the back so it stands  out a bit for this we'll go under shape format   then under shape effects we're just gonna go for  a simple Shadow like this first one over here   now that's looking a bit more like it now in  order to copy and paste this to across all of   the different shapes that we'll use we're just  going to press the Ctrl D key that's going to do   it for us firstly up top over here we're going to  create the trend line so let's make sure that this   area is somewhere long more or less up till here  and we're just going to drag that a bit lower down   great press Ctrl D again and then just below  that we're gonna have two more visuals so I'm   gonna put one more or less over here let  me make sure it's kind of aligned there   and this is going to be about half the  distance make sure it reaches more or   less the same size Ctrl D again and we're just  going to move that one across kind of like so   and then press Ctrl d one more time and we're  just going to create one long one over here   which is going to be the bar chart for us in this  case for all the different retailers let's say I   shrink this just a bit more great now up over here  in this empty area we want to create some kind   of a note that's going to show that hey this was  last updated on this date for instance so I'm just   going to click on this one press Ctrl D we want  to change the shape of it as it doesn't really say   the same type of information let's say I go for  this grayish color kind of like so and I'm just   going to make this a lot smaller so kind of like  so there and also close this down on this side too   so over here that's where we would have the  different nodes so let's say I put this as   a control a first and then type note two  dots and say something like last updated   on say the 2705 and this area over here we can  remove the ball done by pressing Ctrl B and then   Ctrl shift and then the smaller than sign just  over here to the side and then over on this side   let's put some kind of an icon that's going to  make it look a bit more fun for us so press Ctrl   D there and then let's go ahead and Shrink this  more or less to the remains a perfect square and   I'm just going to put it over here to the side  Ctrl a to select everything in there and just   press delete instead what we're going to do is  insert an icon so we'll go to the insert tab   under icons I'm just going to look for something  like a clock that's going to represent time   so I'm just going to type clock in there let's  say we're happy with this one over here so I'm   just gonna go to insert then to make this one  a bit smaller just press the shift and then the   down arrow that's gonna do it for you and I'm  just going to move it over here kind of like so   so more or less this is looking a bit more like it  let me just fast forward how I clean it up a bit   awesome now that's looking a bit more professional  to show you what I did here I just went ahead and   changed this color to a red and also put this in  a different line and from there I went ahead and   aligned these just a bit better where it's  easier without the camera in front of me   now that we have the initial design for the  dashboard let's go ahead and import all of   the different charts and visuals that we made  so we'll go to control page down let's go all   the way to the first one which is this one on  the sales Trend now firstly we need to re-edit   this so just make it look a bit better we don't  need the total title nor do we need the legend   here so we'll just delete that same thing with  these great things we're just gonna go to Pivot   chart analyze click on fields buttons and they're  gonna be removed there then let's stretch this out   a bit more and let's change the chart design  by going to the design Tab and from here let's   say we're happy with this fourth style which it  looks a bit different and let's click inside the   line charts here what we want to do is change  the different The Fill color to a darker blue   like the one in the dashboard so I'm just gonna do  that same thing goes for the actual lines itself   so let's go ahead and click on them and from here  under the line we're just gonna go under the dark   blue color and that's looking more like it once  we're happy with it we're just gonna press Ctrl   C to copy it then go to a dashboard Tab and  hit Ctrl V that's going to be this very top   part for us over here so I'm just going to move  it across let me fast forward how I go about this all right so a few things we need to change here  is firstly we don't really want the borderline so   in case this isn't showing for you you're going  to go to right click and format chart area so in   my case we don't I don't want the border so I'm  just gonna delete that and I don't necessarily   want a fill as well so I'm going to delete that  as well so it's going to look look a bit more   spacious for me and let me just move that a bit  and up top here we're just going to put something   like sales in USD for us and that's the trend line  for us done then let's go Ctrl page down all the   way to the regions in this case so that's the map  chart let's go ahead and edit that as well so in   this case we don't really need the the chart title  either so we'll delete that as for this part it's   debatable whether it's necessary or not in my case  I'm just gonna say we don't really want it so I'm   just going to press delete from here let's change  these colors a bit so we'll click inside this go   to right click go to right click sorry from here  go format data series and serious color is where   we want to go for that inside it the highest value  will put as the darker blue we've been using and   the lowest value we're just gonna go with a  plain white great now what now that we have   this Ctrl C to copy let's take it all the way  back up to the dashboard we'll paste this one   say over here to this side Ctrl V let's make it  a bit smaller there and try to autofit it inside and again we're going to want to remove the  Border here so no line and no fill either   then for the title let's  say we go by sales by region   great now that's looking more like it then next  to that we want the donut chart so let's go find   that under the sales method this is the one that  we're looking for and again we're just gonna go   ahead and start deleting areas in this case we'll  go to paper chart analyze click on the fields   button but we don't really want to delete these  three as they're actually quite relevant for us   instead we're gonna click inside the donut chart  right click and from here format data series and   what we want to do is make the donut hole size a  bit smaller so the columns actually look thicker   for us which I think is a bit of a better look  Also let's change the colorings so first we'll   select this blue one from here just go to home  and under here you can select the dark blue in   my case then next to that one let's say I go for  a red color and then for this very last one let's   say I go for something like a uh a blue color  like so then we can copy this so Ctrl C take it   up to the dashboard and I'm gonna paste it over  here to this side so Ctrl V let me reformat that great now for the title we can change  this to something like sales method   sales method and then now we're missing this  very last bar chart which is the one that we   made towards the very beginning so we're just  gonna go on the retailers and it's gonna be this   one over here let's delete the title layer let's  also delete the legend and we don't really want   this either so people try to analyze again Fields  button now that's looking slightly more like it   let's go ahead and click on the different bars  here and for the fill color we're gonna change   it to the dark blue like the dashboard and  then we can just remove these grid lines as   well which we don't really need and then once  we have all of that Ctrl C to copy we're gonna   take it over to the dashboard Ctrl V we're gonna  have to resize this one as well so it fits one and for the title here let's change  it to something like sales by retailer   great now let's take a closer look at how  everything is looking and it's starting to   look more and more like it the one thing that's  currently missing with the dashboard is that it's   not Dynamic so we can't go ahead and only see data  for 2020 just like we can't go ahead and only see   data for the West Coast but we can go ahead and  change that so firstly let's go to the sales and   USD this chart over here just make sure you click  inside it let me close that here we're gonna go   to paper chart analyze go to insert slicer and  here we're gonna select all of the different   slicers and this is going to be the settings that  we can change it's some kind of a filter basically   so in our case we said we wanted one let's say we  want one for the years we also want one for the   products so how does a different selling a shoe  versus selling a shirt change for us and then   lastly let's also put one for Regions so the west  coast East Etc hit okay there that's gonna give   you three different ones let's go ahead and move  these These are basically going to be over here to   the side now you'll notice that they're actually  quite long so instead we're gonna collapse them   into two different columns to do so we'll just  go ahead and select one and under buttons here   we're just gonna put two columns so it's gonna  look a lot cleaner for us now for the design of   these we also want to make it a bit more different  so let's select all three of them and here under   slicer you're gonna find there's different slicer  Styles so you can select like an orange one etc   etc in my case I already made one so it's gonna be  this one over here now go to the drop down and you   can create a new slicer style that maybe matches  your organization to do so you can have all of the   different things that you can change down over  here hit okay there for now because we're happy   with this one let's start moving the ears and all  of the other stuff as well you'll notice for the   years here I have these three strange ones towards  the bottom which don't really make much sense now   in order to remove them I could just go to slicer  settings and from here we're gonna hide the items   with no data hit OK and now we it's looking more  like it let me fast forward how I move this across nice so now I've got all of the different slicer  settings here on the side hopefully you're able to   follow along so for the year suppose I change this  to 2021 you'll notice that this whole trend line   changes same thing if I go for different regions  that's gonna change as well that being said you   might have also noticed that nothing else is  actually changing it's only the stop part that's   because all of these parts aren't exactly linked  as of now because we only took the slicer from   here so let's go ahead and Link them we'll first  select the slicer go to slicer and we're gonna go   to report connections that's going to allow us  to link everything so firstly we're gonna just   select on all of them hit OK there and same thing  goes for these other ones we're gonna select all   of them on the report connections click inside  that select everything hit okay same thing goes   for the product report connections and select  all of them hit okay there and now if we remove   all of these filters everything should be changing  with it as well as you can see over here and just   like that we've built ourselves an interactive  Excel dashboard where you can select different   regions and see all of the sales data for that  area same thing goes with the products and the   years for more on Excel check out this video  over here to learn about Advanced pivot table   features hit that like and that subscribe button  if you liked it I'll catch you in the next one

Channel: Kenji Explains

Views: 410,047

Keywords: excel dashboard, interactive excel dashboard, advanced excel dashboard, dynamic excel dashboard, the ultimate excel dashboard, pivot tables, slicers, advanced pivot tables, map chart, doughnut chart, interactive map chart, line chart, bar chart, pivot table slicers, excel dashboard design, create an excel dashboard from scratch, excel icons, analysing excel data, excel data analysis, beautiful excel dashboard, pivot charts, dynamic pivot charts, interactive charts

Id: 5eLtjO2Hfs0

Length: 20min 41sec (1241 seconds)

Published: Sun Aug 21 2022