How to create a filtering search box for your Excel data VBA

Filters are the most commonly used functionalities for filtering out any particular result in a large data set. Dynamic filters searches are used by large companies like Google, Amazon, Youtube, Flipkart, etc. where we just type a single character and it starts showing the recommended result. In this article, we will learn how to create a dynamic excel filter search box with the help of an example. In this example, we will create a dynamic excel filter search box that will search and filters the data based on what is typed in the search box.

Inhaltsverzeichnis Show

Step By Step Implementation of the filter search box
Using the FILTER function
Filter as You Type (Using FILTER Function, No VBA Needed)
Filter as You Type (Using VBA)

Follow the following steps to implement filter search box:

Step 1: First, we will open the Microsoft Excel application, and we will define the following columns Course Name and Course Link. And add the data to it. You can define your own columns and data as per your requirements.

Once you choose your table style, excel will give you a popup where you need to check “My table has the header“.

Step 2: Now, we will create a filter and search output layout. For this select some different cells (here, we will choose D1) and name it GFG Search Filter. We will beautify our filter by formatting it. For this go to Home > Styles > Cell Styles and choose “Good“ formatting style.

Step 3: In this step, we will make our Developer option available in excel toolbar. For this, go to any of the tools(here, we are choosing Draw) and then right-click on it and select “Customize the Ribbon..” option.

The excel will open pop-up options, there we need to check the Developer checkbox and click on OK. This will enable the Developer option and make it available in the excel top toolbar.

Step 4: Now, we will insert a text box for the search filter. For this, we will go to Developer > Insert > Click On Textbox.

We can put our textbox anywhere we want in our excel. Here, we are keeping it below our “GFG Search Filter“ cell.

Step 5: In this step, we will link our text box with a cell(Here, we will link it with cell E1). So that, if we will type anything in our search box it will also get typed in the cell. For this, we will double click on the text box, which will open a new window “Microsoft Visual Basic for Application“. There in the Properties-Textbox tab, we will link it with our E1 cell.

Now, we need to unselect the “Design Mode” in order to check our linked cell is working properly or not.

Step 6: Before moving further we need to change our table name, which we will use in the filter script. For this select any cell of the table and go to the Table Design tab and change the table name. (Here, we are changing it to tbl_data).

Step 7: In this step, we will write the VBA(Visual Basics for Application) scripts. This script will filter the data depending on what is entered in the textbox.

Private Sub TextBox1_Change() Application.ScreenUpdating = False ActiveSheet.ListObjects("tbl_data").Range.AutoFilter Field:=2, Criteria1:= "*" & [E1] & "*", Operator:=xlFilterValues Application.ScreenUpdating = True End Sub

In the above script, we can see in the first line the script is running over the TextBox1. We have used “Application.ScreenUpdating = False” this will hide the searching operation. we have used our table name “tbl_data” and the cell name “E1“. Now, the excel will filter for the data entered in cell E1 from the table tbl_data and once it gets completed we will be updating the result from “Application.ScreenUpdating = True” this will finally show the completed task outcome.

Once we are done with our script we need to save it for this click on the save button from the VBA toolbar.

This will open a new tab asking save as a macro-free workbook. Click on Yes and save it.

Step 8: In this step, we will align the textbox to enhance the design. For this, Go to Developer > Select the Design Mode. After this, we will hold our text box and move it to cell E1 and hide it.

Note: Do not forget to unselect the Design Mode from the Developer option.

Output

Here, we will test our Dynamic Search Filter.

These tutorials will enable you to create a search box in Excel that filters a table as you type. Each letter that you type in the search box will automatically be applied to the filter.

The first tutorial uses the FILTER function which is available in Excel 365. If you don’t have Excel 365, the second tutorial will show you how to achieve the same result using Visual Basic for Applications (VBA).

Both tutorials are in video format with the relevant formulas and code documented with them.

Using the FILTER function

Formula used in video:

=FILTER(SalesReps,LEFT(SalesReps[Sales Rep],LEN(E1))=E1,"No names found")

Download the featured file here.

For more information on the FILTER function and the new functions available in Excel 365 in general, please see our New Dynamic Array functions page.

Using VBA

VBA code featured in the video:

Unfortunately I can’t upload the macro enabled file in WordPress.

Private Sub optBeginsWith_Click() Call txtSearchBox_Change End Sub Private Sub optContains_Click() Call txtSearchBox_Change End Sub Private Sub txtSearchBox_Change() If optBeginsWith Then ListObjects("SalesRepsVBA").Range.AutoFilter Field:=1, Criteria1:=Range("B1") & "*" Else ListObjects("SalesRepsVBA").Range.AutoFilter Field:=1, Criteria1:="*" & Range("B1") & "*" End If End Sub

Excel has some powerful Filter options (with inbuilt filter, advanced filter, and now the FILTER function in Office 365).

But none of these options actually filter as you type (i.e., show you the filtered data dynamically while you’re typing).

Something as shown below:

Although there is no inbuilt filter feature to do this, you can easily create something like this in Excel.

In this Excel tutorial, I will show you two simple ways to filter as you type in Excel.

The first method will be using the FILTER function (which you can access only in Office 365, now called Microsoft 365) and the other method would be by using a simple VBA code.

So let’s get started!

Filter as You Type (Using FILTER Function, No VBA Needed)

