Google Sheets - Dashboard Tutorial - Part 1

in this video I'm going to create a dashboard if you were serious about creating dashboards I suggest you look into Google Data studio for really cool-looking dashboard and really something that's more robust but if you really stuck with Google sheets then I'm going to show you what tricks you could use to create some dashboards in Google sheets now again if you want to look into Google data studio I have videos covering Google Data studio in this video I'll show dashboards in Google sheets ok so before we start I want to show a little demonstration about what we're going to build so you can see the end result and then we can see how we get there so this is the data we have our transactions I want to make a dashboard so I'm going to this dashboard tab so you can see what the dashboard looks like so we can see we have our total sales we have a map that shows our sales by States we have a breakdown by regions we have our year-to-year sales we have our performance of different salespeople and the comparison to the average line and so on so it's not the prettiest dashboard but it's not gonna be about design it's gonna be about functionality so you should be able to take the core principles we take from here and just go from here to build a very nice dashboard if you want to do that the coolest thing about this dashboard is not really having all these items here is that you have one filter that controls everything on this particular sheet so if I go here and I switch from all regions to let's say Midwestern you will see how everything on this update we have the total sales just Midwestern we have our year-to-year report we have the map updating to just Midwestern region so we basically just updating every single report on this to this filter now if we wanted to change the start date to a different start date and we want the different date range you can do that so if I want my report to go from you know half of 2016 let's say I want to go from June 1st 2016 I can go here and click and it's gonna filter everything in my report to that particular date range and I can also choose like an end date so if I want to go all the way up to you know in 2017 I'll select June 30th everything will update so everything updates based on one filter we have on top and if I do oh there we go that's no region filter but it's still filtering by date so that's what we're going to build in this video to be able to follow this video and understand what I'm doing you'll need to know a set of functions you need to know query function you need to understand join text join functions you need to understand generally how to concatenate string using and some other things you'll need our pivot tables you'll need some charts if function possibly if instead of if and overall that's gonna be the core you know functionality set we'll need to build a dashboard all of this different things I cover in separate videos so if there is one of these you don't know I suggest you go and watch videos covering those functions and then come back and watch this dashboard video after that I'm gonna go ahead and delete that so let's go ahead and get started the first thing I'm going to do I'm going to create a new tab and create a temporary data set tab so this is gonna be I'm gonna call it temp data set so this temporary data set is going to be coming from this transactions data this data is what I'm going to use to create older reports on my dashboard so the first thing I'm going to do before I do anything in my dashboard I'm simply going to go to this temp data set tab and create a query function and use the data on this tab so I'll go in the corner control shift right down to highlight my data I will remove the reference to the end row so that new rows are automatically added to our results I'm going to do comma and the query that it's gonna be very simple I'm simply going to do select star which means select all columns and comma and in this particular data set we have one row as headers so one close parentheses hit enter as of right now this should just take everything from transactions tab and pull it to this tab so basically we're creating a dynamic copy of transactions as of now and all the reports I'm going to do on my dashboard are going to be based on this tab now I'm gonna create another tab and that's gonna be by dashboard in my dashboard I'm going to first do a little setup so I'll do start date this will be some user inputs if necessary and date something like that uses two cells for users to enter the start and end date and for those I'll select those and I'll do some data validation for that I want to make sure that whatever people entered is actually a valid date and if it's not I will reject the input to those cells make it a little pretty I guess to some borders make it bold change the color again this is not going to be design-oriented so if you want to go into like creating a beautiful dashboard you should probably spend time and do you know the design part on your on mostly I'm going to concentrate on functional things so functionalities that you'll need to actually be able to build a dashboard so the first thing I'm going to do I want to get my total sales on my dashboard as a first thing over here for that I'm going to create a big sale I want it to be large so I'm going to just highlight this four cells together I'll go on their format merge cells merge all that will convert that to one large cell also merge this two cells together so format merge cells merge all so now I have one large cell here I'm on large cell here so in this cell I want to display the total so I'll do equal sum and I'll go to my temp data set I'll select this column of sales close my parentheses hit enter that's total now I can make that a lot bigger than that I can do currency formatting maybe go under format and do some alignment put it in the middle vertically and also put it in the middle horizontally make it bold change the background to I don't know something dark and change the foreground maybe something light and here I'm just going to type sales again probably should be much larger text formatted and align it in the middle and put this in the middle something like that and I'll do some borders here we go that's our total sales so that's very easy hopefully so the next thing I'm going to do is create some reports out of this data set and the first report I'm going to make is going to be our total sales broken down by brand I'm going to create a pivot table for that I'll click in this data go under data and pivot table you see how it made a new tab for our pivot table that's how pivot tables work if you know pivot tables I'm going to choose values and it's gonna be my sales and I'm going to choose rows which is gonna be my brand now the thing is that I want this on my dashboard so I'm going to simply just highlight this pivot table copy it go back to my dashboard and I'm gonna click here and paste so that will place that pivot table on this tab now I don't need this here so I'm gonna delete this tab so now that's my pivot table on this tab now another thing I want I don't want all this lines on my dashboard so I'm gonna go under view and remove gridlines so it will look cleaner like this so instead of calling this sum of sales maybe we'll call this sales to make it look a little better other than that that's fine so that's our first report on this the next report we want we may want to do our total sales by state so we have state we have our sales right now we want to do our total sales by state okay so I'm going to make a pivot now that's gonna be our total sales by state so I'll go ahead and click on this temp data set data pivot table and then I'll go on their values and choose sales and then on the rows I'm going to choose state so that will be our total sales by state now before I move any forward I want to change something about both of the pivot tables that I've made so far and what that is is that this pivot tables refer to a particular range and I want to make sure that if we're adding more records it's changing and adding those to our totals so I'm gonna go here in this temp dataset where it says the editor and instead of referring from a1 through h5 0:49 I'm going to remove references for the rows so I'll do a through H which will basically just select the whole column and the same thing I'm going to do for this one go here remove the reference for one and remove the reference for that number now what that's going to do it's going to add this new blank to our pivot table because it's selecting the whole column here and below all the values they are going to be some blanks so because of that now we have blanks as one of the options so we need to go ahead and remove those blanks so I'm going to go under filters and what I want to do is filter this by state which is this column that I already have and I'm going to go ahead and remove blanks so that's gone and for the other pivot table that's already in our dashboard we should repeat the same now because I'm breaking it down by brand I'm gonna go ahead and choose brand under filters and all items remove blanks hit OK here we go so with the care of that problem now it should be dynamically updating as we add more rows to our table and really we're not going to be adding rows here we're going to be adding rows here in transactions but that will reflect in our temporary data set table so this one will that pivot by by state pivot sales by state and the reason I'm not moving this through that tab is because I don't really want this particular thing on my dashboard what I want is a chart out of this so I'm going to click on this and build a chart so I'll go ahead and hit insert I'll go ahead and do chart and there this it made the chart out of it now instead of it using a column chart I'm going to move this to geo chart and here I'm going to change some things the first thing I'm going to change I'm going to go under customize and under geo I'm going to change this from the world to the United States so now it shows the map looking good maybe make it a little smaller now what I'm going to do at this point I'm going to copy that chart go to my dashboard and paste it and here I'll figure out where I'm going to place that I'll probably scroll this down and here you'll have some sort of label total sales by state and change the size of this that's not the point here okay good enough so that's our State thing now moving to our next report so this one we don't need this chart here so I'm going to delete it we do need the pivot table so we're gonna keep that but I don't want to have this tab on the way so I'm gonna right click on this tab and I'm gonna hide sheet so now it's hidden and at some point we're probably gonna hide this temp data set tab too because we're not going to want to see that as well all right so what I'm going to do I'm going to create another report which is going to be my sales people's report and their performance so I'll go to this tab to create that report I'm going to build a pivot table first so I'll go on their data pivot table brings us to this tab values it's gonna be sales rose is going to be our sales reps we want to make sure that any blanks are gone after we do this so I'm going to scroll down and under filters I'm going to choose sales rep and uncheck blanks the next thing we want to do is make some average line so we can see what's the average and to do that I have to do some calculated fields so I'm gonna do the average of our salespeople sales for that so what I'm gonna do I'm gonna do equals sum I'm gonna go to my temp data set so I'm gonna go from here and shift down a little bit I'm gonna remove the reference to the row so basically for starting from g2 and all the way down close that hit enter and that's total sales which is this number over here now we need to divide that by the number of sales reps we have to get the average so we take that and divide it by count unique open parentheses I go to my temp data set and I'm gonna choose from the first sales rep or here shift down again and do the same thing remove the reference close and hit enter okay so apparently twenty four thousand nine hundred and fifty four let's cross-check that just on the side so I'm gonna do the average of this oops all of those make sure we're getting the same number twenty four nine five four seven two seven eight so that's just formatting for formatted as a currency same number so we're good so the reason we can't do this is because when we do a calculated field we can't use the cells in our pivot to do that so we're using the original data set again I go into more detail at this in my combo charts videos I'll go ahead and cut that formula that gives me the average go back here and create another calculated field and instead of Sun it's gonna be custom and we'll paste the formula in there and this will be average so we'll type that and we'll change that to currency alright so now we have the line of average and now I can make a chart out of this with the line of average so I'll click here insert chart and we don't want the histogram combo chart that's good so I'm gonna add the line of average now so it needs to be starting from here going down to the end and there it is that's my line of average so I'm gonna move this legend click on the legend from bright will put it maybe on top and maybe change the line of average color to something different so that's our average line and we can see who is performing better or worse than the average so I can move this to see everybody's names and then we can just do ok so that's that there we have it so this sum of sales title it's redundant I'll remove that out of there so we don't have to look at it and probably before I copy maybe also rename this pivot sales by sales rep great so now I'm gonna click on a chart copied go back to my dashboard and paste perfect I'll keep it this size maybe we can't even make it bigger to match this to again you'll figure out the design on your own so this one I don't need delete that and we want to be able to hide this

Channel: Learn Google Spreadsheets

Views: 312,551

Rating: 4.9231057 out of 5

Keywords: Google Sheets, Dashboard, Tutorial

Id: 6vbferZJNJY

Length: 20min 48sec (1248 seconds)

Published: Sun Mar 18 2018