Google Sheets - Dashboard with Slicers for Pivot Tables & Charts - Part 2

so let's just get rid of all of this and start from the beginning so what I'm gonna do first I'm gonna make a new tab this tab is gonna serve as a copy of my data set that's coming from this data set tab and you'll see why I'm doing this in a couple of minutes so I'm just gonna call this data copy or something and here I'm going to run our query function and I'm gonna go ahead and select my data so I'm gonna go to subtotal select something like this and then I'm gonna remove the end range from this data to have it go all the way down comma so now I'm just gonna write a simple select statement in quotations so I'll do select and then I'll just pick every single column from this data and I'll start with a and B C then D and E then F then G then H right so pretty much every single column comma separated comma and then we have how many header rows just the first row is our headers so one coolest parenthesis hit enter so that takes a little bit it will adjust the range and make sure it fits and then here we go so that's good we have a copy of our data but I want to now add year month that type of stuff so we can use it in our slicer so I'm gonna go back to this and do a comma and to add the year it's gonna be pretty easy we're gonna do year function and then put the column of dates which is the column a and if I press ENTER you'll see we get year out of this stage that's good in a similar way there is a function month so I'm gonna do comma month and then use the same column a and that should give us this so it's really weird how this works because what this mom function does it goes the first month see January it's gonna do 0 so it starts mom's from 0 to 11 instead of 1 to 12 and we can't change that by going here and doing month a plus 1 to add 1 to that result and then we'll get 1 to 12 so that's okay but I want something more user-friendly that's gonna give them like January February this type of stuff so to do this I want to just somehow repeat the same column here of dates in the end and in this statement you can't really just repeat the same column twice so if I just go and type a instead of that month again see it doesn't let me do that because it allows me only to use the column once so to go around this I'm gonna use this function to date around this column a and that will basically just get me another copy of that column but I don't need these I need the actual months out of this so the way I'm gonna do that I'm gonna go here and format that column that I just made which is called to date a so to date a I'm just gonna repeat that and I'm gonna make it in formatting mmm so that will be three digit month formatting if you want the full month you will add another M to this and that will get you the full month so four M's instead of three but for me the short version is good enough so I'm just gonna do three M's I also want to rename this column so this I want it to be here and this I want it to be month so I'll go back to this statement and add label so that should be before format so I'll do label and then the column I want to add label for is that year a and the label I'm gonna use in single coat it's simply gonna be here maybe we'll just type it like this so that renames this two-year now let's try to rename this to month as well so that column is to date a so I'm gonna go here after I did this label year a year and do a comma and list that second column and type a name for this and there this that should take care of that now we have nicer column names so this should be the data set we'll be using for our reporting so I'll go ahead and create a new tab for us I'll call it a dashboard and on this tab we'll be making all of our reports so I'm gonna go ahead and remove the gridlines from here because I don't want these to be in the middle of this go to data copy so I'll go ahead and select all the columns here you go on their data and make a pivot table out of this and that's gonna be existing worksheet I'm gonna click here go to my dashboard and choose where I'm gonna place this pivot table maybe over here I'm gonna hit OK and create this is my pivot I'll go into rows do something like sales rep values will do sales and then I'll go under filters and add sales rep and filter it to have under condition is not empty to get rid of this blank on top hit OK that should take care of that I'll rename this instead of sum of sales I'll say probably just sales resize these columns a little bit to make it look nice but there it is that's our pivot I'm gonna go ahead and add another pivot table by region here so I'll just again go back to this data pivot table and existing worksheet click here go to my dashboard move this out of the way click where the second table should show up that's a good location he'd okay and create and then again on the rows I'll build my pivot table region and value sales and then we'll filter again to get rid of this blank by region and we'll filter by condition and is not empty okay close this you can build more pivot tables obviously if you want until you have all of them and this is all set I'm gonna add a couple of other things here I'm gonna go to this and make a nice chart out of this by brand maybe so I'll go in there data and do actually on there insert and make a chart so it's taking a little time see I'm gonna click wait until it's done okay here it is obviously not what I want I'm gonna do chart by brand so I'll go here and as my first axis I'm gonna click here and select the column of brands in the background there it is and I want to aggregate sales so instead of dates that are here I'm gonna remove that and remove this and sales that should not be here this should probably not be here so we have sales and we gonna aggregate it so we still have all these things here so I guess I'm gonna go ahead and get rid of that here we go this looks more like what we're after just keep everything else the same if you want to learn more about charts and how to build them and make them look pretty I have videos covering that in detail but for this that should take care of this all take this and do command X or control X to cut this out of here that didn't work very well so I'm gonna click and try again command X again go back to my dashboard and paste it someplace where I want chart so I'll move it around resize it pretty much have it sit where I want it to sit let me for now just add a pie chart for this like region sales so illustrate the same table below in a pie so keep in mind you don't want to make your chart directly from this you want to go back to your data set and make your chart from your data set so I'm gonna go back to my data copy and make another chart out of this this time it's gonna be a pie chart so I'll just select that and the labels we need are gonna be region so I'll just go ahead and select that column and the value is gonna be sales again so I'll go instead of this I'll just remove that and select sales column hit OK that's that we will use aggregate to make sure we get the total for each region instead of all that detailed that we had there and that should do this for this chart for me so I'm gonna go ahead and select the chart cut the chart out of here go back to my dashboard paste it someplace in here and resize it to fit where I want it so I'm just gonna move this around so we have some charts we have some pivot tables basically now let's add some slicers I'm gonna click on one of these pivot tables and go on their data add a slicer choose a column now because we have a year and month we can do those so I'm gonna do a year and move it over here I'm gonna repeat this same one more time data slicer this time I'm gonna do month and again move that over here and do one more slicer will do by gender so I'll go here slicer and we'll do customer gender click on this and move it over here someplace alright so now we should be able to filter this so if I go here and do clear choose 2016 that should filter see all the pivot tables and all the charts and I can go by month and select I want to just get April results and my screen is too zoomed in so I guess I'm gonna zoom out a little bit hit OK and there it is that should just filter to that month of that year we can filter this to multiple months if he wanted to and if we wanted a different year we can just switch the year that's gonna update and we can also switch to gender good so that works I'm gonna clear all of this so that's good finally I want to add a couple of totals so to get your totals to also work with this you're gonna have to add them as a chart so I'm gonna go here and again select this data and add a new chart or you could do a pivot table that would be the plan B but there are some new nice charts built for this so click wait I guess until this does what it's supposed to do so here I just want total sales for example right so I don't need all of this other stuff so I'm gonna remove them and I also don't want this chart type so let me start by actually changing that so I'm gonna go here scroll down until I find this thing that's called a scorecard see it gives us total so I'm gonna click on that it gives us this because that's going by the date so I'm gonna go ahead and replace that by doing sales column instead because we want total sales and we want to also aggregate this to get the total so that's the total of everything it gives us this thing sales rep I'm gonna remove that I'm gonna remove this I'm gonna remove this and I'm gonna remove that and as well as cost of goods there's a lot of columns here that we need to get rid of maybe I shouldn't have picked all of them but anyways so now we have this so I can resize this like this I can resize it like that you can go back and modify this and customize it make it look pretty for now let's just make sure it works so I'm gonna take this and cut ctrl X or command X go back to my dashboard and paste it right here on my dashboard put it in here scroll to the left here on top let's make sure that updates as we run our slicer see that's the same tone here 255,000 if I go back here and so like 2016 very nice it'll updates together there's some delay between different updates but it works so that's our total sales let's also do the total count for our sales so I'll go ahead and do pretty much the same thing all over again or maybe I'll just copy this one so I'm gonna do command C or control C and control V so we have a copy and then I'll add it the second chart and instead of summing the sales I'm gonna change the aggregate type to count but that's doing the dollar sign because it's getting the actual value from the data so for that reason instead of counting sales I'm gonna count some column that's not numeric type so really any column that's not blank should be fine I'm gonna go ahead and choose region for that you don't want any blank so it's not gonna count them so I'm gonna go here and do edit and replace this sales with region so that's not the right column I'm gonna go ahead and select their right column I'm gonna choose this one hit OK and again let's just make sure this all works together so if we're clear this do female see it just filters all of them and if we go back and just select all we're good now let's try to redesign some of the stuff to make it look nicer I guess so for example this it would be nice something that says this is sales so I'm gonna go ahead and edit this chart and go to customize we're gonna choose the style so what the background is but before that let's just go and do the titles here so the title I'm gonna call this total sales that's gonna appear here on top we can't change the styling of that and make it smaller or be it's really up to you to see what you like I'm gonna do some background color changes so the chart style I'll do dark background for that something like that maybe even darker and I'm gonna go under the key value and change the font to a light color so we can see the total in there similar things you can do obviously to this it's all the same so I'm gonna click on this edit chart click on customize go to titles we'll call this number of sales and again choose some sort of font size whatever works for you and some colors then I'll go under this and do the background color to be dark and the font color light again right you can also Center this in the middle if you wanted to you can also change styling for your slicers themselves so if you added the slicer you can go to customize and see this as year that's good you can change the font size you can also change the colors so if you want the background to be a different color you can do that I'm gonna leave these two alone I'm gonna make some changes to this one I'm gonna do at its slicer first I'm gonna change this to just say gender instead of saying customer gender I will also just Center that in the middle and just changed color for that something like that you can also as you can see play around with the text color if you wanted to to change the style for your pivot tables and charts overall you can also condor format and use this new theme on top so if I click on that see there are some existing ones you can play around with this and kind of see what you like better and yes so you can do these and see which one works for whatever you're trying to accomplish and if none of those are good enough for you you can also customize this so you can click customize on this and then change some of this colors and fonts and all of this if I don't want my charts to have this white background I can apply some different type of background for this I'm gonna switch that back to white that was good you can also change what colors your charts are using by the fold so see it goes the first color that's the main color here then you go like the 2nd 3rd 4th colors so you can just change this and by the way this main color will also apply to your pivots so if you went ahead and change this that shoots likely also change this color over here so see this one again that's the red that's the secondary color in this I guess so again just play around with it and see what colors you like it should be pretty easy to figure this out there it is so now we have a working dashboard with our slicers on it and some summary for our data hopefully that was helpful thanks for watching please subscribe and I'll see in the next video

Channel: Learn Google Spreadsheets

Views: 55,382

Rating: 4.9516907 out of 5

Keywords: Dashboard, Pivot Table, Slicer, Charts, Google Sheets

Id: SEzKYOiptCk

Length: 20min 41sec (1241 seconds)

Published: Mon Aug 26 2019