Suppose you have a dataset as shown below and you want to quickly filter data based on the region as you’re typing in a search box (which we will insert in the worksheet).

The first step is to insert a text box where you can type a text string and it will use it to filter the data (while you’re typing).

Below are the steps to insert the text box:

Click the Developer tab.
In the Control group, click on Insert.
Click on the Text Box icon in the ActiveX Controls
Place the cursor anywhere in the worksheet, click and drag. This will insert a text box in the worksheet. You can place this text box wherever you want and can also resize it.

In case you don’t see the Developer tab in the ribbon (in Step 1), right-click on any of the tabs and click on ‘Customize the Ribbon’. In the Excel Options dialog box that opens, check the Developer option in the right pane and click OK. This will make the Developer tab visible in the ribbon.

Now that we have the text box in the worksheet, the next step is to connect it to a cell in the worksheet so that when you type anything in the worksheet, it will also automatically be entered in a cell.

This will allow us to use the value in the cell to filter the data.

Below are the steps to link the text box to a cell:

Double-click on the text box. This will open the VB Editor.
Click the View option in the menu and then click on Properties Window. This will show the Properties Window Pane for the text box.
In the properties window, come to the Linked Cell option and enter F1. This is the cell that we are connecting to the text box
Close the VB Editor
Go to the Developer tab, and click on the Design mode. You will notice that it turns from dark gray to light gray (indicating that it’s not enabled now).

Now, when you enter any text in the text box, you will notice that it appears in cell E1 in real-time (as you’re typing)

Now that we have linked the text box to a cell, the last step is to filter the data based on the value in the text box (which in turn would be the value in cell E1)

For this, we need to enter the FILTER formula in cell E4, so that results are filtered and shown there.

Below is the formula that will now filter the results as soon you can enter anything in the text box:

=FILTER(A4:C13,ISNUMBER(SEARCH(E1,B4:B13)))

The above formula uses the FILTER function with the array as the original dataset and the condition uses the SEARCH formula.

The SEARCH formula checks whether the value entered in the text box (which also automatically gets entered in cell E1) is there in the cells in column B or not. All the cells that have the text will return a number and those that don’t will return the #VALUE! error.

The ISNUMBER function is used to get TRUE if there is a match and the cell returns a number, and FALSE is it returns the error.

Based on this condition, the data is filtered as you type.

Note that this formula checks whether the text string entered in the text box appears in the cells in column B or not. For example, if you enter ‘a’ in the text box, it would return all the records for Canada, Asia, and Brazil.

The position of the text string in the cells in column B is not checked.

In case you want to have the text string (that you enter in the search box) at the beginning only, you can use the below formula instead:

=FILTER(A4:C13,LEFT(B4:B13,LEN(E1))=E1)

Now when you enter A in the search box, it will only give you records for Asia.

Filter as You Type (Using VBA)

If you’re not using Office 365 and don’t have access to the FILTER function, you can still create the ‘filter as you type’ search box in Excel.

This can be done by using a really simple VBA code mentioned below:

Private Sub TextBox1_Change() Application.ScreenUpdating = False ActiveSheet.ListObjects("Data").Range.AutoFilter Field:=2, Criteria1:= "*" & [A1] & "*", Operator:=xlFilterValues Application.ScreenUpdating = True End Sub

To use this code, you will have to first insert the text box in the worksheet and then add this code for the text box.

But the first step is to convert the data into an Excel table. While you can use this code without converting the data into an Excel table, it will be easier when the data is in Table as it becomes easier to refer to in the VBA code.

Below are the steps to convert the data into an Excel table:

Select any cell in the dataset
Hold the Control key and press the T key (or Command + T if you’re using a Mac).
In the Create Table dialog box that opens, check whether the range is correct or not.
Click Ok
Select any cell in the Excel Table
Click on the ‘Table Design’ tab
Change the name of the table to Data. You can use any name you want, but make sure to use the same one in the VBA code as well.

Below are the steps to insert the text box in the worksheet:

Click the Developer tab.
In the Control group, click on Insert.
Click on the Text Box icon in the ActiveX Controls
Place the cursor anywhere in the worksheet, click and drag. This will insert a text box in the worksheet. You can place this text box wherever you want and can also resize it.

Now that you have the text box in the sheet, you need to connect to a cell and then add the VBA code to the text box code window.

Below are the steps to do this:

Double-click on the text box. This will open the VB Editor.
Click the View option in the menu and then click on Properties Window. This will show the Properties Window Pane for the text box.
In the properties window, come to the Linked Cell option and enter F1. This is the cell that we are connecting to the text box
In the code window of the Text Box, copy and paste the above VBA code
Close the VB Editor
Go to the Developer tab, and click on the Design mode. You will notice that it turns from dark gray to light gray (indicating that it’s not enabled now).

Now you have a text box that is linked to a cell and this cell is used in the VBA code to filter the data.

When you enter any text in the text box, you will see that it filters the table in real tile (filter the data as you type in the text box).

Note: Since the VBA code is executed every time you enter a character in the text box, this method could make your workbook slightly slow in case you have a large data set. In such a case, instead of using the code in the text box code window, you can use it in a regular module and then assign it to a button. That way, you can first type the text in the text box and then click on the button to filter the data.

So these are two methods you can use to create a dynamic filter in Excel (filter data as you type).

Hope you found this tutorial useful!

You may also like the following Excel tutorials: