Turn Raw Data into a Clear, Interactive Dashboard in Excel

I want to show you how you can take a long list of  data like this that really doesn't mean anything   just to look at it and quickly turn it into this  an interactive dashboard in Microsoft Excel with   this you can quickly click on buttons and drill  down and find the important information you need hi I'm Jamie and welcome to teachers Tech I'm  going to be showing you how to make an interactive   dashboard in Microsoft Excel today and to do this  we're going to go through this step by step and   looking at three different things to create this  we're going to be using pivot tables charts and   slicers to create this and you're going to see  how quickly all this can come together let's get

started this is the data that we're going to be  using to create our interactive dashboard today   if you want to use this data I'll put a link Down  Below in the description so you can download it   and follow along with me to explain this data this  is 2 years of fictional sales for a store pretend   store that sells hoodies and t-shirts for a superh  heral company we'll call it the superhero Hub now   let's get started with creating our dashboard the  first step that we need to do is turn this data

this range here into a table the reason we want to  do this is later on if we want to add more sales   information we can add it to a table and refresh  and this is going to update our interactive   dashboard as well so to do this we can go up  to insert and click table or if you're clicked   inside this just go ahead and click on contrl  T for the shortcut and it will come right up   and it will have the right range in there so your  table does have headers you can see them across   the top I'm going to hit okay now I want to give  a name to this uh table as well so I'm going to go   where it says table four up here I'm just going  to call this sales and we'll leave it as that   I'll just hit enter now I'm ready to start with  pivot tables so I'm going to go up to insert pivot

table and I'm going to go from table or range  and click on it here is the table that I just   name sales and I'm going to put it on a new  worksheet we'll have several different worksheets   at the bottom uh for these different tables and  charts that we're going to create and then we'll   hide them later on so I'm going to go ahead and  click okay on a new worksheet and we're ready   to add the fields that we need for our first chart  the first chart that I want to create is a monthly   Revenue Trend and we're going to do a line chart  you can make adjustments to the different type of   charts if you want to show something else now I'm  going to go ahead and just rename this sheet down   below I'm going to call this Revenue Trend uh if  I click off notice that the side goes away you   just need to click back in this to open it back  up to see the fields so now I need to add fields   to this to get the correct data over here that I  can create the chart from and I'm going to go and   select month to begin with I'm going to drag it  down and put it into rows so if I drag this down   into here you'll notice this information it goes  over here now I need more information as well so   I'm going to put uh also some columns in here and  I want it to be the product category so I'm going   to have the hoodies and t-shirts so if I look for  my product category here I'm going to put it over   here into columns if you want to learn more about  pivot tables I have a whole tutorial on that that   I'll put down below in the description a link you  can check that out or in the card now the other   thing I want to have is values right now notice  there's no numbers over here so I'm going to do   Revenue so I'm going to go and take revenue and  put it into values so just like this now I have   all this information and notice I can expand here  too so if I just click it shows me the quarters I   don't really want to have quarters it just broke  up the years uh for me for this and so I'm going   to go back over here and just uncheck The Quarters  here and you know you can play around and get the   look that you want so if I didn't want to have uh  the months like this I could click on here if you   don't want the years you can see the differences  but I'm going to leave it like this and we're   going to go and create a chart from this now if  I'm clicked inside this pivot table over here   you're going to notice that we have pivot table  analyze up top in the menu and if you have that

selected inside the ribbon you're going to get  pivot chart so if I go ahead and click this it   defaults to the column and maybe columns what you  want to use and you can pick what chart that you   think will work best for you but I'm going to use  a line one so I'm selecting this one I could hover   over the different ones but I think this first one  is going to work best for me so I'm just going to   go ahead and hit okay now first thing I want to  point out notice that right here it doesn't have   a title you can customize the charts if I just  hit this plus right here notice if I wanted to   add a chart title I could click on it then I could  go over into here and give it a title like this   monthly Revenue Trend so up to you if you want  to show that or not if you want to learn more   more about charts I'll put a link to my tutorial  down below about that so I have this information   notice I can stretch it out here to see more and  when I move this over to my dashboard I can make   it fit the way I need now the other thing I want  to add on this is going to be a slicer and we're

