How to Create a Filtering Search Box for Your Excel Data (5 Ways)

When there is lots of data then if you have a filtering search box for your Excel data then it will be very helpful for you to find any data within a minute. Thus, I will demonstrate how to create a filtering search box for your Excel data, in this article.

Here, I will describe 5 suitable methods to create a filtering search box for your Excel data. In addition, for your better understanding, I’m going to use the following dataset. Which contains 3 columns. They are Customer Name, Region and Product.

1. Use of Data Validation to Create a Filtering Search Box for Excel Data

You can use the Data Validation feature to create a filtering search box for your Excel data. The steps are given below.

Steps:

Firstly, you need to select a cell that you want to use as a filtering search box. Here, I have selected the C15 cell.
Secondly, from the Data tab >> go to the Data Tools feature >> then choose the Data Validation command >> after that select the Data Validation option.

At this time a dialog box named Data Validation will appear.

Firstly, choose the List option from the drop-down arrow in Allow section.
Secondly, in the Source box, select the data range depending on which Filtering will work. Here, I have selected the data range of the Region column.
Thirdly, press OK.

Now, you will see the filtering search box by clicking on the drop-down arrow of the C15 cell.

At this time, from the Home tab >> go to the Conditional Formatting command >> then choose the Highlight Cells Rules feature >> after that select the Text that Contains… option.

At this time, a dialog box named Text That Contains will appear.

Now, select the C15 cell in the Format cells that contain the text: box. Here, you must select the cell where you have applied the Data Validation feature not the cell value.
Then, choose the highlight color. Here, you should try to choose any light Fill color and dark Text color.
Finally, press OK.

After that, select the C15 cell.
Then, from the Home tab >> go to the Conditional Formatting command >> then select Manage Rules option.

At this time, a dialog box named Conditional Formatting Rules Manager will appear.

Now, in the Applies to box select the whole data range. Here, I have selected the range C5:C13.
Then, press OK to get the changes.

Finally, you can see the results below. Here, I have created a filtering search box. As you can see, from that box, when I choose the Ontario region then all the cells in the Region column that have the same cell values are marked.

Similarly, I tried for another Region named Alberta and you can see the result below.

2. Combining Excel Functions to Create Filtering Search Box

You can apply a combination of some functions to create a filtering search box for your Excel data. Here, I will use FILTER, LEFT, and LEN functions. In the case of the FILTER function, you will get this function only in the Excel 365 version. The steps are given below.

Steps:

Firstly, you have to make a table. So, select the data range.
Secondly, from the Insert tab >> choose the Table feature.

At this time, a dialog box named Create Table will appear.

Now, make sure that you have selected the data range in the Where is the data for your table? box. Here, if you select the data range before then this box will AutoFill up.
Then, check the My table has headers option.
Finally, press OK.

Finally, your table is ready.

Now, select any cell of the table.
Then, from the Table Design tab >> go to the Properties option.
After that, write down a table name in the Table Name box. Here, I have written Salesinfo as my table name.

Now, select a different cell where there should be enough space to keep your whole data.
Then write down the following formula in that cell. Here, I’m going to use the E2 cell.

=FILTER(Salesinfo,LEFT(Salesinfo[Region],LEN(F2))=F2,"No Region Found")

Formula Breakdown

Here, F2 cell will be the search box.
Firstly, the LEN function will return the total number of characters in the F2 cell. So, whatever you will write in the F2 cell the LEN function will count that.
Secondly, the LEFT function will return the characters from the start of a given text.
Salesinfo[Region] denotes the Region column from the Salesinfo table.
Thirdly, the FILTER function will filter the data. Here, the array is a table named Salesinfo. It will search for the letters written in F2 cell and if there are no such letters then it will return No Region Found.

Subsequently, press ENTER to get the result.

Now, you can see that if I write just On then it will give all the Region whose name is starting with O,n letters.

3. Applying Combo Box to Create a Dynamic Filtering Search Box in Excel

You can employ the Combo Box feature to create a dynamic filtering search box for your Excel data. This is actually a large process. So, I have divided this method into two segments. The steps are given below.

Step 1: Make List of Unique Regions for Filtering Search Box

Firstly, copy the data range based on which the filtering search box will work. Here, I have copied the data from the Region column of the previous data set.
Secondly, go to a new worksheet and paste that there. Here, my new worksheet name is Unique Region and I have named the column head as Unique Region. In addition, you can use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste respectively.

Now, you need to select the data range of the Unique Region column.
Then, from the Data tab >> go to the Data Tools feature >> then choose the Remove Duplicates option.

At this time, a dialog box named Remove Duplicates will appear.

Firstly, make sure that you check the Unique Region.
Secondly, press OK.

After that, press OK to the window named Microsoft Excel.

Finally, all the uniquely named regions are here.

Now, select the data of unique regions.
Then, from the Formulas tab >> go to the Defined Names command.
After that, from the Define Name feature >> choose the Define Name… option.

At this time, a dialog box named New Name will appear.

