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

Introduction

In this tutorial, you will learn how to create dropdown filters in a Google Sheets dashboard using the QUERY formula. This technique will allow you to filter data dynamically by date ranges, sales agents, and order statuses, making your dashboard more interactive and insightful.

Step 1: Prepare Your Data Source

1. Open a new Google Sheets document.
2. Create a tab for your sales data where each row represents a sale with the following columns:
   • Sale Date
   • Salesperson
   • Order Status
   • Lead Source

Step 2: Create a Helper Column for Month

1. In your sales data tab, add a new column titled "Month."
2. Use the following formula to extract the month from the sale date:

   =DATE(YEAR(A2), MONTH(A2), 1)
   

   • Drag the formula down to apply it to the entire column.
3. Format the column to display only the month and year.

Step 3: Handle Empty Cells

1. Modify the formula in the "Month" column to avoid displaying dates for empty rows:

   =IF(A2="", "", DATE(YEAR(A2), MONTH(A2), 1))
   

   • Drag this updated formula down the column.

Step 4: Create a Dropdown for Months

1. Create a new tab named "Dropdowns."
2. In the "Dropdowns" tab, use the UNIQUE function to list unique months:

   =UNIQUE(SalesData!P2:P)
   

3. Sort the unique months:

   =SORT(UNIQUE(SalesData!P2:P))
   

4. Go back to your dashboard, right-click on the cell where you want the dropdown, and select “Data validation.”
5. Set the criteria to “List from a range” and select the range from the "Dropdowns" tab.

Step 5: Write the QUERY Formula for Filtering by Date

1. In your dashboard, use the QUERY function to pull data based on selected date ranges:

   =QUERY(SalesData!A1:D, "SELECT P, SUM(D) WHERE A IS NOT NULL AND P >= date '"&TEXT(B2, "yyyy-mm-dd")&"' AND P <= date '"&TEXT(C2, "yyyy-mm-dd")&"' GROUP BY P", 1)
   

   • Replace B2 and C2 with the cells containing your start and end month dropdowns.

Step 6: Create a Dropdown for Sales Agents

1. Back in the "Dropdowns" tab, manually create a list of sales agents.
2. In the dashboard, use data validation to create a dropdown for selecting agents:
   • Set the criteria to “List from a range” and select the range of your agents.

Step 7: Implement Filtering by Selected Sales Agent

1. Update your QUERY formula to include filtering by the selected agent:

   =QUERY(SalesData!A1:D, "SELECT P, SUM(D) WHERE A IS NOT NULL AND H = '"&E2&"' AND P >= date '"&TEXT(B2, "yyyy-mm-dd")&"' AND P <= date '"&TEXT(C2, "yyyy-mm-dd")&"' GROUP BY P", 1)
   

   • Replace E2 with the cell containing the selected agent.

Step 8: Create a Dropdown for Order Status

1. In the "Dropdowns" tab, create a list of order statuses.
2. In the dashboard, create a dropdown for statuses using data validation.

Step 9: Integrate Status Filtering into the QUERY

1. Update the QUERY formula to incorporate the selected order status:

   =QUERY(SalesData!A1:D, "SELECT P, SUM(D) WHERE A IS NOT NULL AND H = '"&E2&"' AND M = '"&F2&"' AND P >= date '"&TEXT(B2, "yyyy-mm-dd")&"' AND P <= date '"&TEXT(C2, "yyyy-mm-dd")&"' GROUP BY P", 1)
   

   • Replace F2 with the cell containing the selected status.

Conclusion

You now have a functional Google Sheets dashboard with dropdown filters for date ranges, sales agents, and order statuses. This setup allows for dynamic data analysis, making your dashboard a powerful tool for tracking sales performance. For further enhancements, consider exploring more advanced Google Sheets functionalities or integrating other data sources.