going to move slicers over to our dashboard  when we're done and connect them all together   so making sure that I'm under the pivot chart  analy analyze here we have slicer so the slicer   I'm going to add on this page is going to be the  different states so I can check to see how each   indiv individual state is doing so I click on this  I can click on State and hit okay so notice now I   have just these five states selected there're in  blue I can select them all and then it's going to   show me it I can select one and it will show me  just Florida so I can quickly look at this date   so it's a filter that it just gives it I can  quickly click on any of these or multiple ones   to see the revenue monthly Revenue trend for that  specific state or states for my next pivot table

and chart I want to compare hoodies and t-shirts  over the two different years to see units sold so   I'm going to go back to example data making sure  I'm clicked in here insert and it's going to be   another pivot table all of this same as last time  on a new worksheet I'm going to hit okay I want to   call this something else just to keep things  organized and I'm going to call this t-shirts   versus hoodies so now I can go and add information  by selecting the fields so what did I say wanted   to compare well let's start with the category here  I'm going to put this into columns and you can see   right away we have hoodies and we have T-shirts  I'll make this a little bit larger here and the   other thing that I want to add is going to be the  year so I have years here at the bottom I'm going   to put this into rows and the last thing I want  to add is the units sold you could change this if   you wanted to compare something else with them but  I'll put units sold into value so right now I have   this quick way to look at 23 and 24 and see what  the total units soldar let's add a chart to this   so I'm going to go and up top to Pivot chart again  and you know what I like this one this is easy   to read I can see 23 24 I can compare t-shirts  to hoodies really quickly I'm just going to go   ahead and hit okay so like I showed you before if  you want to add a title to these you can do that   or make any adjustments but I want to go ahead  and add some slicers to this uh and I'm going to   go back to click in here we're going to go to our  pivot table analyze add slicer and we're going to   add let's say we want to add years to begin with  so I'm going to hit okay so notice I can move this   around anywhere I can change the size so even if  I didn't want to have all that white there I could   bring this up here the other thing I could do if  I right click on it we have size and properties   slicer settings so if I open this up notice here  if I don't want to have the year's month display   so if I click off of it hit okay now it will just  be a year one I can put this back and I'll decide   later what I want on the dashboard but I'll go  back to slicer settings and then just click this   on again so the other slicer that I want to add  here is one for month so let's say I go back up   to my slicer to add it pivot table analyze  slicer and I'm going to pick this one notice   there's a couple different uh months I want to  pick this one down here because it will just have   the months labeled uh normally this will reflect  the column uh how it's formatted there uh in the   actual data so I'm going to hit okay and I have  my different months so now I could look at this   information at 2024 I could  look at it 2023 highlight both and see it here   I could also look at March of the two years  compare things specifically so it gives you a   good way again to drill down in the information  all right let's create one more pivot table in   chart then we'll get over to our dashboard and  put everything together now for my last okay

for my last table and chart what I want to do is  find the top five states by profit so I'm going to   go and insert a new pivot table on a new worksheet  and let's go ahead and rename this top five states   and what do I want I'll zoom up so we can see this  a little bit better again I only need two things   the state is going to go into the rows and then  we're going to have the profit I mentioned so we   have the profit and the states we can format this  as well we can simply go back to home to format   it uh you can also rightclick and then if you  format CS you're going to see how you can adjust   the different formatting for your CS there as well  but let's go back here now I have all the states   showing up I only want let's say the top five  if I do the drop down here I can go value filters   and then I can go and pick at the very bottom  we have top here we have top notice we have some   different changes if you on to the bottom but  I'm going to go five and we'll keep everything   everything else the same and now we have the top  five so when I add my chart this time so if I go   back to Pivot chart here we have actually this one  you know again this is easy to read I can see the   numbers well uh the states I think I like this  one again pick which one works the best for you   I'm going to hit okay do we want a title on this  I could double click in here since they gave me   the option here so I'll just put the same thing  top five states we'll say bu profit here the the   other thing I want to point out if you wanted  to have different increments or anything here   if I just doubleclick in here uh on the side  here we have these different options that we   can uh change uh all the different ones so under  the options here notice that here are the units by   a th000 so if I wanted it by 5,000 I could adjust  how far they uh between each one here as well I'm   just going to leave them I just want to make sure  that you saw where the differences are so now we   have our three different carts and we have some  slicers so let's bring them all together onto a   dashboard before I create my dashboard I'm just  going to create one more slicer here I'm going to

