How to Build a Professional Dashboard in Google Sheets (Query Formula Advanced Course) Step-by-Step

cool and today i want to show you guys how to build a sales dashboard just like this inside of google sheets now i've built a dashboard tutorial video in the past but i didn't really go over some of the advanced features such as how to build a date range filter status filters some week over week change these are the things i want to cover in this tutorial and if you guys want to purchase this template it's going to be available for sale but if you guys just want to build it with me you guys can just follow along with this tutorial so i want to go over some of the features of this dashboard first so this is a sales dashboard for paper company dunder mifflin here and it's all powered by this tab here right so every time you make a sale you basically enter some of this data in here and it's going to automatically feed into a visual dashboard that will help you basically look at everything at a glance and know exactly what's going on with your business now at the top here we have a call out section with some some scorecards and it has kind of like our main metrics so here it has the number of sales the average sale and the total sales here the thing is depending on what date range you select up here this entire dashboard will update right so let's say i select april of 2021 what it's going to show you here is the latest month that you select so in this case it's april of the year 2021 it's going to show you the number of sales the average sale in the total sales and then it's going to compare it against the month prior to that which is march 2021 and it's going to show you the percent increase or decrease over that period and if it's red it'll be a decrease if it's green it'll be an increase so let's try selecting a different month like march for example so march had an increase over february of 19 in the number of sales 12 average sales and 34 in total sales down here there is a time series chart that basically shows you how the total uh sales and the number of sales is trending over time and again this everything in this page has to do with this date range here okay so from january 2020 to march 2021 you'll see how everything is trending down here is a breakdown of the same three metrics over the same date range we've added some color scaling formatting so the darkest blue will be the highest number and the lightest blue will be the lowest number this way you can just quickly look at it and see at a glance which months or which metrics to focus on and then here we have a sparkline chart that kind of shows you just visually which month has the highest number of total sales and then at the very top here we have a just a total of all three metrics over this entire date range now in this section here these four charts are basically all the same charts what this is is a [Music] the same three metrics but by sales person so you can see which sales person is doing the best with total sales you can see a pie chart here to see for example dwight shoot has 24 of the sales jim halpert has 17 and so on and then this one is by product type this one is by the referral channel so depending on which lead source that the sale came from you can see where most of your sales are coming from so here you see 24 is coming from a client referral and then this one is the current status of each uh sale again this is done on this tab here depending on which one you select for status right so in transit and it's going to automatically update on this chart now the really cool thing about this dashboard is that let's say you only want to look at one sales agent's data you can click here so let's say you want to look at dwight schrute select dwight shoot and the entire dashboard is going to update and only show dwight's sales if you want to look at dwight and then you want to look at only this the sales that have been delivered just click under status delivered and it's going to update and show you only his deliberate numbers so and you can just delete whatever you want and it's going to automatically update everything and then what we have here is a weekly dashboard now this is the exact same dashboard except that it's grouped by week this can be helpful if your company does things by week or just if you want to kind of track things on a weekly basis as well as well as a monthly basis right so you can basically see all of your numbers broken up by the week so in this case the week goes from a sunday sunday to a saturday so it ends this is the the the week ending date and you can just select the date range that you want to cover here so let's say we want the last week to be april 24th which is a saturday everything is going to update automatically okay now for how to build this so like i said everything is started with this sales data tab right and that's kind of the first thing that you want to start with is how to enter in your data now i covered this in great detail in my last video so i'm going to kind of just go over the basics on this so what you basically want to do is you want to have each row be a sales record right and then every column is going to basically describe the attributes of that particular sale as in which sales person it is what the sale amount is what the commission percentage is the commission amount the product type that they're selling the status and the lead source and then some notes these are calculated columns i'll go over throughout the video so starting with the first step you want to create a sales data tab or it's like a data entry tab so i'll call this sales data v2 and then you're going to select whatever columns you want to add onto your dashboard whatever you want to track throughout your sale right and there's always got to be at least one date a lot of times there's multiple dates like a open date and then a close date things like that but for this particular dashboard we're just going to have the date of the sale so the date the sale was made i'm going to copy all of these columns over just all of the black ones and then i'm going to paste just the values over just like this so and then i'll wrap it and then i'm going to freeze it so that i can scroll and keep it here and then i'll also bolt this now i'm just going to paste in all of this fake data in so that i don't have to do it all over again okay just so we have some data to play with so i'm going to copy everything whoops sales data and then i'm going to paste everything going to change this format to the right date format okay so now i have a bunch of fake data right but there's kind of like the sales person it should be a drop down the sale amount should be a dollar amount so let me show you guys how to do this so let me click here and i'm going to change this to a dollar what you also want to do now is you want to create a drop down menus tab the reason for that is because you want to call this one drop downs v2 because you want to make sure that whatever you want to track your your dashboard by so like all the statuses all the referral channels sales person product type you want to make sure that everything is entered in the exact same way because if you just have people type it in without a drop down menu they might type it in differently each time so they might say dwight schrute for one they might just say dwight or dwight s using drop down menus we make sure that everyone enters in this this important data the exact same way each time so if you look at my drop down menu here okay so this doesn't have to be here you'll see that um let's see you know there's a sales person lead source product type in a status right this is everything that we want to have drop down menus so let's start with the sales person so if i go to drop downs sales person and this is where you type in all of your sales agents right and every time you add a new one in just add it just keep adding it to this column and it's going to automatically show up over here and i'll show you guys how to connect that in a second so what i'm going to do is i'm going to take my old sales agents from the old drop down menu so i have all of these sales agents and then i'm going to paste it here okay so and then you're going to go to wherever you're going to enter in your data and let's highlight this entire column of sales people all the way down to the very bottom and then right click hit data validation and then go into your range and then just wherever you have that list of sales agents just highlight all the way down we want to go to the very bottom so a2 all the way to a okay so this way if you add in any new agents it's going to automatically show up so for example let's say i type in i start typing in dwight it'll show up if i just do dwight s there's going to be a red flag that shows that it's an error let me zoom in a bit and then if i were to add in anything new let's say i were to add in michael h here if i now it's going to show up at the bottom just like this okay so you want to do this with all of your important columns that you want to track by so let me just bolt the first ones and the second one i did was i'm just going to copy this entire thing and i'll show you what these ones are later so these are all of the other ones so i have lead source so these are all my lead sources they can either be a client referral cold call inbound lead purchase leads ryan's website yellow pages this is wherever i can get all my leads from okay and these are entered multiple times so let me delete this and then same here these were also entered in multiple times okay perfect so now i'm going to go to my lead source column same thing highlight all the way down right click it whoops so i'm going to go to status all the way highlight it all the way down to the very bottom you can change it here so it goes to the very bottom select the date range make sure that it connects to the statuses to the very bottom and then let's go to lead source same thing i go into really good detail in the last video so i'm just going to kind of breeze through this part you guys should already know how to use drop down menus if you guys watched the other video so all of my lead sources here and then product type is the last one right click data validation and then i'm going to highlight all product types and if you guys have any new product types just add it in as you go okay so this is the current sales data and then now for the commission's amount right so this is the way that i did it before you can do it several ways okay so let's say you put in the total amount that the sale was worth right now you can either and then let's say there's a percentage that you want to enter in so let's say you want to have 10 percent you want to type in 10 percent and then you want to just have the commission amount just automatically calculate 10 of this total amount so what you want to do is you want to click click on column j and change it to a percentage type form as percent so now it says a thousand percent which is wrong so let's delete it and just type in 10 again now let's say we do that go into the commission amount and type in equals this times this column so the commission percentage times the sales amount column you can auto fill this down if you want it's only going to go down here you technically want to have it go down to the very bottom if you're on a mac i just always press command shift down all the way down and then it goes all the way down and then i press command d on a pc it's with control instead of command and this drags the formula all the way down so now whenever you type in any percent like eight percent it's going to automatically calculate this right so you can do it that way or you can just type it in manually yourself you know this is pretty simple the only thing i would add is an if formula so that these zeros aren't always always there so i'm going to say if j2 is empty leave it empty otherwise perform this calculation so now it's going to stay empty even though the formula is here now it's important to note that if you do it this way if you kind of delete an entire column this formula is going to delete from here right so now there's no formula up here so make sure that if you do do that you drag it down you kind of just drag this formula down so that it covers that formula as well that's something to just note um just in case you do it that way but besides that now we have this is a calculated column sometimes i'll make like a formula column gray so people know that not to type anything in here because it's a formula okay so let's say i want 10 all the way down i'm going to fill this up as 10 okay now the next step we want to do is let's see we want to do some formatting to make it alternating color so it's easier to read and maybe some conditional formatting on this and then kind of format some of these colors so i'm going to highlight all of this and i'm going to change it to let's say this color here and then i'll make the font white just so it looks a bit better i'm going to middle align it and then i'll probably add some white bordering here just so it looks a little bit better that's kind of an ugly black just go full black okay now if you want to add alternating color so it's a little bit easier to read go to format alternating colors okay so just close headers and just hit done i don't know why it took away the colors but just make the color black again so now it is alternating colors again okay now the next thing we want is some conditional formatting so that whenever we let's say it's delivered it's going to be green if it's processing it'll be red and in transit it'll be yellow so you can look at it at a glance and see what's going on here oh i forgot to add in the drop downs for lead source so data validation drop downs and lead source if you know how to do all this data entry stuff feel free to skip ahead i'll have time stamps so you can go to just straight to the building the dashboard okay so that part is done now let's do some conditional formatting so click on the column that you want to have conditional formatting let's go to format conditional formatting and then we're going to apply this to the entire range of m so m2 all the way down and then you're going to do a custom formula under format rules and then we will say um and then we will say m2 to m equals processing or in quotation marks processing and then let's make this color i'm using my custom colors i'll show you guys how to do this it's better to kind of have like a palette so that your dashboard looks a bit more professional hopefully with your brand's colors so we'll do processing will be that color and then hit let's see let's add some dollar signs here just to lock this in just so it's a little bit safer hit done well you don't hit it done actually hit add another rule and then just change that word in the quotation marks to the next status which is in transit we'll make this color maybe that color and then hit add another rule and now we'll do this one be some sort of green whoops and transit should be delivered and then let's make this sort of green and hit done okay everything looks good now so what this does is every time we do pro and transit processing and delivered it'll automatically update perfect okay so let's see what else there is to do on the data entry side so yeah this looks pretty good um now whenever we get a new sale i guess this first column is a start date right and what we want is data validation so that it has to be a date that's entered in so the way that you do this is you highlight this all the way down just like before data validation but this time click under criteria change it to a date and just is valid date and hit save now whenever you double click into this it's going to have a calendar you can just select the date we can select um let's see the client name let's just put jan no put random addresses select the sales person type in the sale amount the percentage the product processing inbound call and perfect so that's how you do a set of data entry basically okay another thing that you might want to do on these types of sheets is group some columns so let's say that you don't always want to see you know the address the city the state and the email of the client name right you can just highlight all of these columns right click it and hit group and then there will be a little addition or subtraction sign here and it'll hide all those columns and you can just toggle back and forth right so let's say you know some of these can be grouped just to make it a little bit easier to look at whoops do it this way just so you kind of save some columns because you don't always need to see some of this information so that's how you group columns okay so now let me hide some of this okay let me move some of this over so i'll put all my new stuff over here towards the end okay so the next step now is going to i guess start to build this dashboard right so let's create a new tab and then we will call it dashboard v2 now for a dashboard the first thing you want to do is i guess we can kind of add in some queries right now this is going to be using the query formula now i went over this in the last video but let's kind of go over in more detail so that we can kind of get some good queries going so let's say the first thing we want to do is we want to see the sale month we want to see our number of sales our average sales and our total sales by month right if you look into here you'll see the formula is written here but the first thing we need to do is add a calculated column so what this is if you look at my original sales data tab it's all of these columns over here and let me show you why we need to do that so let's say we want to grab you know all we basically want to group all of these all of these uh sales by the month right so let's call this the month column so the month here i don't know why this is showing up okay so let me make all of this blue and then i'll add some bordering here okay so what we're going to do is we're basically going to use the sale date and tag this entire row as a as the month of march of 2020. and the way that i do that is i use the date formula so the date formula and then i use the year so what you need to do is enter in the year the month and the date right so i enter in the year of a2 to build this new date and then the month of a2 and then i just always use one so it's always going to show the first of that month right so if i drag this all the way down you'll see here 3 1 march 1st 2021 december 1st 2021 so if i hide this you'll see that it correlates correctly now let's have this show as only the month and the year so let's highlight that column go to format go to number and then choose this one so it's going to show as the month just like this the thing is if you go to the very bottom it reads empty rows as december of year 3799 so what we want to say is we want to say if this is filled in then apply this formula so it's another if formula this is a pretty good formula to kind of know very well so i'm going to say if a2 equals two quotation marks for blank comma if it's true then comma again just basically leave it empty and then this formula falls in the value if false and then i just hit enter and then i drag this formula all the way down and now you see it's going to be empty because this is empty okay so now that we have this month column here we can select it in our query formula right so going to the dashboard now i'm going to type in equals query i'm going to select the data whoops i messed up i hit enter by accident so query sales data v2 i'm going to highlight all this data i'll highlight it all the way to the very end actually so a1 to z comma in quotation marks i'll type in select so now i have a p column which is the month so i can select p and then comma i want the i want the number of rows that are basically right so the number of sales amounts so i'm going to type in count i you can use any column but i'm just going to use column i what this is going to do is it's going to count every time there's something in i it's going to count it as 1 right and then comma count i and then i want the average of the sale amount so average i avg comma now i want the sum of i and then group by now you have to group it by whatever is in the select except for anything that is aggregated aggregated means anything with a formula that's like summing it up or averaging it or counting it so in this case only p so i'm going to script by p and then end it in quotation marks and close it now you'll see what it does is it grabs it groups it by the month and it tells me the total number of rows with a number in each month the average of that in each month and then the total sale amount so that looks kind of like this right or this is the weekly one now that looks kind of like this right but it let's let's kind of format it a little bit better um so going back here you see it has this empty row and the reason it has this empty row is because this data set is going all the way down from a1 to the very very bottom right and you'll see down here there's all these empty rows and we don't want we wanted to ignore these rows so the first thing we're going to add is a where clause now by the way with the query formula everything goes in this order now you're always going to have a select right and sometimes you'll have it where whatever but um you basically whenever you use these you have to use it in this order so the where comes after the select the group by comes next the pivot the order by the limit offset label format and options in this tutorial i'm only using the ones that i checked so i'm not using a pivot i'm not using offset i'm not using format or options but maybe in a future video i'll do i'll use that in the query formula but um so this is the order that it has to be in okay so i'm going to add in a where clause so as you see here the where comes after select but before but before group by okay so what i'm going to say is i'm going to say where column a which is the sales date where column a is not empty so basically just ignore all empty rows right so before the group by i'll type in where a is not null now that's just something you need to memorize is not null is basically how you say is not empty so now it's just going to select all of this where a is not empty and now that line disappears now another trick i went over in the other video is how to organize this because this is kind of confusing to read right and it's going to make it look a lot harder than it is so click on before each what do they call this before each clause it's called a clause before each clause just basically press option enter and it'll bring it to a new row in this and this is going to make your query look so much easier and like less intimidating right so now it's looks pretty normal now okay so now we have that the next thing we want to do is we want to use the label clause which will change the name of this because right now it's called whatever you however you ripped it so it says count sale amount because that's the name of the column average sale amount and some sale amount but that's kind of ugly so the label goes at the very end it goes after the group by so i'm going to hit option enter label now you're going to basically whatever you selected you have to use the exact text so let's say i want to change count i label count i and then you hit a space okay and then you use a singular apostrophe to wrap it so let's say i wanted to call this one number of sales like that now it's going to change this name to number of sales now if i want to change average i then i hit comma average i average sale and then put an apostrophe and then same thing some i i'm going to call this total sales enter now we it looks a lot prettier now the next clause i want to add in is order by which comes before label you'll see here because i want to order this by the month but i want to order descending so the latest month is at the very top right so before the label i'm going to type in well let me show you some couple errors okay so let's say i added it at the very bottom so i'm going to type in order by p is the month right p and then i type in d-e-s-c that means descending i'm gonna get an error and it says unable to parse encountered order at line six now what this means is wherever it says encounter the certain text that's where the error begins so it says it encountered this order when it was expecting something else which basically means that text shouldn't be there so i'm going to take this i'm going to cut it and then i'm going to add it here instead order by descending if i just deleted if i deleted the desc it's going to automatically order it by ascending now i can order it by anything i want i can order it by sum i which is going to be total sales descending now the highest sales is going to go first right but like i said this one i want to order it by p descending okay so you can basically read this out quite logically now let's see what else we wanted to do with this dashboard i mean with this particular query um okay so that's pretty much it except for adding in a date filter which i will do very soon okay so let's move this down move this down a bit so i'm going to add in a few more rows i can just highlight this and bring it down here okay um let's say now and then let's say i want to format this okay so i'm going to grab these two and highlight it all the way down and then let's bring it all the way up here i'm going to change this to the dollar amount so that you can actually see the dollar let me delete some of these rows because i'm not going to need this many just delete all of these rows here okay and see the next thing i wanted to do now is i want to write a couple of other queries except now i want it to have um i want to do it by status and referral channel and things like that so it's essentially once you write one query out it's so easy to just copy and paste it right and let me show you what i mean so going back down to my new dashboard i'm going to enter here i'm going to take this entire query and then let's say i want to put this one here let's i'm going to see where i put it okay so i put it put it down here at h30 so i started everything at row 30 last time so i'm to do the same thing i'm going to start everything at row 30. so it looks exactly like it okay and then i started this one at h30 so i'm going to just copy the same exact query right and let me show you how easy it is so this one now is by oh h10 so i put in h10 it's by status okay but it's the same metric so this is why we can copy it so let's go to the dashboard h10 is where i put it you can put it wherever you want doesn't really matter okay now if i go to the sales data the status is m right right now i'm grouping it by p which is month now let's say i change this i change the p to the m so now i'm saying select m and whatever you change in the select you always have to change it in the group by as well as the order by if i press enter now it's still going to work technically let's see okay so it says column and order must be in select this is because order by p is here but there's no p in the select you can only order it by something that's in the select column okay so i can order by m count i average i or semi so that changes to m descending uh that just means it orders it by status alphabetically what i probably want to order it by from now on is by total sales descending so i can see the highest sales first someone changes to some i cool so now i have this here and all you're going to basically do now is just kind of copy and paste some of this stuff right so now this one is by sales person so i started this one at cell h30 so sales person i'm just going to copy the same exact one put it wherever i want to put that query and then salesperson is column h so i'm going to go in here and i'm going to change it all the m's to h basically just twice okay see how easy that is and then i'm going to do it two more times because now i have referral channel and leads a referral channel and product type referral channel and product type referral channel and product type referral channel is column oh it's lead source so column n wait referral channel and lead type on product type whoops okay product type okay so lead sources column and product type is l so n and l so i'm just going to change this one to n okay and then i'm going to change this one to l okay now you'll see i have my four queries here right and then i can change these ones to dollars these ones to dollars these ones the dollars now make sure that you actually you might want to format it all the way down because if you add more product types then it's not going to show up in the proper format okay okay so this is kind of the skeleton of our dashboard now the next thing you want to do is want to add in a couple of charts right now if you see here in the dashboard every chart is the same except for this one right so let's start by adding in the sales by month so let's go to the dashboard obviously a dashboard looks a lot better without grid lines without these little gray lines so go to view and hit grid line so it goes away now to add in our first chart let us highlight all the way down just in case there is a bunch more data and then just hit this chart over here or go to insert and hit chart now we want to make this one a i guess a line chart works but all we really want is is number of sales and total sales so go to series go to customize and go to series or go to setup i guess um chart style setup okay here it's going to automatically take whatever you highlighted i'm going to delete average sale use row aggregate so let's see customize aggregate seems to make it look like it shows all the months here so let's do that it's going to aggregate it by month it's going to add it up by month and what's cool is you can click on certain elements of these charts and you can go directly to that setting so let's say you want to change the title to number of sales versus total sales by month it's kind of long but so i guess i'll just call this sales by month okay and you can change all the fonts i changed the theme settings earlier i'll show you guys how to do that in a bit um okay so let's kind of customize this chart so it looks a bit better so go to customize go to series series means these little lines here and then let's change number of sales to the right access so that it can actually show properly okay you can have data labels which might be a bit messy you can show a trend line see how everything is trending all things like that and then let's see i'm going to delete this because you don't really want that there just take some space we know that it's by month so by month yeah that looks pretty good to me let's see how i had it here oh i added some of these like data points so i can click here and then point size get like a two point maybe like a five point do it for this one as well just do like a five point oh and then one thing is we need to reverse the order because if you see here since we descended this one since we ordered this by the latest month first we need to change the order of this so let's go to horizontal axis and click on reverse axis order so now it's in the proper order okay so now let's drag this over here i think i had some more space on this side oh i guess not oh i know i did i did have a column a okay so i'm going to add in just so it's not so close to the very edge here it looks kind of bad um i also want to change the background of this it looks a bit better so i had this color which was a custom color so what i did was so you can just highlight everything change the color here to custom now it's always a good idea to use a kind of coherent color palette for your dashboard so it looks a bit more professional don't just use a bunch of random colors they have certain theme colors that you can use so that everything is a bit more coherent you can kind of just select a theme i have a custom theme i just kind of googled like a certain palette i just recommend you use like a palette so that all the colors kind of match so these are my theme colors what you can do is you can go to format theme and then just click customize and you can just add in like the accents of everything and then you can just change it to whatever you want like a custom color you can use like the hex codes and then what it's going to do is whenever you click on the color there's always going to be your colors here you know or like your custom colors here just to make it a bit easier and i changed the font as well it looks a bit more like officey so the show so i made it that okay so that is one thing and then it looks like i had all of this table area white so it stood out a bit i like the way that looks so what i'm going to do is i'm going to make this section white just so that it pops out a little bit this is all up to you this is just how i decided to do it yeah i'm going to kind of skip this part a little bit okay so that part is done now the next chart we want to add is going to be these pie charts or you know what i'll do that later on because you guys kind of know how to do that or i guess i'll do it now so all the charts will be in the same chapter okay so let's add in some of these charts here so just highlight all of this data in case it comes down here go to chart and then we'll change this to a pie chart and that's basically it number of sales but let's say i want this one instead to be so let's do i10 okay so what you can do they make it real easy now just click on this and just change the total sales go to customize pie slice um see chart slice label i want the percentage to show and then text color can be white or auto auto is probably better so they can decide okay perfect so this is it pretty straightforward and you'll basically do this for all of them so you can quickly see what is going on here so you basically repeat that same process for everything okay now the next step we want to do is let's format some of this so it looks a bit better and then let's add in the scorecard and then finally we'll get to the filters okay so the filters are kind of the most difficult part so let's get to that towards the end so i'm going to spend some time adjusting some of this stuff so let's make this one black and then change these border colors let's format this to the middle and then we'll make this font like a 12. you know i'm kind of lazy so let's just i'm just going to highlight this you can just highlight this exact stuff hit this paint thing to paint format i'm going to go to my dashboard and just click it here and it's going to basically format it to look just like that and then i can just do that all the way across so much easier format and format perfect i should just do that with this as well format so i'm going to copy all of this stuff okay um okay it looks pretty good now let's get to the filters okay so let's let's kind of add in a start month and month filter so what we have here is so where did i put that i put that in starting in cell d2 so i'm gonna have this open in a different screen so starting in d2 i had something called start date and end date or maybe it was start month end month so set start month and month now what this does is this allows you to control this entire dashboard based on this date selection right so what i want this to show is i want this i want them to be able to click on this and then show them all the months that are available but i don't just i want to have it show in the right order so if i highlight both of these and i go to data validation and i go to my sales data and i just do the month column so from p2 all the way down and i hit save go to my dashboard let's make this white so it stands out if i click here all of these months are just like in random order right it's in whatever order this is in so what i actually want is i want to have a list to refer to where it's all these months in order so i'm going to go to my drop downs i'm going to create a drop down option called month and then i'm going to type in equals sort unique i'll go to my sales data i'm going to highlight oops mess that up okay equals sort unique go to my sales data oops and i'm basically going to highlight from p2 all the way to p what this does is it creates a sorted unique list of everything in this column column p right now what i can do on my dashboard is i can refer to that for my drop down menus data validations and then now i'm going to go to sales data and then oops i'm going to go to drop down i'm going to highlight all of this to the very bottom i2 to i hit save now when i go here it's going to show up like this now unfortunately it's going to show up like showing the first of the date even if i change um my drop down options here even if it if i change the format here it's still going to show up in my drop down but that's not too difficult you're right to know that 1 1 equals january and so forth right so i'm going to select some dates in here now the question is how to apply this how to apply this into this formula right so there's a kind of like a certain syntax that you need to use in order to make this work oh by the way let me change this format at least to a number and this okay number so it's only going to show the year in the month so it looks a little bit better okay so what we're going to do is we're going to say in this query formula we're going to say where column p which is the month is in between these date ranges so it's only going to pull in the rows that are in between this date range and the way that you do that is you type in and so whenever you have multiple where clauses you type in and is the month column is let's see it's greater or equal to e2 right in order to do a reference you're going to have to okay you basically have to use this exact exact text right either type literally type in the word date to basically let the formula know that you're using a date three quotation marks the and text and then the cell and then type in this exact same thing now if you have you know i'm gonna have a cheat sheet in the query reference tab where it has this type of stuff you can just copy and paste it but you can just look at this screen and you'll see it and then i'm going to change this to e2 so what this says is it's saying only select where a is null and where p is greater or equal to the date that is in e2 right so if i just hit enter here and then i select let's say i select october 2021 it's only going to show all the dates that are greater than october 2021 right what i want to do is i want to say and where p is also less than or equal to this which is an e3 so i'm literally super easy once you get it down once i'm going to copy and paste everything from and over and i'm going to say and p is less than or equal to everything in e3 see super easy now it's only going to show this so now you basically have it now i can filter it by month now in order to add it all to here look how simple it is i'm literally going to take everything from the and i'm going to copy it and i'm just going to add it here so once you write the queries one time it's so easy to just add it into everything else so where a is not null and and so now you'll see if i select it's going to automatically refresh everything see look how simple that is now the next thing i want to do is i want to add in some filters a filter by specific agent or status or lead source right so under cell h2 i'm going to type in agent status and lead source and then for the agent one i'm going to have a date a data validation or drop down list i'm going to go to drop downs all of my agents or sales persons hit okay and then here for my statuses if i go to sales data if i go to whoops if i go to drop downs and status if i go to my dashboard my lead source same thing so this just to create a drop down menu for all of this stuff okay so go back to my dashboard i'm gonna make this white so it stands out a little bit so okay now let me show you how this would normally work right let me show you my little my little trick to make it work even better so the way that this normally works is let's say the agent column is column h right so normally in the query formula what you would do is so we have all these ands here it might be a good idea depending how many where clauses you have sometimes you put your ands on different ones so you can read this is even easier to read right so what you want to say is where h equals now when you're referring to a text to a cell reference in a query column this is how you do it three quotation marks two and signs and three quotation marks and in between this and sign you're going to put in cell reference in which this case is i2 so what this does now is you'll see that you know if i select if i select jim halpert it's going to update right so if i select dwight schrute it's going to update it and show only filter and only show dwight shoots data that's basically how you do it it can't be one quotation mark this is for a difference only for numbers i think uh but yeah so that's basically how you do it and this is how i used to do in the past um so like this right now the only issue with this if you do do it this way is what if this is empty now it's going to say no data and what i used to do is i used to have a bunch of if statements of the same exact query if this one is empty use this query if not use this query but i've learned a lot since then and now let me show you guys a really really cool trick to make this work even when you want it when this is empty okay so what you want to do is go to the sales data tab and we're going to create another another uh calculated column right now what i want this calculated column to do i'm pretty impressed with myself on figuring this out because i just figured this out kind of recently but if you look at my old sales data tab agent month filter is true so it says true all the way down right but then if i go to my old dashboard and i select an agent let's say i select dwight shoot and i go back to my sales data it's only going to show up true on the dwight shoot rows so where is the sales person so dwight shoot it's going to show up true but then everything else is going to be empty so basically what i'm doing is i'm going to call it true if if their agent is being selected and if it's empty then everything is going to be true and then what i'm going to say is i'm going to say to this query in this query i'm going to say only select where this column is true so i'm going to call this agent filter okay i still don't know why it's doing this to be honest why it's changing it to this color so no one okay so the goal here is to get it to say true if if this is empty everything's going to say true otherwise if there's something in here then it's going to say true only on this agent so let's see how i did this i did this with an if formula f1 okay so what i'm going to say is equals if if i'm going to go to this dashboard equal if dashboard i2 equals blank then true otherwise okay it's true so that's the first step and then i'm going to bring this all the way down and then oh whoops okay a very important thing here okay i need to hard code and reference this with a dollar sign which means it's only going to stay at i2 because if otherwise if i drag it down it's going to make it i3 so that's very important okay so now it's all going to be true and it's going to pull it's going to pull everything okay now let me check back quickly against this formula against my old formula it's a monthly dashboard equals h2 then true okay now the next step is so this is if that's agent filter is empty then just mark it as true however if it's not so this is the value if false if it's false i'm going to go back to this dashboard whoops okay however if it's not why can't i okay if it's not i'm going to put in another if if this selection equals back to my sales data jesus i don't know why this is not this is being so difficult if it equals h2 h2 then true otherwise it's empty and then hit enter so and then let me hard code the reference to h2 to i2 in the other tab so what this says is if if this is empty they make it true but if i if this is not empty then check and see if this matches this right and if it does then mark it as true if it doesn't then leave it empty so this is this is basically how we're going to do this okay and then um okay so let's say now i delete this selection here i go to sales data you'll see it's all going to be marked true right let me and just follow just stay with me here now the only problem is that it's also going to mark all of these empty rows as true so what we're going to say is this one's really easy if a2 is empty then leave it empty otherwise use this formula now now this formula works perfectly now what i'm going to do in this formula is i'm going to say let's see what column that is so that's now column q it's called agent filter okay and then i'm just going to add in another where clause that says and q equals true one second and q equals true hit enter and hit turn now it's not working let's see why it is not q equals true so state of v2 dashboard oh because i had wait so let's see what's wrong it's probably good to troubleshoot this live so i can show you guys what's going on and h equals oh because i still i left my original the example i was showing you guys that was wrong okay so now this filter is going to work if this is empty it's going to still show everything because everything here is true if i select an agent now let's say andy bernard it's only going to show andy's data because it's only going to mark this as true so we're basically using this column as a filter and that's kind of a really cool thing of the query formula right so now we just want to have to add do the exact same thing but with but with the status and the lead source right so it's i3 and i4 for status and lead source so we'll call this one status filter and lead source filter i did not know what let me fix what's going on here because this is really annoying what why is it doing this is it the alternating colors this is so weird i've never seen it do this before okay whatever okay so anyways um this is so ugly okay so now we're going to do the exact same thing so you can just copy this exact same formula and turn it in over except now for status filter it is not going to be it's going to be instead of i2 it's going to be i3 so let's change it all to i3 and then we're going to say if i3 is equal to not h2 this one is status so the status column is m to m2 okay i'm going to bring this formula all the way down so now if you go to the dashboard if i select a status called in transit go to the sales data only the in transit ones are going to be marked as true all right so let's go back to the sales data here copy the same exact thing but now instead of i3 it's we're using i4 and then we're going to say if it's equal to the lead sources column n and i 4 is where the drop down menu is on the dashboard okay so i'm changing all the m's to okay perfect now i'm gonna pull this all the way down okay and then now if you select this inbound only the inbounds are gonna be true so now we're gonna add another where clause where r equals true and s equals true okay so let's go back here where q is true and r equals true and s equals true now you're probably asking why there's not where there's not quotes around true or like an apostrophe like how if we ever want to reference any other text in the query formula it usually has to have a quotation around it and that's because the words true and false are special cases um they are like boolean metrics i guess and then those are kind of like the only words that can be used true or false and you can honestly use anything you want here you can make it say yes or no and then you can say as long as it equals yes or no but i think for yes you'd have to type in either a quotation mark within a posture for you probably um yeah you type it in with one apostrophe okay so it doesn't have to be true that's just what i use just because um these are like special or phrases i guess but you can also use like yes no but you just have to add quotation marks into them if you're referring to them inside of a formula but if you see here um yeah now it's going to work right and then now we just have to add in this where clause basically this and all these three ends into each of these so you see how easy this is um okay so see how ugly this looks now what i'm going to do is i'm going to put all my ends on different rows this is just going to make it look way more organized and then just add that in look how simple and this is honestly like a really cool trick it'll really help you guys with understanding the query formula just how to manipulate sheets in general um okay super easy let me do some selections just so i can see it change it's more satisfying okay where then and this same thing here so yeah i hope you guys agree that if you guys just organize this better as you go it's going to be so much easier to maintain right okay so that's pretty much it now that's pretty much the bulk of the heavy work right now that we've done for this now there's a couple more aesthetic things i i want to do so let's say i want to add in the dunder mifflin logo i would probably take you know just take any image basically and then just save it save image as download and then you want to insert an image i always do image over cells for like logos not in a cell now this is to help make your logo just look at your dashboard look a lot more professional and just something that is a real easy win now i chose a bad logo for some reason but i think you get the idea so something like this um you of course want to maybe bold this a little bit maybe right align this so it's right up into the box same here right online this okay so the next important thing that we want to add is kind of this little score cards or call out section right and this is important for if you just want to at a glance show some important metrics okay so i definitely recommend that you guys do this so let's go into the dashboard again and let's start at c let's start here at c six okay so we're going to put number of sales here average sale here and total sales here now i am going to make this all black just so you can see this right away what color did i use okay so i use this color okay looks a lot better to me now then i'm going to make all this text into here white then let's fold this and then i want to make this a bit smaller okay so this part is pretty straightforward um so no matter what your most recent month is going to be in this case cell e31 right so this one is literally just going to be an equals a cell reference to this equal to this and equal to this so that's pretty pretty easy so if i were to change this to september it's going to reference this cell right and then for these ones it always looks better if it's center aligned and you're going to want to make this text bigger let's make it 16. let's make sure that we wrap it and then let's get rid of the decimal points because this is just kind of like an overview i guess we should middle align this too so it matches up okay okay all right so want to make sure that this row is okay perfect so now we have the main scorecards right and then for here is just another cell reference so i'm going to reference whatever is here you know september of 2021 and then this one now what i want to show now is comparison against the previous month right so i guess i can just reference this one now the formula for percent change is new minus old divided by old so let's just do equals the new number which is always going to be this number minus the old number divided by the old number and let's make it a percentage 33 and then i can just literally copy and paste it over here and it's going to just reference the correct cells so that is basically how you do that okay now let's now let's say you added let's say we select march of 2020 it's going to give me these ugly errors right so in order to fix that just to wrap it in a quick if error if there's no error leave it oops blank misspelled that if error value if error empty if error comma just leave it empty well actually i don't want to leave it empty i want to put a dash so it shows a dash because it looks better to me okay so let's say we selected a different month now let's say let's do some conditional formatting again to make this look a bit better so i'm going to say if this cell is greater than zero then i want it to be green okay and then i'm going to say i'm going to add another rule and i'll say if it's less than zero then i want it to be red i'm going to use red and then i'll say if it's equal to zero then i'll have it be this little yellow color okay so let's see how make sure this works oh everything is increasing where are the decreases that's crazy okay perfect so here are some decreases um i'm not sure if these colors stick out enough but i don't love these colors but whatever you get the idea okay so that's how you do that part let me adjust this up and then now we want to add some totals so the totals are quite straightforward i'm just going to sum everything i'm going to sum everything to the very bottom and just pull this over okay that's how it's going to look and then i'll call this totals and then i will make this black as well and i'll make the font white and then let's add some nice bordering so it looks just like everything else then let's middle align this oops and let's add a border to the bottom as well okay so the next important thing i want to add is a sparkline chart now sparkline charts are a great way of just kind of adding some more visualization so let's do a sparkline sparkline and then the data will be here and then i'm going to add in a chart type comma bar so everything is separated by these semicolons okay and then i want to have the max of this bar chart be the max of this entire column just so it's going to compare it against this entire column right so it's going to take this number and then it's going to basically compare it to see where it's at of this entire column so this total here is 4 million and then 158 is the max okay i'm sorry it's comparing it's the highest number so that the max of this entire column is 257 000 so that's the you know that's the target right that's where this number is and then um 150 158 000 is about a little bit over half of this right just so you can see where it stands compared to the highest one i want to lock in this 31 reference and then i can just pull this all the way down again you get these little ugly errors the iferror is a good formula to always learn and learn how to use comfortably if there's an error i'm just going to leave it blank okay now this orange color doesn't quite match the theme of what i'm doing you can also change the color okay we can change the color basically after any semicolon i'm going to type it color one and then you can literally type in a comma you can even just type in blue if you wanted and it's going to make it blue right but that doesn't really match too i probably want to use like an actual blue so i have this little plug in here that i can pick a color palette and it'll give me the hex code so instead of in blue i'm just going to put in the hex code and it's going to change it to that the reason why i put color 1 is because color 2 is going to be the the the negative of the bar so let's say i copy this entire thing if i make color 2 color 2. okay well it's not working but i'm going to do a follow up on sparklines where i'm going to show you guys exactly how to use all the sparkline charts because you can use line charts it can this can be a line chart this can be like a bunch of other different things so you can actually do a lot with sparklines so just watch out for like my next video it might be about sparklines but yeah so this is basically what you want to do i can just copy this formula and i can just paste it in here but now instead of referencing um l e31 i'm just going to reference l11 l11 and then i want the max of l l all the way until i think it's l 25 or something right so that's basically what you're going to do you're just going to copy these formulas all the way around you know now i'm going to do r 11 and then from r 11 until r 25 and just bring it all the way down pretty much you might want to lock in these bottom numbers as well so 29 so make sure you lock this in because when you drag it down there's going to be some errors cool okay the last thing now is going to be i'm adding in this color scale right so what you want to do is you want to highlight this and the reason for adding this in is just to give another kind of like a visual aspect of this dashboard so that when they look at it at a glance it's not just a bunch of numbers right they can actually just kind of see which one is the highest number because all these numbers can get a bit confusing so go to format go to conditional formatting go to color scale now i pretty much always go from lightest to darkest i'm going to select from white to green but instead of green i want to choose one of my theme colors so i'm going to choose well that's a bit too much of the same blues i'm going to choose this one right and for this one you can't you can't just highlight it and then add another rule because it's going to mess it up if i do this and if i add another rule it's just going to and i can't just change the range to d because it's going to combine all the ranges and it's well actually maybe it does work okay so i guess that does work add another rule and then i'm going to go to c here okay perfect so now if i look at here i can see that this month is the highest total sales because it's dark this one is also quite high high is average and so forth so this is pretty much how you build this dashboard i would say the most important part of this entire video that i made was how to do these filters right that's kind of like a really good hack a good trick to pull in only specific data sets based on this formula here one more thing i forgot to show you is how to make this a weekly dashboard as well now this one is a little bit tricky so i'm going to include a cheat sheet if you guys want to buy this template then there's going to be a cheat sheet to kind of give you some formulas kind of stuff i went over but i'm just going to show you here in this video as well okay so you can just follow along with this video what this is it's basically the same exact thing okay you know how we added a month column now we're just going to add in a week column so that we can group the data by week if this turns white oh geez okay so i'm going to call this week ending so what this is is now i'm going to group this by the week it's you can do it by anything okay so let's say this date here is march 17th of 2021 it's this date right now it's up to you it's up to you march 17th what you want to call that week you can call it the first sunday the sunday before right which would be you can call it the week of march 14th i personally prefer to use the week ending date it just helps prevent errors when you're kind of selecting things selecting calendars because it's going to select everything before that that week right just it just probably makes better sense to use the weak ending because when you select it from a calendar it can get a bit tricky if you for example if you were to select like the week before you might miss some dates based depending on how your formula is built so in this case for march 17th i'm going to call it march 20th okay so it's basically march 17th belongs to the week ending in march 20th if it's march 19th the same week if it's march 21st it belongs in march 27th so basically i'm finding the next saturday okay so this is the formula for that i have an entire um sheet of of these formulas depending on which day you want to find so for example i want to find the next saturday it looks something like this okay you can either screenshot that or whatever you want to do but it'll also be under the cheat sheet section so i want to find the next saturday i just enter it in and i just change the a2 to wherever my date is in this case it is a2 which is kind of nice so like i said it's march it's march 20th right and it's going to just do this automatically so that's a pretty cool formula just have to copy it in exactly like that okay perfect now for the weekly dashboard it's literally going to be the exact same thing just duplicate this dashboard so the week column is in column t right so now instead of grouping it by p i'm going to group it by t t t d t you see now everything is now i can see for the week ending in november 27th 2021 i got this many sales super easy now another thing you get to change is this drop down menus now same exact thing as what we did with the month we want to do with the week so equals sort unique sales data i don't know why it's doing this but sales data is in column t so the drop downs i'm going to do t 2 to t okay now on the new dashboard i'll call this weekly dash change this one to um weekly dash dropdowns v2 weekly dash start week and week and then whoops now let's change this format back to an actual date format okay so now you see here same exact thing however you're also going to need to add in these new filters but referring to the new dashboard okay because because all these true statements are going to follow the original monthly dashboard and you want to make it so that it refers to a different tab basically so that's basically all you need to do to have this weekly dashboard working so that pretty much covers everything so i pretty much built this dashboard back from scratch it took me like an hour explaining it um yeah so this should save you a ton of time because i remember when i was building this when i was first starting it would take me literally like six to eight hours i'm trying to figure everything out but yeah you can just watch this video hopefully it shows you how to create a very professional looking dashboard feel free to purchase this template if you guys want to have something to follow along and support me i'm going to be creating a bunch more videos from now on on how to create dashboards on all sorts of google platforms using google sheets data studio whatever and i'm going to kind of do show a lot of the cool products that i've created using google sheets and then hopefully you guys can kind of get an idea of what you guys can do if you guys have any kind of questions or anything you guys want to see regarding kind of anything in the data i work with anything in the google suite with data if you guys have any questions about this i work with some very common ad platforms such as like facebook ads google ads analytics i create dashboards for all that stuff um amazon sellers shopify sellers if you guys have any questions or anything you guys want to see just let me know leave it in the comments below besides that i have links below where you guys can book a consultation call if you guys want a custom project done i do take on like some projects here and there so if you guys want to reach out to me just check out all the links below okay thanks a lot guys

Channel: Hustlesheets

Views: 29,573

Rating: 4.9633026 out of 5

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

Id: ccTdDPDGEGM

Length: 85min 29sec (5129 seconds)

Published: Sat May 01 2021