How to Create Dashboards in Excel

in this video I'm gonna go over how to make a professional-looking dashboard without the need of using any complex formulas or programming or or coding and so I've got a sample database that you can download if you want to follow along and so I'm going to do is use this data set to create some pivot tables and then link them together in one-one dynamically updating dashboard and so I've already set up a named range for my data sets so the first thing I'm gonna do is make two tabs one for the data and the pivot tables and the other one I'll just call the dashboard and so on the data tab I'm gonna start by creating a pivot table and my named range is data set one and the first thing I'm gonna do is get all the sales by month and so just plop it in the dates on the roads and the sales dollars in the value section of the pivot table now Excel 2016 are automatically grouped my dates in a months and you can change how it's grouped if you right-click on the row labels and select group it gives you an option so you can see days and once are selected I could actually uncheck days because I just want to see the months so but if you had multiple years you want to compare against you can click off years as well just gonna leave it on once and hit OK and then the sales sales numbers I'm just gonna change the formatting on this value field settings number format and accounting just so that way it looks a little looks a little better when you know looking at that the number isn't easy to see thousands and hundreds and so now that I've set on my pivot table the next thing I want to do is turn it into a chart so there's a pivot chart button here I'll click on that and I'm just gonna leave it with the clustered column by default now the only thing I don't like pivotcharts is they have these ugly gray buttons here and so if I go to the analyze tab I can unclick the filled buttons and that disappears and because these are totals I don't need a legend and what I'm also going to do is fix the gap on on the data one with the data series I'm gonna change the gap width here now you can't see it but I'm changed at fifty percent so it's a little fatter and add a label for total sales bold it and for the border I'm just gonna get rid of the outline and so now got my first chart set up I'm going to copy this over cut and paste it to my dashboard it's up there on there and what I'm also gonna do on on this tab let me get rid of the gridlines okay and now that I've got that set up that's my first pivot table what I'm gonna do is now make another pivot table based on the same data set and in this case I'm gonna look at sales by product so I'm gonna drop in sales dollars again don't put product in the row section and again I'm going to change the formatting so it's like counting formatting and now again I can convert this into a pivot chart again this time I'm gonna go with a 3d pie chart and again I'm gonna cut and paste this into the dashboard right away and I'm gonna go through same stuffs I did before getting rid of the field buttons getting rid of a border and then what I'm also gonna do here is I'm gonna add data labels and of course well not these data labels so I have the category name as well as the percentage obviously you can set up however you want I just want to get rid of this this legend is not really useful when you have this amount of categories and now here I'm going to name this sales by product and I can put this a little bit higher there we go so that one's downs nicely in there so that's for the table too I'm gonna do a couple more so I insert another paddle table and the great thing about it named ranges and out there select the range anymore and so this one I'm gonna do sales by Stuart I store my data sales and now again I'm going to change my field settings so the number format is accounting again and ready to convert it again into a pivot chart and this time I'm going to do a bar chart just so it's a little different than the other ones again cut and paste onto here and again go to analyze get rid of the buttons I'm gonna change the gaps again for my data series I normally set the gap width to 50% and again I cuz these are totals I don't need a legend and I'm also going to give her the border again and because this is already already blue I'm gonna change the change the color scheme to orange and it's called this sales by store okay so that's good and now I'm gonna do my final pivot table go to the side you want to make sure these pivot tables don't overlap because then if by chance when you run a filter and it expands it that it's gonna give you an error message so should I be careful about that if you've got a lot of rows and columns and you wanna you might want to put them on separate sheets that is that one again and this time there's gonna be my most complex per table it's gonna have sales rep and store type so that's the stores here cells are up in the columns and then I once again total sales and then again I'm going to change the value field signs again the reason I go through field settings as this change is that even if I refill tur it because you can select the entire column and change it to accounting but then when you rerun the pivot table when you refresh it or apply filter to it then then the formatting is gonna revert back to what it was so don't my pivot table setup I do again insert a chart and this time I'm gonna do a stacked contra so that way I can see a different makeup of the other stores and get rid of the buttons again and I'm going to change the design of it so that the legend is at the bottom okay and again I'm going to do control X control V and again I'm gonna give my border and in this case I'm gonna add a chart title of the chart I'll call this sales by rep and store and with with this one set up this one's those companies I'm gonna try to stretch it a little bit more so you can see it and again I'm going to change aren't the data series again to give her that gaps a little bit okay so the pivottables and pivotcharts they're all set up to go but right now they don't really do anything special they're just regular regular charts but what I'm gonna do now is add slicers which will do what sort of make these dashboards oops turns them into turns them into you know dynamic dashboard that I'll update right away based on your selections so if I select one of these of these charts I'm gonna go to insert analyst hood slicer I'm gonna hit you know want to store sales person products and I actually want month in there as well but grab that in a second so that's store in here and so when you're writing a slicer I mean you can see right here this is not really practically you want to stretch this all the way down just to make a selection but under options you can put the number of columns you want so I'll put three combs because they're not really long strings so it'll fit easily same thing for sales rep I preferred so it's across the top here I could even do four columns their product same sort of thing I'll try to line it up a little bit here okay and then the one thing I was gonna add is I forgot I should have added yeah make sure that all these have a date linked to them there we go so that way when I'll take the pivot table it factors in date as well Sarge's answered another slicer for the date so I'll make sure they all are updated based on the date selection so now all of these slicers are now set up obviously can space out a little bit more however you however you want for the sake of appearances so I'm just gonna make it so that all the charts fit nicely on here okay and stretch this one out a little bit more so I get all the months in there oh I know with slicers if you see options like this that are faded out that just means you can't make that selection that you don't have any data that those filters will be able to pick up so if you're wondering why some of them might be faded out that's why I'm gonna pull these over a little bit so there's no lot okay so that stretch this one out just so it's even okay and I so I've got my charts I've got my slicers I'm so convenient the slicer is you know you select it and it'll filter your your pitchfork based on those selections but to make this really dynamic I'm going to to report connections and one check all the pivot tables on here and this way it'll be linked to every single one of them it's that we want to make a selection or store a now all of my charts change as opposed to just just the one so that's the really cool thing about linking slicers to your charts and pivot tables is they can make the changes across the board so again I'm going to do the same thing for each one of these slicers and the key thing that makes this work is I've got the same range set up for all these pivot tables because if my range is like I used data set one my named range but if that wasn't the case if I if I had different sources from these pivot tables then it wouldn't be able to to connect you know as dynamically to this cuz otherwise now when I make a selection like let's say I wanted to see the sales for stores a B and C so I can take control on my keyboard select B and select C and now my chart updates based on those selections so everything that I see now our sales by product are for stores a B and C I go by sales rep and store you can see I only have sales a stores a B and C where a B and C right and so let's say I wanted to see the sales in these three stores that were made by let's say sales rep a so now I see all of sales rep A's sales in these three stores you know year to date oops a product mix and if I wanted to say C only a few months is there so at April may actually did the opposite here well a second if I select April May and July I guess he didn't have any June and that's why you see June in September or are grayed out there because there's nothing that fits that criteria so now you can see you see all of the sales that rep a made in stores a B and C for these three months so that's the beauty of setting it up with with a dashboard like this and sending all the links up is the user can you know drill down however they like and see the different see the different charts based on their their selections you know have to go one by one so that's that's a quick and easy way to set up a dashboard that you know you can use in a variety of different ways I mean obviously you can select the the charts to look however you want and you know different short types the key thing is using the same data source and and then yeah just a matter of making pivot tables and that's why I also put the pivot tables all on one tab as well just so they're not getting in the way cuz you don't want to see pivot tables on the background on on the dashboard and so so all we found this useful and thanks for watching

Channel: HowtoExcel.net

Views: 89,450

Rating: 4.9319606 out of 5

Keywords: Microsoft Excel, Dashboards, Reports, Excel, Office 365, Microsoft 365, Chart, Charts

Id: JcdORXZjbbg

Length: 14min 15sec (855 seconds)

Published: Sat Jan 27 2018