Are you tired of manually filtering data in Google Sheets? Wish there was a way to filter as you type, just like in Excel? Well, I’ve got a workaround that’s almost perfect! While it’s not 100% straightforward, it can save you time and effort.
In Excel, filtering a table as you type your keyword in a search field is a breeze. But unfortunately, Google Sheets doesn’t offer this functionality out of the box. However, fear not! I’ve discovered a clever workaround that will do the trick.
Steps to Filter Rows As You Type in Google Sheets
-
Combine the values in multiple columns: Unlike Excel’s Combo Box feature, which allows you to transfer real-time search input to another cell, Google Sheets doesn’t have an equivalent option. Instead, we’ll use a data validation feature called “list from a range” to simulate real-time search suggestions.
-
Create a drop-down box with search suggestions: Start by combining the values in the columns that you want to filter. For example, if you’re filtering based on country names in the third column, join the values in the columns using the following formula:
=ArrayFormula(F2:F16&rept(".",10)&G2:G16&rept(".",10)&H2:H16) -
Apply data validation: Next, merge the cells where you want the drop-down box to appear. For example, merge cells A1:C1. Then, go to Data > Data Validation and create a drop-down box using the merged cell. In the “Criteria” section, choose “List from a range” and select the range where you applied the formula in step 2.
-
Filter the rows based on the search suggestion: Now comes the trickiest part. We need to create a filter formula that can dynamically filter the rows based on the value entered in the search field (drop-down). Unfortunately, Google Sheets’ FILTER function can’t directly take wildcards. But don’t worry, we can still achieve the desired result using REGEXMATCH.
Enter the following formula in cell A3:
=iferror({F1:H1;filter(F2:H,REGEXMATCH(H2:H,"^(?i)"&A1))})The above formula matches the search key in cell A1 with the values in column H. If there’s a match, the FILTER formula filters those rows.
And there you have it! A workaround to filter rows as you type in Google Sheets. It might not be as straightforward as it is in Excel, but it’s a great solution nonetheless.
Filter As You Type in Google Sheets – Changing the Filter Column
If you want to change the filter column from H (Source Country) to, let’s say, F (Item Description), it’s a simple modification. Just update the Filter formula in cell A3 by changing the text range from H2:H to F2:F:
=iferror({F1:H1;filter(F2:H,REGEXMATCH(F2:F,"^(?i)"&A1))})I hope this workaround helps you save time and streamline your data filtering process in Google Sheets. Give it a try and see how it simplifies your workflow!
To discover more efficient Google Sheets tips and tricks, don’t forget to check out Crawlan.com. Happy filtering!
