How to filter data based on drop down list in Google sheet?
When working with a large dataset across multiple sheets in Google Sheets, it is often necessary to display or analyze information based on specific criteria chosen by the user. For example, you might maintain a master list of employees, products, or sales in one sheet, and wish to filter records in another sheet dynamically according to an item selected from a drop-down list. This setup provides more interactive reports and dashboards, helping users focus on the data relevant to their selection. As illustrated in the screenshot below, you can use a drop-down list in one sheet to instantly filter and show only corresponding rows from a data range in another sheet. The following guide explains practical methods to accomplish this in Google Sheets.
Filter data based on drop-down list in Google sheet with formula
Alternative: Filter data using Google Sheets built-in Filter Views
Filter data based on drop-down list in Google sheet with formula
This solution leverages Google Sheets formulas to automatically extract and display rows based on a drop-down selection. It is suitable when you want to provide a user-friendly filtering interface without needing extra add-ons or extensions, and when you need results to update instantly as the selected value changes. Please follow these steps:
1. Begin by creating a drop-down list for users to select from. This is often based on the unique entries in a column such as Name. To do this, select a cell in your target sheet, then go to Data > Data validation. For the Criteria, select List from a range and specify the range referring to the Name column in your source data (for example, Sheet1!C2:C). This action will provide a cell with selectable items, as shown below:
Practical tip: If your Name column contains duplicate values, consider using the =UNIQUE(Sheet1!C2:C) formula on an intermediate range, and reference the unique list for the drop-down to avoid repeated choices.
2. In a new sheet or any cell where you want to display the filtered result, input the following formula:
=query(Sheet1!A1:D, "select * where C = '"&B1&"' ")Here, Sheet1!A1:D defines the full range containing your original data. C refers to the field (column) containing the same values as your drop-down list, while B1 is the cell with the drop-down selection. Ensure your column letters, sheet names, and cell references match your actual spreadsheet for correct results.
Precautions: The formula is case-sensitive by default. If your Name entries differ in case between the drop-down and data, results may not display as expected. Double-check for extra spaces or inconsistent capitalization.
3. After entering the formula, press Enter. The table immediately filters and shows all rows where the Name matches the selected value in your drop-down list. Each time you change the drop-down item, the result list updates automatically without the need for manual filtering or formula modification.
Advantages: This method is fully automated and requires no manual refresh. It also supports dynamic data updates if records in Sheet1 change or new entries are added.
Limitations: The QUERY formula works best when your filter criteria are straightforward (for example, filtering by one field). If you wish to apply multiple criteria or more complex filters, you may need to extend the formula or explore other tools such as built-in Filters or Apps Script. Additionally, if the drop-down cell is left blank, the formula may return an empty table or all results depending on the logic.
Troubleshooting: If the filtered result does not show any data but you expect matches, check that:
- The drop-down value exactly matches the data in the column, including letter cases and extra spaces.
- The formula’s sheet name and range are correctly referenced.
- The column letter in the formula corresponds to the column with your drop-down choices.
If you need to filter based on partial match or multiple criteria, consider adjusting the QUERY clause accordingly, for example, using like or and in the selection criteria.
Alternative: Filter data using Google Sheets built-in Filter Views
Another common approach for filtering data visually is to use the built-in Filter View. This is especially helpful when you want to create reusable filtered displays for yourself or for others, without relying on formulas. With Filter Views, you can control which rows are visible based on drop-down style selectors on the column headers, and other users can switch between different predefined views or set up their own temporary filters.
Applicable scenario: Use Filter Views when you have a large dataset and want non-destructive, quick visual filters that are easy to reset and share, especially if your filtering criteria change frequently or are based on several columns.
To use this method, follow these steps:
- Select the full data range you wish to filter in your sheet.
- Click on Data > Create a Filter. You'll see filter dropdown arrows appear in each column header.
- Click the drop-down arrow in the desired column (for example, the Name column) and choose the value to filter by. Only the matching rows will remain visible, while others are temporarily hidden.
- To save this filtered state, go to Data > Filter views > Create new filter view. Give the view a name so you can reuse it later.
- To return to the full dataset, simply remove or change the filter, or exit the Filter View.
Limitations: This method is interactive and easy to use, but it does not provide a separate result table and does not update another sheet automatically. Also, users must manually select the filter value each time.
Suggestion: Consider using built-in filters to quickly explore or share views of your data, especially in collaborative environments. For reporting dashboards or when dynamic lookup is essential, formulas like QUERY are often more suitable.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in