How to Create Dropdown Filters on Google Sheets Dashboard Using QUERY Formula (ADVANCED TRICK)

my name is michael from hustle sheets and today i want to show you guys how to create filters using the query formula for your google sheets dashboard now by filters i mean something like this where you have a drop down list and then you can select the date range and then the entire dashboard is going to update and only show data within this date range the second type of filter i want to show you is how to create something like this where you have a drop down menu of let's say like a sales agent or like a order status and you can select one sales agent and the entire dashboard is only going to show that agent's data and let's say i want to filter it again by the status which is delivered in this case and it's only going to show dwight schrute delivered within this date range okay and the way this works is by using a data source that looks something like this so let's say you have another tab where you keep track of all of your sales data and each row is a sale right and then there's a sale date you have a sales person you have a status and a lead source so those are the filters that we're going to use okay so what i've done is i've created a copy of of the sales data here and then i'm going to show you guys how to do this so the first thing i want to do is i want to do a date range filter so i'm going to put in and i want to do this by month okay so i'm going to do start month and end month and if you look here all i have is the sales date right i don't have the month so what i want to do is i want to create a helper column called month and what i'm going to do is i'm going to basically pull the month of this sale date like this i'm going to use the date formula so i'll type in here equals date let me zoom in date parentheses and then now it asks for the year the month and the day separated by column now i'll use a formula called year formula and then i'm going to grab the year of this comma month formula the month of the sale date and then i'm just going to put in one the reason that i put in one is so that every single so it's always going to show the first of whatever month and year the sale date is in okay so let's say i drag this formula down a bit it's going to show december 1st august 1st like this right and then all i need to do is i need to format this and then change it to show only the month and the year so it looks like this now i basically have the month of each sale date and if i drag this all the way down to the very bottom you'll see that it adds in this random date right that's because if this cell is empty it's going to read it as december of year 3799 the way to fix that is to add in an if formula so i'm going to say if a2 equals empty two quotation marks comma another comma because i'm going to leave this blank so if it's true leave it empty if it is filled in then use the formula i just wrote now if i drag this all the way down you'll see now this is empty so that's our first helper column right so what i want to do now is i want to create a drop down list a drop down menu with every every month that we have so what i'll do is i'll create another tab and i'll call it drop down drop downs and what i want to do is now i want to grab the unique months in this entire column so let's say under drop downs i type in unique months unique i'll type in unique sale month to be more precise and then let's say i type in unique go to the sales data and then i just highlight this all the way down from p2 to the very bottom p it's going to give me all of my months right and let's say now i want to reference that in my drop down menu i'm going to call this dashboard so let's say i right click i go to data validation list from a range now i'm going to go to the drop downs and i'm going to select this entire column from a2 all the way to the very bottom hit save if i go to my dashboard now if i hit this drop down menu now i have every single month the thing is it shows it in the whatever order was in the sales data which makes it a bit hard to understand right so going back to my drop downs i'm going to wrap this in a sort now it's a sort unique it gives me a sorted data set of all the months that i have if i go to the dashboard now this is sorted it's always going to show like this which is kind of annoying no matter what but you kind of i think it's pretty easy to figure out what month is what by looking at the first of each month so i want this drop down menu to go for start month and end month so i'm going to drag this down so now i can select a drop down menu like this right okay so the next thing i want now is to write my query formula now for all dashboard pages i always remove the grid lines because it looks a bit better and then let's type in the query formula now i go into the query formula in great detail in my other dashboard videos but for this one i just want to focus on the filters so if you want to go back and learn more go check out my other dashboard videos but for now i'm going to type in query i'm going to select this entire data set from a1 i'm going to go to the very end because i'm going to add more helper columns okay comma now what you want to do now is you want to select put quotation marks select now i want to group everything by month right in this case so select p and let's say i want to get the sum of the sale amount some sale amount and then group by p you always have to group by anything that's in the select that's not aggregated like by a sum and then end quotation marks hit enter now you'll see here i have the month and the sale amount the reason it has this empty row is because it's pulling all of this data even all these empty rows so what i'm going to do is i'm going to add in a where clause after the select now a trick for the query formula that i always do is i separate each clause by a new line so click before each clause the clause is select or group i have a query reference here in this template so it goes in this order select where group by okay so i separate each one with by pressing option enter so this way it makes it a bit easier now the where clause always goes after the select so now i type in where p or i can put where a is not null it doesn't really matter and what that means it's going to grab only the data only the rows where a is not null which is not empty okay now i have this and then i want to add in a label because i want to change some i to just say sale amount and that's how you do it okay and then let's change the formatting of this to dollars so now it looks like this right now what i want is i want this filter to work okay and now this goes in the where clause let me show you guys how how this looks if i go into sale i'm gonna copy this text right here this is basically the text this is basically the text that you want okay so you have to write it like this inside the query formula what this does is it basically tells it that it's a text in this format it basically lets it know that you're referring to a date so what i do is i always save this piece of text and i just copy and paste it okay so it's three quotation marks the and sign text the cell reference the format and then you close it out with the and sign in through quotation marks so i'm going to copy this and this goes under the where so i'm selecting p where now my month column is here p okay column p so now i'm going to put and so where a is not null and where p is greater or equal to right and then i'm going to paste in my piece of text now instead of e2 the start month is b2 so now i'm saying wherever column p is greater or equal to whatever is in here and then let's hit enter now it's going to be empty because this is blank right so now let's say i do this i select where p is greater or equal to b2 is not null okay then i'm going to type in and p is less than or equal to b3 so let's select something here okay so this isn't working let me figure out why it's probably good to figure out um equals and date to figure out uh errors live so you guys can see what happens okay okay the reason why is i didn't copy the full text amount so you want to make sure that you have the word date in front of it so i miscopied it let me show you what i mean okay it needs to have the word date so this is actually the full text and date because i didn't let it know that it was a date so if i type in the date in front of this now it's going to show okay so what it's doing now it's only pulling anything where the rows are greater than greater or equal to where p is greater or equal to what's in here and less than or equal to what's in here so let's say i only select march it's only going to show march 2020 right and i can change this format so it's only going to show the month and then if i select here now it's going to update just like this so that's basically how you do it the key is this piece of text right here i would save that somewhere okay now the next part is really is much more advanced and complicated it is this over here this filter right here right only by one agent so the way that i want to do that is let's go to dashboard and let's type in an agent text here agent and then for the drop down menu now it's one thing to keep in mind is if this is empty then no data is going to show okay so there has to be a selection here so let's do the full data set now for agent you also want a drop down list to select all the agents names right now for this i would manually type in a list of your agents because you don't want to use a sort unique or you can but i would recommend you have a static data set where you type in your agent so you can manage it yourself and whenever you add in new agents you just delete them if you um yeah so when you want to wherever you want to manage that list i would type it in okay so i'm going to just copy my list of agents of sales people here and i'm going to paste it under drop downs i think there are multiple there are duplicates so let me get rid of that okay so here's my list of agents that i have right and then they are spelled exactly like what is in here okay so you want to connect so let me remove this so what you want to do first is type out a list of all of your agents like this and then on this wherever you enter in your sales data highlight that sales person column and make it a drop down list that goes to the same list okay that way it's spelled exactly the same now once you have that let's go to the dashboard and then let's select data validation equals go to the drop-downs and select this entire column so now what we have is a selection for each agent like this right and then you can you can either drag it out to make it bigger or you can always just highlight over and merge these cells because you maybe don't want to ruin how it looks on the underneath but you need some more space so something like this will look good right and i always kind of write online this stuff okay so now we have this and now we need to make this work in the query formula now the easiest way to do this is to basically go into the where okay you add in the where and you say and you say and equals and okay so what column is the p in so let's go into the sales data now column h is where the sales person is right so what you want to do is you want to go into the dashboard and now you want to say into the formula and you want to say n equals h equals three quotation marks two and signs three quotation marks and then you wanna refer to this cell which is e2 so now e2 so now what it's going to do is wherever you select this it's going to update right so that's the easiest way of doing this just like this so now it's only going to show stanley hudson's data now it's only going to show danny's data and andy's data and so forth right you see it updating the only problem with doing it this way is that when you have no selection here it's going to be empty and the way i used to do that is i used to have a bunch of if formulas that said okay if it's empty use this query if it's not uses query and that's just really complicated right so i figured out a better way of doing this actually is by using helper columns so what you want to do is you want to go back to your sales data and i'm going to call this column sales agent filter now the goal of this column is basically to make it so that on this query we're only selecting we're only selecting the columns that has a true in it now it doesn't have to be true but it can be like any text like yes but i'm going to use the word true okay so the way that this is how it's going to work i'm going to say in here i'm going to say equals if go back to the dashboard if this call this cell equals empty then true and true is one of the few words that doesn't have to be in a quotation marks because it's like an actual like it's like a boolean um but if if i want to say yes then you can just put in like quotation marks yes right but i'm just going to use true okay if dashboard equals nt then it's true otherwise false i'm going to leave it empty okay now if i drag this and make sure that you lock it with a dollar sign because if you don't then it's going to refer to e3 dashboard e3 dashboard e4 and i'm only referring to this drop down okay so if i go to sales data i want to lock it i'm going to lock it with the dollar sign right and then i can drag this all the way down like this okay now when i drag this down now it's going to have just e2 all the way down now it's going to say true whenever it's empty so if i select an agent let's say i select this agent here now this is going to go away okay so if you see here if i go if i instead change this last part where h equals e2 instead i change it to where q equals true so now i'm going to say and q equals true now like i said normally if you're referencing like words in the query formula you have to put it in an apostrophe right but true is the true and false are the ones that you don't have to do that for okay now if i delete this it's going to show all of this data because now it's going to pull in all the rows that have true right now so now what i want is i want it to if i select an agent here i want only dwight shoots rows to say true and this is how you do that okay so under where if value is false i'm going to say if it's blank equals true i'm going to add in another if statement that says if go to the dashboard e2 equals this sales person's name then true else empty okay so now there's true now there's two if statements and don't forget to lock this in right here now if i drag this all the way down you'll see only this where it says dwight schrute is going to be true so if i look here let me remove these columns a bit so you can see it if you see your dwight shoot here now it's going to be true and then this is going to say true right so if i go to the dashboard now if i delete it it's going to show everyone's totals so here i'm going to do a total the total so you guys can see the sum okay so it's like 4 million in sales here but then if i select do i shoot now it's going to change to 1 million if i select someone else it's going to automatically update okay so that's basically what you want to do with everything okay so now let's create a status one and let's let's create a drop down section with all of our statuses so i'm going to sort unique just so i can get the list down but normally you would type this in beforehand there's only three i think so there's only three statuses okay so now i've copied and pasted it so this is a static so under the dashboard i'm going to do data validation then i will select the statuses and now you can select a status right and i wanted to show all of the in transit rows only okay and now we're going to do this the exact same way i'm going to call this a status filter and then i'm going to take the exact same formula it's going to be really easy the exact same formula but instead of e2 i'm going to reference e3 right so let's go to e3 i'm going to change e2 to e3 and then instead of saying equals to h2 i'm going to say m2 whoops m2 and i'm going to drag this all the way down now the selection right now is in transit now it's only going to pull in the rows that are in transit right so in transit here it's true otherwise it's false now what i want is i want this dashboard to only show data where these both of these rows are true because right now i've selected ryan howard and in transit so i really only want to show where both of them are true because this one is ryan howard and in transit right and then where is this whereas this one is just in transit but it's not ryan howard so i'm going to go back to the formula now q now this status filter is in column r so now go back to the formula and i'm going to say and are equals true see and now i can delete both i can select any of these statuses and it's going to automatically update so that's basically how you create the filters okay if you guys have any other questions um just leave a comment and you guys can ask me about this and i'll create another video about it i also have other dashboard videos if you guys want to check it out and it will go over how to create this entire dashboard in better detail basically alright thanks a lot guys

Channel: Hustlesheets

Views: 13,088

Rating: 5 out of 5

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

Id: XGvCT3IeYdA

Length: 21min 21sec (1281 seconds)

Published: Tue May 04 2021