QUERY - Drop Down List to Filter Data - Google Sheets

Summary

This video tutorial demonstrates how to combine the `QUERY` function in Google Sheets with dynamic drop-down validation lists. It guides users step-by-step on creating unique lists for Regions and Sales Reps, setting up data validation, and writing a query that filters data based on these selections. The tutorial also covers how to handle case sensitivity and options like 'All Regions' and 'All Sales Reps'. With practical examples, viewers will learn how to dynamically update their data based on drop-down choices, ensuring a flexible and interactive reporting system in Google Sheets.

Takeaways

• 😀 You can combine the QUERY function in Excel with dropdown validation lists to filter data dynamically.
• 😀 The first step is to create a new worksheet for the report setup and add columns for region and sales rep.
• 😀 To create dropdown lists, use Excel's data validation feature and reference a unique list for both regions and sales reps.
• 😀 Use the UNIQUE function to extract a unique list of values from the data for both regions and sales reps.
• 😀 The 'All Regions' and 'All Reps' options can be added manually to the dropdown to represent unfiltered selections.
• 😀 When using the QUERY function, the SELECT statement can be set to filter based on the selected region and sales rep.
• 😀 Use the LOWER function to make the filtering case-insensitive for both region and sales rep columns.
• 😀 Replace hard-coded filter values with dynamic references, allowing dropdown selections to control the filtering.
• 😀 Use IF statements to dynamically adjust the filter string, ensuring blank values for 'All Regions' or 'All Reps'.
• 😀 The final QUERY formula should incorporate both region and sales rep filters, allowing for flexible, dynamic queries.
• 😀 Implement the 'WHERE 1=1' trick to ensure the QUERY function works even if both dropdowns are set to 'All'.

Q & A

What is the primary goal of the video tutorial?

The primary goal of the video is to show how to use the `QUERY` function in Google Sheets combined with dropdown validation lists to filter data based on selected criteria, such as region and sales rep.

How do you create the dropdown validation lists for region and sales rep?

To create dropdown validation lists, you first gather unique values from the region and sales rep columns using the `UNIQUE` function. You then add those unique values to a new sheet and use them as a range in the data validation settings to create dropdown lists.

Why is the `SORT` function used in this tutorial?

The `SORT` function is used to arrange the values from the `UNIQUE` function in alphabetical order. This ensures that the dropdown lists are easier to navigate and display values in a more organized manner.

What does the `QUERY` function do in this context?

The `QUERY` function is used to filter data based on specific conditions. It allows you to return only the rows that match certain criteria, such as a specific region or sales rep, and display them dynamically in a report.

What is the purpose of using `LOWER` in the query formula?

The `LOWER` function is used to make both the region and sales rep values in the data and the selected filter values case-insensitive. This prevents issues where case differences could prevent matches (e.g., 'Midwest' vs 'midwest').

How can you ensure that the filter works properly even when 'All regions' or 'All reps' is selected?

To handle the 'All regions' or 'All reps' selection, the formula includes an `IF` statement that checks whether 'All regions' or 'All reps' is chosen. If so, it leaves the filter condition blank, effectively ignoring that criterion in the query.

What does the `1=1` condition do in the final `QUERY` statement?

The `1=1` condition is used as a trick to always return true. This ensures that if no specific filter is applied (i.e., when both 'All regions' and 'All reps' are selected), the query will still return all data from the table.

What happens if both the region and sales rep filters are left empty?

If both filters are left empty (i.e., 'All regions' and 'All reps' are selected), the query will return all rows from the dataset because the `1=1` condition is always true, and no additional filtering conditions are applied.

How do you modify the query formula to dynamically adjust based on dropdown selections?

To make the query formula dynamic, you build it with concatenated strings that change based on the selected region and sales rep. The formula checks the selected values and incorporates them into the `WHERE` clause, adjusting the filtering conditions accordingly.

What is the significance of using `AND` in the concatenated query formula?

The `AND` operator is used in the concatenated query formula to combine the conditions for both the region and sales rep. This ensures that the query filters the data based on both criteria, returning only rows that match both conditions.