Dynamic Excel Filter Search Box
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.
Step By Step Implementation of the filter search box
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.
Here, we will test our Dynamic Search Filter.