click into my pivot table to pivot table analyze  and I just want one for product category just so   I can switch between hoodies and t-shirts quickly  okay I think I have everything let's go now and H   click on just the new sheet here and we're going  to call this one uh dashboard just like that and   the other thing is I think I wanted the front  so I'm just going to drag it over to the   front so the next step is just taking all of  these different things the charts and slicers   and moving them over to the dashboard page so if  I select this I could go contrl c contrl v for   copy paste or I could contrl X to cut it so I'm  going to cut it but rather than do one at a time   I'm going to hold shift down and now I have this  selected the slicer selected and this chart so   I'm going to go control X it cuts it from there  I'm going to go to the dashboard and controll V   so now I can go and start placing these Loosely  where I want I'm just going to move everything   over to begin with and then I'm going to make it  more graphically appealing so I'll go to t-shirts   I'm going to go and hold down shift as I select  them all here and then I'm going to go control X   and go to my dashboard and paste it and I'm just  going to uh put them kind of loosely where I might   need them again here and I'm just going to put all  the slicers so we have all the different slicers   here we have this chart and  we just need to go grab our one more so let's   go control uh control X go to our dashboard make  sure you're clicked off of this so I don't want   to replace that one and control V so now I have  all my information let's make this look a little

bit better now first thing let's check the slicers  let's go ahead and select a few different states   to see uh what's happening with the slicers so  if I select these five notice this one just moved   if I select a few months here well this chart  just here moved if I select hoodies it was only   this one if I select both again it's only this  one that's because they're not all connected   and you can connect them so that when you select  one of these slicers it will make   the changes in all charts if you rightclick on  them if you notice we have report connections   if I click on this and just stretch this out I  need to make sure that I hit okay and you need   to do this on all of them so if I quickly  go through and make sure all the different pivot   tables are connected in here just by making sure  you stretch them out so you can see all of them   and I just have this one more to report the  connections too and now if I go and use any of   the slicers notice they all reflect each other so  they're all different connected so if I wanted to   see the year now I'm looking at 2023 Texas those  four months and of Hoodie and t-shirts now it's   just hoodies so they're all going to be moved and  connected to the slicers some other formatting I   might want to do with the slicers would be this  if I go ahead and just right click on them and I   open up the size and properties notice over  here I have some different choices I can uh   make sure that they're all going to be the same  width so if I want to say you know what I want   them all to be 4. 5 I can adjust them to each  one like this so now I could go to my next one   and I don't have to open it up each time because  it's already open and just quickly go and type in   that and then they'll all adjust because sometimes  when you're dragging them it's hard to get them to   the exact match of each other this way it makes  it easy the other thing that you can do with   your slicer maybe I don't want to eat up this much  room with all the different months on this one and   I can go and choose to say you know what I want  two different columns so now I have a little bit   more room that I can size this and as I showed  you before maybe we don't need to have the column   headers on these because it's pretty self-evident  that what these are so I can go through and go to   my uh sight the size and or sorry my slicing uh  settings here and make sure that I can just click   off on all of these so go through and set those  slicers the way you want and then I'm going to   start moving some things around if you take a look  at my slicers I've start lining them up around the

