How to create a filtering search box for your Excel data

If you want to quickly calculate how much revenue your service-based business made this month, you can easily whip out your smartphone calculator. But if you need to calculate, sort, and filter a lot of data, spreadsheets are your best bet. Filtering is specifically useful for narrowing down a large data set into manageable data points you can use to answer important questions about customers, employees, products, and more.

One of the most widely used spreadsheet solutions is Microsoft Excel. If you want to filter data in this program, you could use a basic filter that allows you to show and hide data. But you can do so much more with a filtering search box — which will show you data based on what you type.

Below we review the steps to create a filtering search box for your Excel data.

How to create a filtering search box for your Excel data

Here’s an overview of the main steps you need to take to create your search box:

1. Convert your data range into a table.
2. Add filtering controls.
3. Test the filtered search.

The instructions below are based on Excel for Microsoft 365, so the dialog and button names you see may vary.

1. Convert your data range into a table

First, turn your raw rows of data into a table object by following these steps:

1. Click anywhere in your data range.
2. Click the Insert tab and then click Table.
3. Confirm the range and check (or uncheck) the checkbox, depending on whether your table has headers.
4. Change the name of the table in the Table Design tab to something meaningful. (We used the name “MyTable” for the purpose of this walkthrough.)

2. Add filtering controls

After you create a table from your data, here’s how to create your filtering search box to filter based on data in the first column:

1. Ensure you have at least six empty rows available for the controls. You can place your filtering controls above your table (as we did) or in another worksheet entirely.
2. Save the spreadsheet as a macro-enabled workbook.
3. Click the Developer tab.
4. Click Insert and then click Text Box. Draw the text box in a cell above your table.
5. Right-click the text box and then click Properties.
6. Enter the cell number where you drew the box in the LinkedCell field, and then close the window. (We used “A3” for this walkthrough.)
7. Double-click the text box to open Visual Basic module. In the blank space between the “Private Sub” and “End Sub” strings, enter the macro text below — be sure to replace any references to “MyTable” and “A3” with your table name and text box cell, respectively.

8. Save the module and close the window.
9. Click Insert and then click Command Button. Draw the button to the right of the text box.
10. Right-click the button and then click Properties.
11. Enter “Clear” in the Caption field, and then close the window.
12. Double-click the Clear button to open its module and then enter the below macro text in the blank space.

13. Save the module and then close the window.
14. Deselect Design Mode by clicking it. This will enable you to use the controls you’ve created.

3. Test the filtered search box

Now that you’ve created your controls, it’s time to test their functionality:

1. Enter any text in the search box. The table’s rows should filter as you type.
2. To ensure the filter operates correctly, enter the full word(s) in the search box for the record you want to find.
3. Clear the search box by clicking the Clear button.

If you experience any issues with your filtering search button, review the above steps to see if you missed anything. A common mistake is incorrectly entering the macro text for the text box and command button. Ensure each text string is an exact match for your chosen table name and text box cell.