Excel has rich resources on filtering as expected from a spreadsheet software. However, the functionality may lack of fanciness in a business software, like filtering on-the-fly as users are typing. Fortunately, Excel has enough customization tools to add such a feature easily. In this guide, we're going to show you How to filter as you type without VBA in Excel.
Formula part
The no-VBA approach relies on the FILTER function which is the only dynamic filtering option available while we are writing this post except the filtering tools you can find under the Data tab of the toolbar.
Note that the FILTER function is available for Microsoft 365 subscribers and one-time purchase versions starting from 2019.
If you do not have access to the FILTER function, continue with the VBA approach that requires a simple code to copy and paste.
The FILTER function can populate filtered data from an array or range based on the criteria you define.
=FILTER( array, include, [if_empty] )
The criteria (include) should be an array of TRUE/FALSE values. To provide such a criteria array, we can use a combination of ISNUMBER and SEARCH functions.
The SEARCH function returns the index of a searched string in a given text when the given text includes the string. Otherwise, the function returns #VALUE! Error.
On the other hand, the ISNUMBER function returns TRUE if the parameter is a number. If not, FALSE.
Thus, wrapping the SEARCH function with the ISNUMBER generates the criteria array we need. Excel filters out the FALSE (not including the searched string) values.
=FILTER( array, ISNUMBER( SEARCH( search string, array ) ) ) )
Obviously, the formula alone doesn't provide "filter as you type without VBA" functionality. The next step will.
Textbox part
Once the structure is ready, we need to add a text box control. You can find the text box control in Developer > Controls > Insert > ActiveX Controls.
If you do not see the Developer tab in the Ribbon, you can add it from Customize Ribbon section in the Excel Options. Check out How to display the Excel Developer tab for using advanced features article to see the full walkthrough.
You can place the textbox anywhere you want. But ideally, place it on top of the cell where the formula gets the search text. So, you can hide the cell and avoid possible manipulations on it.
Next, right-click on the textbox and click Properties.
Type the search cell's address into the Linked Cell box in the Properties dialog.
Once typed, you can close the dialog. Click the Design Mode button in the Developer tab to turn it off and you are good to go.
Tip: Whenever you want to make modifications on the textbox, click on Design Mode toggle first.
Bonus: Textbox Appearance
You can alter your textbox appearance by changing its properties. For example, you can completely blend the textbox by choosing flat effect, transparent background, and a border.
Tip: Select Categorized in the Properties dialog to see the properties by category.