10 row here uh the other thing I point out I  took off all the headings off them this is all   optional depending what you want and if I just  right click on this and go to the slicer size   and properties with position and layout I checked  off this disable resizing and moving I can uncheck   it if I do need to change it but I can't move  these right now or resize I just want to kind   of lock them in place now some other things with  formatting I could do with any of these charts   if I click on any of these I can go to design  and you'll see that we have all these different   options if I hover over how you can change them  depending on what look you want I'm just going to   keep them pretty plain Jane here so I'll just uh  line these up things a little bit and I'm going   to put them around maybe on the fifth row here and  about halfway through this one cell I'll take this   one here and just give it a little bit of space  in between I'll put this one down below here and   I'm going to just stretch it out now if I wanted  to add a TI tile to this what I can do is if I go   to insert I could type in a Cell just like normal  in Excel but I'm going to go in put in a shape and   I'm going to use the rectangle with the rounded  corners and I'm going to just draw this out like   this here and I can take the round off the corners  a little bit by just dragging this yellow back and   if I want a little bit more Square so if I go and  just put in uh let's say I put in a title here   I'm going to call this heroic Insight so I'll  type this in uh just like this heroic insights   CU remember what the store is about it's about  uh superhero paraphernalia with shirt t-shirts   and hoodies and I'll also put the year here let's  say 2023 2024 so this will be our sales data I can   make this larger here by increasing the font just  like normally and I can even change my if I wanted   to have a different font like this so I'll leave  it like this here and now what else can I do with   this well let's say I want to add something to the  side here so if we want to have more maybe this is   good the way you have it I could insert another  one now and if I go back to my shape and I'll use   the same shape maybe I draw another one right down  here to the bottom and now I can take this yellow   I'll just make this a little bit like this but you  you're going to be thinking well you're covering   this well if I right click on this what I can  do is send to back now this is over so maybe I   want to uncheck all these and move them way from  The Edge and place it uh just so I have the same   spot space on each I can still size this up a bit  to match all of these maybe I want to add a logo   so under insert we can go to pictures I'm going to  place oversell here and choose from this device we   have a superhero one I created with AI so here it  is much too large here my superhero Hub I'm going   to size this down and just place it up here now we  can keep doing more things to this you have a spot   down here where maybe you want to put another  uh another chart you could place in or move   things around we could also make things larger  still so even if I wanted this to be slightly   larger like this to stretch things out and I can  do the same thing with each of these charts here   here so you can really maneuver things exactly the  way you want if you want to add backgrounds to any   of these also I could go back to insert go to my  shape and then let's pick this and as an example   I'll draw this over top of this one here and I'll  just size this up a little bit I'm going to nudge   it over with my arrow keys and like I showed you  before you could send this to the back now so if   I rightclick I could send to back and then I could  align this chart inside of here so you have lots   of different options on how you want to decorate  it the other thing that you can change here is you   see all you have all the cells showing you can  turn those off if I go to view and we have grid   lines if I just uncheck this now if I was starting  to fill these up with a bunch of colors you can   see how it looks better without the grid line  and I can adjust this maybe I want to go right AC   cross here and I can shrink this back and get  everything matched up I could add a different   color one the other thing is once you get all  these colors in place what you can do is if I   go to page layout so I've only picked this one  color right here but if I drop down on my themes   look what happens when I hover over all the colors  everything comes right through so if I wanted it   to be a different color I can just simply click  on a theme notice that the fonts change the colors   changed so you can quickly adjust your entire  dashboard's look I made a couple adjustments I   just centered all these slicers in the middle here  I added a little bit of gray at doing the same   thing that I showed you before like I said you can  keep on changing this adding different graphs if   you want to change any of these different charts  you can just rightclick on them and you're going   to see that we have change chart type so if I  wanted it to be a bar or any other ones I can hit   okay and it makes those quick changes the other  thing I want to point out is you might want to   share this with people so you go up and you can  hit share and add the people just like normally   how you would share in Microsoft Excel then they  can go through and start digging down in the data   making it in an easy way through this interactive  dashboard but maybe you want to hide all some of   these different worksheets down here you don't  want to have people just look at those and just   keep them on the dashboard if I select Revenue  Trend and hold shift down and select example data   actually I'll do that one more time hold shift  down and select them all like this then you notice   they're all selected if I rightclick and go hide  now you don't see them so if you were sharing this   those all those worksheets would be hidden if you  want to show them again if I just rightclick and   go to unhide you'll get the options of which ones  you want to unhide so I could select them all hit   okay and then they're going to be back again what  happens when we get new data what happens to the

dashboard let me show you how you can add some new  data and then refresh it I'm going to make a quick   change this chart I just noticed something and I  didn't notice it before I'm going to take out this   month this will give it a little bit cleaner look  across the bottom here just showing the years and   the months just like that written all right let's  go over to the example here and what I'm going to   do is just paste in some more information and  notice the date it's 2025 January since this is   in a table format I can refresh this  and it's going to update on my dashboard I'm   going to go over to the dashboard I'm just going  to rightclick on this slicer here notice I have a   refresh option as soon as I do that look at  this we have 2025 if I go and select all of these   now let's say I select to 2025 notice on this  chart we have 2025 now it's been updated so I   can start looking at the newest data through  January so even if I just picked uh 2025 by   itself only January comes up so what do you  think this is pretty easy right now you get   this interactive dashboard where I can look at  let's say this chart for 23 24 I can see where   there's fluctuations and I get an idea that that's  happening in the summer so I can see t-shirt sales   going uh up in the summer and hoodies going  down and vice versa so I can see patterns in   this very easy or looking from state to state  state is this something you could see yourself   using especially with sharing data with others  let me know Down Below in the comments thanks   for watching this week on teachers Tech I'll see  you next time with more Tech tips and tutorials