Dynamic Excel Filter Search Box

Excel’s filters help you narrow down big datasets easily. A dynamic filter search box makes it even better by letting you type a search term and instantly see matching results, like searching on Google or Amazon. This guide walks you through creating a dynamic search box in Excel using VBA, so your data filters automatically as you type, saving time and effort.

Step-by-Step Guide to Create a Dynamic Excel Filter Search Box

Let’s walk through an example where we build a search box to filter a list of courses and their corresponding links.

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.