Firstly, write down RegionList in the Name: box.
Secondly, press OK to keep the changes.

So, you have defined the name of the Unique Region data as RegionList.

Step 2: Inserting Combo Box to Create a Dynamic Filtering Search Box

Now, go back to the previous worksheet named Combo Box.
Then, from the Developer tab >> select the Insert feature >> then insert a Combo Box (ActiveX Controls). Here, you have to click on the Combo Box (ActiveX Controls) option and then drag the Mouse Pointer at a certain place to insert the Combo Box.

Here, I have inserted the Combo Box.

Now, Right-Click on the Combo Box.
Then, from the Context Menu Bar >> go to the Properties option.

Here, you have to do three things in the Properties window.

Firstly, write down an empty cell like D15 in the LinkedCell box.
Secondly, write down RegionList in the ListFillRange box.
Thirdly, select 2- fmMatchEntryNone in the MatchEntry box.
Finally, you should close this window.

Now, from the Developer tab >> uncheck the Design Mode feature.
Then, if you click on the drop-down arrow of the Combo Box you will see the list of unique regions. Furthermore, if you select any region from that list you will get that region in the D15 cell too. Which means linking of the cells is working.

Now, select a different cell.
Then write down the following formula in that cell. Here, I’m going to use the D5 cell.

=ROW(D1)

In this formula, the ROW function will return the row number of a cell.

Subsequently, press ENTER to get the result.

Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.

Now, select another different cell.
Then write down the following formula in that cell. Here, I’m going to use the E5 cell.

=IF(ISNUMBER(SEARCH($D$15,$C5)),D5,"")

Subsequently, press ENTER to get the result.

Formula Breakdown

Firstly, the SEARCH function will return the found value from a text or string.
SEARCH($D$15,$C5)—> will search for text in the $D$15 cell within the $C5 array. Here, $C5 denotes a fixed column only.
Secondly, the ISNUMBER function will return TRUE if the value is a number otherwise returns FALSE.
Thirdly, the IF function will return the D5 cell value if the previous output is TRUE else return blank space.

As you can see, when I selected Manitoba from the Combo Box the D15 cell value would be Manitoba too. Then, it will return the D column values in the E column where there is Manitoba.

Now, select another different cell.
Then write down the following formula in that cell. Here, I’m going to use the F5 cell.

=IFERROR(SMALL($E$5:$E$13,ROWS($E$5:E5))," ")

Subsequently, press ENTER to get the result.

Formula Breakdown

Firstly, the ROWS function will count the number of the total rows for a given array.
- ROWS($E$5:E5)—> returns 1.
Secondly, the SMALL function will return the k-th smallest value from a given array.
- SMALL($E$5:$E$13,1)—> becomes 1.

Thirdly, the IFERROR function will check whether the value is an error or not. If the value is an error then it will give a blank space.
- IFERROR(1,” “)—> turns 1.

As you can see, when I selected Manitoba from the Combo Box the D15 cell value would be Manitoba too. Then, it will sort the data in the F column based on the D column and the E column where there is Manitoba.

Now, select another different cell.
Then write down the following formula in that cell. Here, I’m going to use the B17 cell.

=IFERROR(INDEX($B$5:$C$13,$F5,COLUMNS($B$17:B17)),"")

Subsequently, press ENTER to get the result.

Formula Breakdown

Firstly, the COLUMNS function will return the total number of columns of a given array.
- COLUMNS($B$17:B17)—> turns 1.
Secondly, the INDEX function will return a searched value. Here, $B$5:$C$13 is the array and $F5 is the row number.
- INDEX($B$5:$C$13,$F5,1)—> becomes Rose.
Thirdly, the IFERROR function will check whether the value is an error or not. If the value is an error then it will give a blank space.

Now, drag the mouse pointer up to C17 cell.

After that, drag the mouse pointer up to the B18 cell. Here, you can not use the mouse pointer at once to apply the formula in the rest of the cell. You have to do this cell by cell.

Lastly, from the Home tab >> make the font color White to the cell D15. This is because I don’t want to see this additional cell.

Now, if you select any value from the Combo Box then you will get this value along with the related cell values in the below.

4. Creating a Filtering Search Box for Excel Data with Filter Icon

You can use the Filter feature to create a filtering search box for your dataset. So, follow the steps given below.

Steps:

First, select the full range of cells of the dataset.
Then, go to the Data tab in the top ribbon.
Now, click on the Sort & Filter options and select the Filter Feature.

As a result, you will see the dataset will turn into a table and there will be a Filter arrow icon in the Header cells.

Now, click on the Filter icon in the Product header cell.
After clicking, you will see some options will open.
Here, you can mark or unmark items to filter and you will show the filtered dataset.
So, mark the items that you want to show and unmark the items that you want to hide in the list.
Finally, press OK.

As a result, you will see only the marked items in the dataset. And, the unmarked item rows have become hidden here. Thus, you can use the Filter feature to search specific item.

5. Use of Text Box Feature

You can use the Text Box feature to create a filtering search box for your data. So, you can insert the value in the Text Box and you will get the filtered dataset. For that purpose follow the steps below.

