Search Box in Excel

The idea of creating a search box in Excel is that we keep writing the required data. Accordingly, it will filter the data and show only that much data. This article will show you how to create a search box and filter the data in Excel.

Creating a Search Box in Excel

15 Easy Steps to Create Dynamic Search Box in Excel

You can download this Search Box Excel Template here – Search Box Excel Template

To create a dynamic search box in Excel. We are going to use the below data. You can download the workbook and follow along with us to make it yourself.

Follow the below steps to create a dynamic search box in Excel.

Step 1: First, create a unique list of “City” names by removing duplicates in a new worksheet.

Step 2: For this unique list of cities, give the name “CityList.”

Step 3: Go to the “Developer” tab in excelDeveloper Tab In Excel Enabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more. From the “Insert” box inserts “Combo Box.”

Step 4: Draw this “ComboBox” on the worksheet where the data is.

Step 5: Right-click on this “ComboBox” and choose the “Properties” option.

Step 6: This will open up the “Properties” options like the below one.

Step 7: We have several properties here. For the property, “LinkedCell” gives a link to cell D2.

Step 8: For the “ListFillRange,” property gives the name given to a unique list of “Cities.”

Step 9: For the “Match Entry” property, choose “2-fmMatchEntryNone” as you type the name in the combo box. It will not auto-complete the sentence.

Step 10: We are done with the properties part of “ComboBox.” Go to the “Developer” tab and unselect the “Design” mode option of “Combo Box.”

Step 11: Now, from the combo box, we can see city names in the dropdown list in excelDrop-down List In Excel A drop-down list in excel is a pre-defined list of inputs that allows users to select an option.read more.

We can type the name inside the combo box, which will also reflect inlined cell D2.

Step 12: We need to write formulasWrite Formulas Write Formula in excel refers to different formulas written in MS excel to imply a function or calculation to the selected cell or range of cells, rows and columns. There are hundreds of inbuilt formula like AVERAGE, SUM and a proper way of writing it to apply it.read more to filter the data by typing the city name in the combo box. For this, we must have three helper columns. For the first helper column, we need to find the row numbers by using the ROWS functionUsing The ROWS Function The ROWS function in Excel returns the number of rows selected in the range. It's not the same as the ROW function. read more.

Step 13: In the “Helper 2” column, we need to find the related search city names. If they match, we need the row numbers of those cities for this to insert the below formula.

This formula will look for the city name in the main table. It will return the row number from the “Helper 1” column if it matches. Else, it will return an empty cell.

For example, now we will type “Los Angeles,” and wherever the city name is in the main table for those cities, we will get the row number.

Step 14: Once the row numbers of the entered or selected city name are available, we need to stick together these row numbers under the other. So, in the third helper column, we must stack all the entered city names row numbers.

To get these row numbers together, we will use the combination formula of the “IFERROR in Excel” and “SMALL“ functions in Excel.

This formula will look for the smallest value in the matched city list based on actual row numbers. It will stack the first smallest, second smallest, third smallest, and so on. Once all the small values are stacked together, the SMALL function throws an error value. So, to avoid this, we have used the IFERROR function. If the error value comes, it will return an empty cell.