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.
Just so you know
Sign up for a free Jotform account to create powerful online forms in minutes.
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:
- Convert your data range into a table.
- Add filtering controls.
- 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:
- Click anywhere in your data range.
- Click the Insert tab and then click Table.
- Confirm the range and check (or uncheck) the checkbox, depending on whether your table has headers.
- 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:
- 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.
- Save the spreadsheet as a macro-enabled workbook.
- Click the Developer tab.
- Click Insert and then click Text Box. Draw the text box in a cell above your table.
- Right-click the text box and then click Properties.
- Enter the cell number where you drew the box in the LinkedCell field, and then close the window. (We used “A3” for this walkthrough.)
- 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.
|‘Filter Name field in MyTable Table. |
Dim strFilter As String
strFilter = “*” & [A3] & “*”
- Save the module and close the window.
- Click Insert and then click Command Button. Draw the button to the right of the text box.
- Right-click the button and then click Properties.
- Enter “Clear” in the Caption field, and then close the window.
- Double-click the Clear button to open its module and then enter the below macro text in the blank space.
|‘Clear MyTable custom filter.|
[A3] = “”
- Save the module and then close the window.
- Deselect Design Mode by clicking it. This will enable you to use the controls you’ve created.
Similar to Microsoft Excel, Jotform Tables allow its users to filter the data according to various criteria, including the date interval and a particular value in a column.
3. Test the filtered search box
Now that you’ve created your controls, it’s time to test their functionality:
- Enter any text in the search box. The table’s rows should filter as you type.
- To ensure the filter operates correctly, enter the full word(s) in the search box for the record you want to find.
- 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.
I was able to get this to work, but I did have to re-type the code (copying and pasting didn't work). Also, for this line of code Field:=1, _, the number is the column you want to filter. I needed to filter my second column, so changed the code to Field:=2,_. All is working well now.
I had to make changes for this to work -- the copy & paste didn't run it successfully right away.
I had to change the back quote (`) to an apostrophe ('). The 1st line in each code is supposed to be a comment, not a running code.
For some reason, typing the code out ultimately caused it to run successfully, despite no change in code. I'd suggest giving that a try for those that are getting stuck. (I was getting the same "Run-time error 9 Subscript out of range" error others were getting)
My only issue is it doesn't clear filters
This doesn't work! Nice try though. Hopefully some of you made your way down to the comments before wasting your time setting this up how they did.
It did not work for me, first it show the error 9 and now it is Syntx error on 'Filter Name field in SearchTest Table
The macro text below worked for me! Hopefully, it would be useful to you too. As above, you'll need to replace the text corresponding to your Table Name and Linked Cell.
ListObjects("TableName").Range.AutoFilter Field:=1, Criteria1:="*" & Range("LinkedCell#") & "*"
Same here, the answer didn't help.
Ive got the same error as Tiago, Run-time error '9':
Subscript out of range..
Any thing that will help please?
I managed to follow this instructions, but when trying to use the search box I get the following error:
Run-time error '9':
Subscript out of range.
I can't find the issue.
Thanks in advance