Steps:

First, you have to make a table for your data. To make the table, you can follow the first portion of method 2.
After that, select the header cells of the data table.
Then, you will find a tab named Table Design in the top ribbon >> you should go to this tab.
Now, select the Properties menu and insert Table Name as Sales here.

Now, go to the Developer tab >> click on the Insert option.
From the lists, select the Text Box option in the ActiveX Controls section.

Now, you have to draw a Text Box by dragging the mouse pointer in a suitable position as of your need.

After creating the Text Box, right-click on it to open the Context Menu Bar.
Then, from the Context Menu Bar >> select the Properties option here.

Subsequently, a window named Properties will appear.

Now, find the LinkedCell box option and insert C15 in the box.
Then, you should close this window.

At this time, you should double-click on the Text Box to open the VBA section.
Then, write down the following code in between Private Sub and End Sub.

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

Code Breakdown

Here, Sales is the array or data table.
AutoFilter Field:=3 —> denotes based on which cell filter will be working. Here, I have used Field 3 for filtering which is the 3rd column of your given table. Thus, Field 3 denotes the Product column of the table.
Criteria1:=[C15] —> denotes that whatever you search that value you will see in the C15 cell.

Now, you should minimize the VBA code.
Then, select the Text Box.
After that, from the Developer tab >> uncheck the Design Mode option.

As you can see, when I have written P in the Text Box then the C15 cell value would be P too. Then, it will filter the data for the Product whose name starts with P. Furthermore, in the case of no matched product, it will remain blank.

After that, from the Developer tab >> select the Design Mode option.
Again, select the Text Box. And, drag this on the C15 cell. This is because I don’t want to see this additional cell.

Finally, you can see, that when I have written W in the Text Box then it will filter the corresponding data for the Product whose name starts with W.

How to Use Conditional Formatting to Create Filtering Search Box in Excel

You can use Conditional Formatting to create a filtering Search Box for your Excel data. The steps are given below.

Steps:

Firstly, select the cell where you want to keep the filtering search box. Here, don’t select any cell below your Excel data. If you do so, then the formula might not work. Thus, I have selected the G6 cell.
Secondly, from the Home tab >> you must go to the Conditional Formatting command.
Thirdly, you need to choose the New Rule option to apply the formula.

At this time, a dialog box named New Formatting Rule will appear.

Now, from that dialog box >> you have to select Use a formula to determine which cells to format.
Then, you need to write down the following formula in the Format values where this formula is true: box.

=IF(AND(ISBLANK($G$6),ISBLANK($G$7)),0,AND(SEARCH($G$6,$C5),SEARCH($G$7,$D5)))

After that, go to the Format menu.

Formula Breakdown

Here, the ISBLANK function will check whether the cell is empty or not.
Then, the AND function will join multiple logics.
Then, the IF function will test whether both the cells G6 and G7 are empty or not. If they are empty then it will return 0 otherwise the SEARCH function will work. Here, the Dollar($) sign denotes the fixed cells.

At this time, a dialog box named Format Cells will appear.

Now, from the Fill option >> you have to choose any of the colors. Here, I have chosen Orange, Accent 2, Lighter 60%. In this case, try to choose any light color. Because the dark color may hide the inputted data. Then, you may need to change the Font Color again.
Then, you must press OK to apply the formation.

After that, you have to press OK on the New Formatting Rule dialog box. Here, you can see the sample instantly in the Preview box.

Now, from the Home tab >> you need to go to the Conditional Formatting command.
Then, you have to choose the Manage Rules option.

At this time, a new window named Conditional Formatting Rules Manager will appear.

Now, in the Applies to box select the whole data range. Here, I have selected the range B5:D13.
Then, press OK to get the changes.

Now, if you write down any region in the Region box, you will get all the corresponding data as highlighted.

Furthermore, if you also specify the Product name then it will highlight the cells according to both that particular region and product.

💬 Things to Remember

Here, method 1(Data Validation), method 3 (Combo Box), and method 4(Filter Feature) both have a drop-down arrow as a filtering search box. So, you don’t need to write anything to get filtered data. Simply, you just can choose your wanted name or ID for the corresponding data.
In the case of method 2(Combined Functions) and method 5(Text Box) you can get the filtered data by typing just a letter.

Download Practice Workbook

You can download the practice workbook from here:

Creating Filter Search Box.xlsm

Conclusion

I hope you found this article helpful. Here, I have explained 5 suitable methods to create a filtering search box for your Excel Data. Please, drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Search Box in Excel | Data Management in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Create a Filtering Search Box for Your Excel Data (5 Ways)

1. Use of Data Validation to Create a Filtering Search Box for Excel Data

2. Combining Excel Functions to Create Filtering Search Box

3. Applying Combo Box to Create a Dynamic Filtering Search Box in Excel

4. Creating a Filtering Search Box for Excel Data with Filter Icon

5. Use of Text Box Feature

How to Use Conditional Formatting to Create Filtering Search Box in Excel

💬 Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply