How to build a custom search box in Excel

The addition of a research field to your spreadsheet can considerably improve the navigation and user -friendliness of the data. In this guide, you will learn how to create a dynamic search area that filters data according to your input, using the integrated Excel features and development tools.

To create a research field in a spreadsheet, you must first activate the Developer tools menu. To do that, go to Options> Personalize the ribbon on Deposit tongue. In the Customize the ribbon column, check the box next to Developer tools and select ALL RIGHT.

Then go to your spreadsheet containing your data. Make sure the first line has header data describing the contents of each column. Select all the data and convert it to a smart table by pressing on Ctrl + t.

Confirm the area of ​​the table by clicking ALL RIGHT. Then in the Table design menu, give a name to your table under Table namesuch as Data.

You can now create a search field to filter the data from your spreadsheet. In this example, he will seek the Department column of your data.

IDG

Go to an area next to the table and insert the search field there. Go to Developer tools> Insert> Activex controland select the Text field icon. (Pass on the icons with your mouse to see their names; in our test system, it is the fifth icon of the left in the first line.) Place the text field next to your table.

Right -click on the text field, select Propertiesand under LiendcellEnter the cell behind the research field – for example, B7.

Then click Developer mode in the Developer tools Menu to anchor the new search field. Copy your table header data and stick it under the search field. Then write the following formula in the cell directly below the first header cell:

= Filter (data; data[department]= B7; “Nothing”)

THE FILTERED The function follows this model: Matrix, include, [if_empty].

• Matrix is all the data that the filter must produce.
• Include Specifies the lines to be displayed according to the condition.
• [If_empty] Define what to display if there is no correspondence.

In this example, the table is named Data and defined as the matrix. Research filters the Department column, displaying only the lines where the value corresponds to the input into the cell B7. If no line corresponds, it is displayed “Nothing”.