How to Create a Dashboard in Google Sheets (10 steps) - Query Formula

hey what's up guys this is michael from hustle sheets and today i want to show you guys how to build a dashboard inside of google sheets and the dashboard will look something similar to this over here and the data is going to come from a data source like this now this is for anyone who records any sort of a sales data or business data or job data inside of google sheets and your data is going to look something like this right but when you're looking at a spreadsheet like this you really have no idea what's going on with your data like you have no idea how much let's say you want to know how much sales you made in the month of october or you want to know the sales of each sales person by month or by totals you want to know the sales by job type you want to know the number of jobs by status things like that you can't really tell this information by looking at something like this right so that's the reason why we build the dashboard versus uh kind of just going in there every week or every month and kind of adding up the data i'm using a bunch of similar formulas that takes way too long so when you build a dashboard like this it basically uh auto updates automatically and then you just build it once and then you can always just look at this dashboard you can change the date ranges things like that and it'll just give you all the information you need in a nice visual format so i've tried to break break this down into 10 easy steps this is the most simple way i can think of doing it so i'm just going to kind of build it from scratch and show you guys each step how to do this okay now the dashboard i'm going to show you guys how to build is slightly it's a slightly more simplified version meaning that it probably won't have this little date range selector it's just going to show all of your data broken out by month or by week or something like that okay i'm going to make another video or maybe a course kind of showing you guys how to do more complicated things but this is a really good way to get started okay so if you go to the steps that i made the intro that was basically it the next thing you want to do is you want to choose your columns it's the first thing you want to do and this is probably the most important step of the dashboard is kind of how it's kind of how you want to structure your data right so let's say in this example i run a business where i serve clients and i each and i have jobs for for each client right so something you might want to record is a date you always want at least one date most people have multiple dates like a sales date and open data close date things like that so i see people do them kind of wrong let me start a new tab people kind of they they record data in a weird way maybe sometimes they'll do like their months um across the top like this and then they'll put in like their sales data and then each section might be like an agent right so let's say the agent's name is james something like this and then the next section might be for michael and then they might do the same thing something like this right so this is the wrong way of doing it and this is always the first thing i do with my clients is i fix their structure so what you really want is you want each row to be a job or a sale and things like that and then the columns across the top are going to describe that job so anything that describes that job goes into its own column so for example in this case we have the sale date the client name the client information is going to be over here the client email the sales person the person that that did the job the sale amount the commission amount the job type the status referral channel and then some notes usually right so this is basically how you want to do it however your business goes like the steps of each of the steps of the funnel i mean you know it starts with maybe like an appointment an appointment date you know appointment setter things like that and then it goes all the way to when the sale was closed right or maybe even a follow-up that's how you want to do it you want to order it by your columns in the way that makes the most sense so for this example i'm going to use all of these that i already had so you want to type in all of your columns across the top and the next thing you want to do is you want to format it a bit so i click here to highlight the entire entire row let me zoom in and then go to format go to text wrapping let's wrap it so that all the text shows basically and then let's bold it so once you have your column selected over here the next thing you want to do is data validation or drop down menus now the reason for drop down menus is so that when you enter in your data if you see here there's certain data data sets that are always going to be the same options over and over again right and then what you don't want is if you go to see i'm going to call this tab new data okay so if you kind of type in your data i mean like a sales person for example like michael heap someone might someone might type it like this someone might type it like this someone might type it like like kind of like this and the important thing with building a dashboard is that when you want to sum up like sales number by a sales person for example you have to make sure that the sales person is typing exactly the same otherwise it's not going to add up correctly right and the way that we fix this is by validating the data using drop down menus so let me show you guys how to do drop down menus okay i want you guys to create a tab we'll call it drop down menus drop down menus 2 because i already have one and then so which whichever column is going to have um the options for a drop down menu type it here so we'll call this one salesperson right and then add in all of your sales people however you want their names to be spelled so michael heap i'll do james rule let me see what else i had in this one so i had all of these right i'm just going to copy this and paste in all of my options like this so once you have that set up go back to your data data sheet and then go to the column wherever the the drop down menu will be right and then just highlight all the way down you can press command shift down on a mac to do that or control shift down on the pc right click it and go to data validation and then go to leave everything how it is click here select data range go to the drop down menu options and then highlight all of the sales people you can highlight more if you're going to add in more names later so maybe i'll do 20. you can go all the way down if you want and then just hit ok and hit save so when you go back to your data set what's going to happen is all of those names in the other tab are going to show up as options right and then you can kind of just start typing it and then things will show up now if you want to add in more names you can basically just add it to this thing so let me go add new name add to that column and when you go here it's going to be a selection for you as well okay so you're going to want to do this for all of your drop down menu options so let's see so state wouldn't always be the same so i'm going to just copy it from my previous one so i'm going to copy all of these options so you'll just want to type it in when you're doing it okay and then so like referral channel is an option job type status this right so once you have them all typed in just go to your data set and then let's go to like state same thing highlight all the way down right click data validation click here and then we're going to click on let me just highlight all of this and hit ok save the next thing was let's see referral channel or let's let's do job type data validation this job type is over here you can do as many options as you want basically hit save status so this will be kind of the status of the job that's usually a good column to have if you want to mark like where along this which stage of the process that job is in so let's go to status and then let's see what else is left referral channel okay and this will be if you keep track of where the job came from like which marketing channel or where it came or like how you guys got that job hit save okay so now that we have the drop down menu set um what what we want to do is we want to freeze the top row right so what this does is this when you scroll down it keeps the the header row at the very top so just highlight it go to freeze and then go to up to current row so this way when you highlight it you know what's going on and then at this point you also might want to reformat some of the the columns so everything shows okay so now that we have this kind of set up you also want to do data validation for something like a date right and let me show you guys how to do this it's the same thing you highlight everything down data validation but this time change the criteria to a date and hit save now what's going to happen is when you double click into this then it's going to have a date all in the same format like this right and then the client name will be the same let's see okay so at this point now what i recommend you doing is start typing in some fake data or if you have real data just paste it in right so i'm gonna borrow some of the data from my old sheet i'm gonna highlight some of this stuff then i'm going to paste it in to my new data set and when you're pasting in data it's important to remember to um just so right click it paste special and i pref i suggest you guys use paste values only and the reason for that is when you paste data in uh what happens is it takes the format from the previous from the data source right so if you have drop down menus it's going to overwrite the drop down menus and then just paste over it but if you paste the values only then you can just then it'll just keep the formatting of the current sheet you'll see here that this formatting isn't correct right so just highlight the column go to format number and then just select the date format that you want and now it's going to be like this and then you also want to fix some stuff like the sale amount and commission amount are dollar amounts so highlight this as well and then click on the dollar let's kind of make this column a bit wider and then same here okay so now that that part is set the next step is going to be the conditional formatting now what conditional formatting does is this is an optional step but for example if you have like a status or something that you want to update and then you want to change the color of the row based on that value this is how it's going to work so let's change this to open all open jobs will be will turn red so this way when you look at it you can easily see which jobs are open right so now it just turned red if jobs are paid then it should turn green so that you can just look at it and easily know that it's um it's it's green so the way that we do this is just click on format and click on conditional formatting and then i'm going to fill all this out and then i'll show you guys what it means okay so apply to range what you're going to do is highlight all of your data except for the header row so highlight all of this data and you want to go all the way down the direct to the very bottom or you can just kind of delete the last two numbers so it's a2 to all of o and then under format rules you're going to change this from is not empty to let me move my face real quick okay so you're going to change it from is not empty to custom formula is now wherever the the value column is that you want to change the color based on and type in equals it's so my status is column m right equals m two to m so all of m equals let's use um open as an example in quotation marks type in open or whatever your text is going to be and the important thing to remember is that you have to add dollar signs in front of each of the column labels right so in front of the m the t with the m if you go to like m 100 and the same thing but we're going to the very bottom and the reason for this is it locks the cell so it tells them that it's it's this column only right it's just something you have to do so you see now that it turns green you can change the color to whatever you want you can change it to blue or whatever they give you like you kind of some default options so green is a good one and then now instead of typing it all over again for the same rule basically but different words just type in add another rule and it's going to fill everything out for you and just change the name from open to paid and then if it's paid it's going to be let's say we'll make it red right and then same thing add another rule let's change it from paid to pending and then now that's yellow now you can hit done okay so now that that is done let me close this okay so now that that is done you basically have the the data source part set up right so how this means how you entering your data is pretty much set you can format some things like to make the center aligned to make it a bit more neat you can go through make this center lined maybe the zip as well and the phone number so it looks a little bit better this is up to you and then let's make this a bit smaller and then another cool thing you can do is kind of group some of this data so for example let's say you don't always want to see like address city state zip and phone number of the of the client right just highlight whatever rows you want to hide and then just click on group column so now this will just open and close on this button you can even like hide like something like the commission amount so group so it doesn't always show just so it makes it a bit more easier to look at and you don't look at all these extra columns that aren't always necessary but if you want to go back to it you can always pull it up okay so now we have our the way we enter in the data set and that's probably you know the most important part now we can kind of start building the dashboard right now this is using something called the query formula and if you don't know what the query formula is it's probably the most powerful formula inside of google sheets it combines like five different formulas into one simple formula for example you can do like a vlookup you can do some ifs you can create pivot tables all through this one formula right so if you go to my dashboard here's an example of what a complicated version of the formula is so it's a query formula and it looks a bit confusing at first and it was really hard for me to learn at first but trust me once you kind of get the hang of it it's really easy and then if i kind of give you guys these little shortcuts and how to fix mistakes you're going to learn it way faster than i did so the most important thing is the syntax you can do one two three four five six seven eight nine ten different things right but for the most part we only use like you always use the select um and the group by things like that um but it has to go in this order and i'll explain what i mean in a second okay so let's say the first thing we want to build because essentially what we're doing for a dashboard is we're building a bunch of little tables and then we're just going to add some charts to it right so let's look at our new sales data and let's say the first thing we want to find out is we want to we want to know that the sum of the sale amount for each sales person right the total amount so i want to know how much james how much james's sales are how much michael sales are and whoever else is in here i guess i'm that's the only one okay so let's create a new tab and we'll call this one our dashboard dashboard v2 okay so we can start the formula anywhere we can move this we can move this whenever we want let's just start it um here in b10 so what you'll do is you're typing equals query and then you just press tab or put a quotation marks i mean a parenthesis and then go to your new data here let me pull up the the helper so basically you're going to type in your data set first and then you're going to type in the query right so let's highlight all of our data so it's going to be from a2 all the way to all of o so let's just delete so a1 oh yeah so a1 to o right and then you'll type in a comma now again the first thing we'll do is a select which is always going to be the first thing always type in select and this means selecting whichever columns that we want and like i said before the columns you want are salesperson and sale amount right so we want the sales person and we want the sum of the sale amount so salesperson is column i so type in i and then a comma and then type in j right except now we do sum j so type it in just like that okay and capital i capital j this part can be lowercase or capital but just do it the way that i do it so select select select i comma sum j and then type in we're going to group this data the sum by i which is the sales person and i'll explain more in a second so type in group i and then quotation marks close parentheses and press enter um let me see where i did something wrong um select i some j group i was expecting oh sorry it has to be grouped by i see even i make little mistakes like this and normally that would take like forever to figure out but if you watch this video then you kind of see kind of some of the mistakes right so it has to be exactly typing like this so this is basically the language of the formula so type in group by i so you see here now we have the sales person and the sale amount right and the cool thing is if you go back to the data set if i add in a new name like john andrews and i type in you know whatever amount and i go back to the sales data the dashboard now john andrews is going to show up all right so it's going to update automatically so this is the beginning of a dashboard basically right because all you'd want to do now is you can just highlight all of this data wherever the data is going to go down to let's say you have 10 sales people like this and then you're going to insert a chart okay so i don't really like to use pie charts um so let's just do this and then we can go to customize and go to series and show data labels right okay so this is basically kind of the beginning of a dashboard if you look here let's just format this and just enter some roles up give it some more space so if you see here this is basically it so all we're going to do is recreate this a bunch of times for however you want to see the data right so if i go back to my original dashboard i did by referral channel i did it by salespersons okay so let's build each of these out if i go to dashboard v2 so what you can basically do let's say you want to see the sale amount right and then you also want to see the number of jobs or in this case the number of rows right so you want to see the number of rows and then you want to see the average sale amount so let's go back to the dashboard we can just modify i'm going to delete this chart real quick we can just modify this formula so select sum j we also want now the count j what this does is it's just like the count formula it's just going to count the number of times that let me see so now it's going to just count the number of times so for james rule so michael heap is five right all it's going to do is it's going to count the number of times that there is a value inside column j where my name is michael heat so it's basically a countif formula right so there's one two three four five persons of me there's one john andrews okay so now we have the count of the amount and then you always have to separate each one by the by a comma so now we want the let's say we want the average so just type in average j okay so you always have to separate it by um by a comma except until you move on to the next to the next clause this is called a clause so you see here now select where now we use select let me zoom in so we used uh select we used group by and then we used i think that is um that is it so select and then group by so that's basically it right now let me show you guys a couple ways to modify this formula to make it look a bit better so you see here it brings in an empty row up top right and the reason why it does that is because we have all of this empty all these empty rows and it's bringing that and it's also grouping that row in right so it's pulling in by sales person but it's also pulling in a blank sales person's name right so because it's all these empty rows so it's pulling in so there's zero counts for that there's no sale amount and there's no amount so how do we get rid of this row we're going to add a where clause now where is basically an if right and aware it comes right after select if you see here and i'm going to have kind of a cheat sheet for you guys to look at but basically if you want to put in a where it comes after the select so let's go and double click in this formula so here's the group by and here's the where now here's a little kind of like a little hack that i learned really late that will teach you guys i like to separate my clauses by line so it makes it easier to read so you can do this by going into the formula right before the word that you want to enter basically hold option and then press enter now it's going to move it down right and then the group by and then move it down now this makes it a bit easier to read it's up to you if you want to do this so now after my last select i'm going to hold option enter and i'll type in where i is not null let me explain you what this means okay so there's certain little language phrases you can use inside the query formula so if you want to let me tell you what this is saying it's saying select the the column i the sum of column j the count of j and the average of j which is a sales amount where i is not null grouped by i so we're grouping the data by now grouping by i basically means i mean um sorry swear i is not null basically means it's only select these columns if or where column i is not null and null in spreadsh it means empty so not empty so basically means pulling it wherever there's only data inside of column i so let's say we have 500 whatever number this is 50 million right it's not going to pull into this because there's nothing inside of column i but if i add just one any text to it actually it doesn't have to be anything then now it's going to pull it in right so that's basically what that means and i wanted to say something else group i okay and then the group by can be a little confusing when you're first learning this formula but just know that you always whatever you put in the select column that's not a aggregation an aggregation is like a sum account an average right those are that's called an aggregation but if you're just pulling anything that's not an aggregation but is in select has to go in group by because you can actually pull in multiple columns so if i go to new data let's say i want to pull in the sales person and then i want to also break it out by status right by m column m you can actually go like this and you want to put in all of your aggregations at the end and all of like the normal columns at the beginning so it's going to say value unable to unable to do this because and it's going to tell you why it needs to add column to group by or aggregate m so it's basically saying you need to add this column m to either the group by or aggregate it right now i wish i had someone tell me this in the beginning because it would take me hours to figure this out so what it's telling you is you can either do it aggregate it by doing a count so now it's just counting the number of statuses which is going to be the same as as this if you try and sum this you can't sum it because um it's not a numeric number right because the status is a word so what we really want though is none of these and what we want to do is we want to group it by by this right so group by i comma m you separate everything by comma except for the last one remember i type it in exactly like this so now it's going to pull it by salesperson and then by each status of each salesperson so now it's going to show okay james all of his open jobs are this much james all of his paid jobs are this much you see that now it basically separates it out right so that's just to show you that you can group it by whatever you want but just to keep it simple we're going to group it just by to delete it if you delete it from group by you can delete it from the select okay so this is basically how we have it okay now let's make this a little bit more neat and the way we do that is so you see here here's some issues right so the first thing obviously you want to do is you want to format some of these numbers correctly right so let's kind of do that but this will change as you move things around the spreadsheet but just to show you guys so it looks neat for once now let's say we want to change the names of these headers right we don't want to say some sale amount or count sale amount you can't just go in here and just type in sale amount and then it's going to cause an error because now it's going to say reference array result was not expanded because it could it would overwrite data in c15 now this means that it's telling you what it means it says that you this formula is not working because it's there's a text inside c15 that needs to be deleted so once you delete that so you can't type in any of these formulas in any of these cells right but the query formula gives you a way to to label it basically and then labeling so labeling comes almost at the very end basically after limit and order by and i'll show you what each of these mean right but just so you know we've only used select where group and that's it so obviously limit i mean label has to come after that so to make it easy i'm just gonna before the quotations in between the i i'm going to press option enter and then type in label now you can label anything that's in the select column so i'm just going to copy all of this actually i'll make it more simple for you guys to understand so let's say you want to change column i to a different word to a different header name right so just type in i in capitals and then in single apostrophes now you can type in just sales agent and then close it it's going to change the name of the sales agent now again you can also if it's if it's not an aggregation you can basically just change it inside the the sales data which is probably easier so you probably wouldn't really do this but it really only matters for the aggregation amounts right so the same thing as before after each after each inside each clause you separate it by a comma right so comma now the next thing we want to change is sum j so type in sum j exactly like this and let's change this instead of saying some sale amount we'll just call this sale amount or revenue whatever you want to call it right in single quotation marks and then let's change let's add a comma and then count j will now be jobs the number of jobs and then comma and the last one is average j will be um average sale like this so there you go and then it probably looks best to kind of right align this something like this right we can center it i usually like to write align numbers it just looks better but for small numbers like this maybe you want to center align it but normally i do the formatting at the very end so i'll get back to the very end right okay so this is basically a pretty good query right um let's see a couple things to note i didn't want to get too advanced into this but screw it so another thing that you might want to add is a limit right especially when you're dealing with something like um like a dashboard where there's limited amount of rows so limit comes after order by and before label so trust me it gets really easy to kind of figure to understand this so after group by let's add in see label can be written as uppercase or lowercase okay so now i'm going to have another separator and i'll type in limit 10. now what this does is this limits the number of rows that it's going to pull in so it's going to just pull in you know by alphabetical order or something like that right at first it's going to pull in um yeah by alphabetical order of the first select by default and what limit does is it's because if you go back to my original dashboard let's say you have like 20 like you probably don't but let's say you have 50 statuses right it's going to keep on pulling it down and then there's not going to be enough room and truthfully on a dashboard you just want to show the main information sources right you want to show every piece of data if it's if there's too much right because no one wants to look at the bottom one percent usually so what you'll do is you'll type in the limit and if i go to my dashboard for example if i change this limit to just one it's only going to pull in the first one right whoops if i change it to two it's going to pull in two rows and let's say you want to pull in just the top sales amount right so what you'll want to do is you want to do a order by order by means sort basically now this one comes after group by before limit order by and then let me show you guys how to do this um so after group type in order buy and now this depends what metric you want to order by usually one of my clients i do it by the revenue amount that's the most important thing so i'm going to type in the revenue amount is sum j so ordered by sum j and then you can type in here asc for ascending or desc it's going to automatically if you don't do anything it's just going to do it by ascending amount right so now what this does is it pulls in the the top two lowest ones sorry um all right sorry guys okay so it basically pulls in the top two by um by uh ascending amount right but let's do it by descending because that's usually how we want it so now it'll pull in the top two top sales amounts um let's change this to limit 10. so let's say we're gonna give it two 3 4 5 6 7 8 9 10. so we know at most the data is going to come down here so this way we can kind of format our dashboard in the future to look something like this because we know it's not going to come past this certain point right and i'll show you guys in a more advanced video or i'm still thinking about maybe doing like a query course where i get more depth with it um i'll show you guys how to create dynamic things that you can like add in a button we can change it to sorting by ascending descending and change the limit amount based on the drop down menu so let me know if you guys want that in the comments okay so okay now we have a pretty good query formula now it looks complicated but when you break it down it's super simple right now the cool thing about this is so this one's by sales agent what you can do instead of typing this all over again just literally go in here and copy everything and then let's say we want the referral channel now so we're going to paste it in here and go back to your sales data and the referral channel is by column n right so go to dashboard so now all i got to do is change the i to an n everything where you see an i just change it to an n now you have to do everything because if you forget just one thing watch it's going to have an error and i always have to go back and craft and it'll tell you label column not in select so it's saying there's a label i but there's nothing in select that's an i so you have to change the n and then right and then it says sales agent but you can make sure to change it to the right thing but typically i don't even have a label of a non-aggregation amount because it's just going to be the column header so referral channel okay so you can basically do this for everything and then let's go back to the new data you can do this for status for example right so let's go to m just move it over and go to change this to m root by m whoops okay there you go so now it's by now it's my um open status amount okay so yeah so look how simple that was right once you build it once it's really easy to just manipulate one formula you can change the limits on some however you want to do it right okay so now let's see if i go to dashboard steps finish this part now let's go over chart basics okay so i kind of want to kind of start doing some formatting as i do it i know i put it at step 10 but it kind of bothers me so i'm just going to kind of do it now so on the dashboard side the first thing you want to do is probably want to remove grid lines it just makes it look a bit neater i like to change the font to something smaller like a condensed font like a roboto condensed so it looks a bit smaller right okay now we want to add charts in so let's highlight okay so let's say we have a limit of 10 right so we know where this is going to end it's going to go 1 2 2 3 4 5 6 7 8 9 10. so this is basically the end let's just put a little marker here this is the end of where it's going to go so i can basically highlight this and then i can go to insert and a chart and then you can basically choose whatever chart you want so you can go to customize chart style um let's see well let's go to setup you can change the type of chart that you want right so this one is like this column chart you can do it to this bar chart whatever you want probably for something like this i would do a this type of a chart um yeah let's see because there might be up to 10 people right so you need to think about it if there's 10 people how is it going to look um and then you can move let's go to customize you can move the series so let's say you want to move the let's see the number of jobs right so the sale amount average sale might be pretty similar but then the jobs is going to be a much lower number the number of jobs you can change this to the right access like this so it kind of makes a little bit more visual sense okay so this is basically how a chart would look and you're going to want to do this for everything right depending on the chart that you want um okay and let's say so same thing this thing is going to go down 10 right up to 25 based on the limit again you can go all the way down it depends on how you want to format this right and then you can go in here and add a chart this way as well now let's say what we want is like a pie chart i know a lot of people like pie charts i don't but whatever let's see this pie chart for example this is going to show you know by referral channel the sales amounts or you can change this click on here it's going to change it to the jobs all right this is just by sheer number of jobs you can go to customize and then you can go to pi slices or pie chart and you can kind of show the label so let's do the label oops let's do the value or percentage since pie chart should be a percentage okay so something like this okay and then you can even you can double click into it and then go to customize and then um you can even change the title so let's so let's go uh jobs by referral referral channel and press enter something like this right now you can see the dashboard is kind of coming along quite nicely so you're basically going to do this for everything right and i'm not going to kind of go over it because it'll take too long um but the next step that you'd want to do is um okay so this is the kind of the last thing of the query formula that i want to show you guys and this is a really big hack and it's super helpful and it's something that really helped me and once i figured this out i could build so many different types of dashboards okay so you see here in my original data set i have something called a sale year and a sale month column right and what this does is it labels the month that the job was in because most of the time people want to see their their data grouped into certain time sets like month like week things like that and it's kind of difficult to do inside the query formula like i know there's a way of doing it but it's quite complicated sometimes adding a helper column is is much easier so this one's called helper column so let's go to new data so whenever the data is going to end you can add in helper columns right so let's call this one month and then i'll probably highlight it gray so people know that it's a formula not to edit or delete it the helper columns are tricky because you don't want to accidentally delete them if you add in new columns one more thing to note is if you add in new columns it's going to kind of mess up this entire dashboard so make sure you have the columns set as much as you can what you can even do what i suggest you probably even do is add the month column to the very beginning to the very beginning of it and then you can just hide it but you want to make sure that you don't accidentally delete it as you remove the data and i'll show you guys how to make sure that you don't do that okay so let's go to equals date and then what we're going to do is we're going to type in the year so type in the year formula of the set of the date which is going to be b2 comma now the month b2 and then just type in a 1 and i'll show you guys why and then i'm going to drag this formula down i guess i should be more obvious this is called sale month because you can have multiple dates right so it depends what date that you want to group you want to see the data in so now it's going to basically bring it to the first of each month right let me change this to a different month so it kind of shows a bit better so whatever date it is it's going to put it to the first now it's probably better to do it to the very last of the month but there's a formula to do that i think it's um eo month be the last day of a month before after a date okay so this is probably better i maybe i'll do a follow-up video to show you guys how to do it but basically if you know how to do it you want to do it to the last of the month so i did i did the first but i learned later on that it's probably better to do the end because when you're selecting a date range it'll show but that's probably a little bit too advanced this is good enough and then you're going to change the format here format number and change it to this one just month in the year so now it's just going to show the month in the year right and then i'm going to change this one to march just so i have more examples okay so this is a hack to how to basically group your data by by month now when you go back to the dashboard everything is going to be messed up because i added in a separate column right so this is what i mean by you want to set the columns incorrectly so everything basically moved over one so now i is now j j is now k whatever right so i don't want to go back and do all this again so i'm just going to move this back over to the front so that's important to note that you can't like add in new columns after you set otherwise you could change everything which is kind of a pain but it's just part of it so now that i've moved it back all the columns are correct again now the month one i'm going to add in some columns to the left now for the month one same thing all right i'm just going to paste this in now there's a couple of things you want to note okay so this data set only goes from a i let me zoom in it only goes from a i to o right so if i go to new data i mean a1 to o but then i added a new column so it's p so go back to your dashboard and change this to p we can even just do z and just go all the way to the very end so it captures it right and then we're going to change this one instead of selecting i we're going to select it by where is it by p so go here we're going to change the i to the p where p is not null group by p i'm going to delete this label sales agent hit enter so you see here now you basically have the data grouped in by month and for this you're probably not going to want to limit it unless you want to limit it by 12 or 15 just because there's 12 months in a year depending how you want to do it sometimes i don't even have a limit it just depends how much space you have three four you can always just if you don't want to you can just delete this or just change like 100 or something really high so two let's see so i'm gonna just highlight all of this 12 12 down see first row second third fourth fifth sixth seven eight 9 10 11 12 and then i'm going to insert a chart insert chart now for this you probably want a time series which is basically i guess all they have is a line chart so do a line chart and then um let's see the cell month remove it because it should be the x axis like this and then you can kind of customize this again so let's change the series to the job should go on the right axis so it's a different axis okay so i wonder why it looks so weird like this okay let me move this up so you can see the options okay use row 15 as headers it's not checked so let's do that use column a as labels yes that's correct i wonder why it has this weird thing oh aggregate maybe okay yeah aggregate wait aggregate 25 let's see no we don't want to aggregate this okay let me just look at this and see all right so weird february 2020 yeah so a lot of the times this stuff comes up when you're trying to deal with charts and stuff it gets a little confusing so oh maybe because it's not in order so okay so one thing i need to change real quick is this is order by some j descending so this is ordering by the revenue right and we all we obviously want to order it by the date so we want to order it by p so change it from just delete that and just put p so now it's order by p right or you can go order by p descending if you want the last month first it's up to you um but probably you want the first month first let's just go down here and just try to add this chart again insert chart so this looks correct right and then let's change it to a line why does it show february twice for line chart okay i need to figure this out but for now let's just do a column chart for some reason the line chart's messing up customize go to series um go to jobs i'll do a follow-up video if i find the error for you guys and then let's change this one to the right axis so it looks like this okay so now this makes sense right call this sales by month something like this i can delete this because it's pretty obvious okay so this is basically correct now move myself down again okay so you'll see here we kind of have basically the dashboard that i showed you guys except that you know the formatting is not as pretty right let me change this and then let me change this okay cool and let's go look at this dashboard okay so let's see helper columns is done scorecards and sparklines so a scorecard is essentially this right just kind of like some main metrics that you want to see so if i go back to the dashboard that i'm building dashboard v2 this one's pretty easy it's basically just like a sum of things let me see how i how i built it okay so for this one i didn't really have a chart which is fine so let me move this you can basically just drag this or move the formula up we'll insert another column so it looks a bit neater and then basically a scorecard is basically just like a sum of like certain metrics the main metrics except we want to do a sum 2 3 4 5 6 7 8 9 10 11 12. 12 because we set the limit to 12 and then you can kind of just drag this formula out and we'll call this totals so that's basically a score card the format's not as pretty but we'll fix that later let's right align all of this so it looks a bit neat okay so and then the next thing now is a sparkline so a sparkline is pretty helpful it looks like this okay so it's basically like a mini little chart inside of a inside of a cell so let's go to dashboard v2 we just do it here we'll call this one sale amount again so equals type in sparkline and then the data that we want is going to be just these two all the way down to the very bottom or sorry it's just going to be i actually have a video on sparklines itself so just make sure to make sure to check that out too i'll link it in the description below so eagle sparkline and then we want the sale amount here and then this is basically the syntax right it just gives it to you so you can just copy this if you want paste it in so the chart type you want is a bar and then the max is 50 right so let me show you what this means um this means that okay the sparkline we're going to take this data set we're going to create the chart type it's going to be called the bar chart the max is going to be 50 which means that the amount here is 27 million right and then the bar chart's going to do it in perspective to 50 being the max so that doesn't make sense because 27 is way more than 50 but let's change it to 50 million now this is just half of 50 million but what we really want is half of we what we want is to be the max the max number for it to scale is the highest number of these 12 months right so one two three four five six seven eight nine ten eleven twelve i need to put a marker so i know so how we do that is we change the max instead to um max of this all the way here oops and it says the error here is um it's an array value right so let's see how i did that last time okay so i did the max of e9 to e so it's the max of this is the fun of spreadsheets right it's kind of figuring all this stuff out but i'm here to help you guys so that you guys can save a ton of time so the max is going to be the max oh i see what i did that was smart of me okay so the max is going to be the max of i have to just put in the max okay and then let's lock it by saying c7 so what this means is it's the max the max formula basically pulls the max number of a certain range so the highest amount is going to be the highest amount on here so it can change its dynamic right so if i say so if i say um so if i say like if i put in 100 million one two three one two three here so now this is in perspective to the highest one right okay so that is pretty helpful um now you can kind of just drag this all the way down to i think it was 18. right and then these numbers are way too low so it's not even going to register for example but and to get rid of these n a things you want to add in if error f error is a very important formula so in the very beginning of the formula let's wrap this and then if error this is the value and then if there is an error then just leave it blank put your quotation marks now we can drag this down now it looks like this okay so that was the sparkline and then now let's get to formatting so formatting is kind of the fun part you can just decide how you want to do it so this is basically how i format mine right so everything is going to have a very light gray background let's see let me move this over so it's easier for me to compare the two and then the scorecard is just going to be all black let me just make this all black and let's make the text white let's bold it and let's make the text a lot bigger or let's make it a little bit smaller something like this and then it's going to be centered and you also always want to wrap the text of the headers if it needs to be wrapped it doesn't in this case so let's pull this and then let's change this background to also be whoops to be black and the text to be white and then so 2 3 4 5 6 7 8 9 10 11 12. this can be a white background so it looks like this um and then okay so here let's say you want to move this forward you can literally just take this highlight all the data and just drag it up like this the formatting will stay which is fine uh so there's a limit of like 10 right so you probably want to drag this down a bit let's change this to white this will be black white like this so something like this you can play around with the format okay so i'm not going to go through and kind of do everything but you basically get the idea of how to build this dashboard okay so if you guys want to learn more make sure to check out my query course i'm going to be putting out it's going to be a more advanced dashboard course where you'll learn how to do things like create these drop down menus that'll automatically change all the data how to add dynamic sorting options things like that i'll kind of go more in depth and i'll go more in depth on the query formula because there's a ton you can do and it's a really great formula to learn i will also leave let's see where is my new data my dashboard sheet okay so i'm going to leave kind of this as a sample for you guys um in the link to the description you just have to go in you have to add in your email address to go form then i'll give you a copy you can make a copy of this right but if you guys want this copy unfortunately i don't give this copy out like it took me a ton of time and actually sell this copy so if you guys want you guys can buy this copy as well i'll have a link to how to purchase like my original copy and i'll give you guys um and what comes with it is kind of a description on how to edit it so you can basically just have this ready-made thing and i'll give you guys kind of examples if you guys want to add certain columns what you guys can do how you guys can change column names a lot of it you can basically get from this video but if you guys just want something a bit more structured and then also kind of gives you guys access to be able to message me and i can help you guys set certain things up so i'll leave a link for that below as well but this is basically yeah how you create a dashboard inside google sheets right this is a really great way to get started so we created this in i don't know how long maybe like 40 minutes or so the key thing is just kind of mastering the query formula which is a really great thing to learn but besides that you can see that it's it's honestly pretty easy i use like three formulas to build this entire thing if you guys want to see more videos like this make sure to comment any type of topics or any questions that you guys have i'll do my best to answer them besides that make sure to like and subscribe thanks a lot guys

Channel: Hustlesheets

Views: 311,256

Rating: 4.954814 out of 5

Keywords: google sheets, dashboard, excel, spreadsheet, google spreadsheet, google sheets dashboard, sales dashboard, query formula, google sheets query formula

Id: 1JkvHfzIL0Y

Length: 56min 27sec (3387 seconds)

Published: Sun Nov 01 2020