Adding a search field to your spreadsheet can greatly improve data navigation and usability. In this guide, you’ll learn how to create a dynamic search box that filters data based on your input, using Excel’s built-in features and Developer tools.
To create a search field in a spreadsheet, you must first activate the Developer tools menu. To do this, go to Options > Customize Ribbon on the File tab. In the Customize Ribbon column, check the box next to Developer tools and select OK.
Next, switch to your spreadsheet containing your data. Make sure the first row has header data describing the content of each column. Select all the data and convert it into a smart table by pressing Ctrl + T.
Confirm the table area by clicking OK. Then, in the Table Design menu, give your table a name under Table Name, such as Data.
Now you can create a search field to filter the data in your worksheet. In this example, it will search the Department column of your data.
IDG
Switch to an area next to the table and insert the search field there. Go to Developer tools > Insert > ActiveX Control, and select the Text Field icon. (Hover over the icons with your mouse to see their names; in our test system, it’s the fifth icon from the left in the first row.) Place the text field next to your table.
Right-click the text field, select Properties, and under LinkedCell, enter the cell behind the search field — for example, B7.
Next, click Developer Mode in the Developer tools menu to anchor the new search field. Copy the header data from your table and paste it below the search field. Then, write the following formula in the cell directly below the first header cell:
=FILTER(data;data[department]=B7; “Nothing”)
The FILTER function follows this pattern: Matrix, include, [if_empty].
- Matrix is all the data the filter should output.
- Include specifies which rows to show based on the condition.
- [If_empty] defines what to display if there are no matches.
In this example, the table is named Data and defined as the matrix. The search filters the Department column, showing only rows where the value matches the input in cell B7. If no rows match, it displays “Nothing”.
This article originally appeared on our sister publication PC-WELT and was translated and localized from German.
