Creating a Search Box in Excel
The idea of creating a search box in excel, so that we keep writing the required data and accordingly it will filter the data and show only that much of data. In this article, we will show you how to create a search box and filter the data in excel.
15 Easy Steps to Create Dynamic Search Box in Excel
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 create it on your own.
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 a name as “CityList.”
- Step 3: Go to the Developer tab in excelDeveloper Tab In ExcelEnabling 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., and from the insert, box inserts “Combo Box.”
- Step 4: Draw this “Combo” box on your worksheet where the data is in.
- Step 5: Right-click on this “Combo Box” and choose the “Properties” option.
- Step 6: This will open up properties options like the below one.
- Step 7: We have several properties here. For the property, “Linked Cell” gives a link to the cell D2.
- Step 8: For the “List Fill Range,” property gives the name given to a unique list of “Cities.”
- Step 9: For the “Match Entry” property, choose 2-fmMatchEntryNone because 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 “Combo Box.” 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 drop-down list in excel.
In fact, we can type the name inside the combo box, and the same will reflect inlined cell D2 as well.
- Step 12: Now, we need to write formulasWrite FormulasWrite 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. to filter the data as we type the city name in the combo box. For this, we need to have three helper columns. For the first helper column, we need to find the row numbers by using the ROWS functionUsing The ROWS FunctionThe ROWS function in Excel returns the number of rows selected in the range. It's not the same as the ROW function. .
- Step 13: In the second helper column, we need to find the related searches city names, and if they match, we need the row numbers of those cities for this to enter the below formula.
This formula will look for the city name in the main table. If matches, it will return the row number from the “Helper 1” column or else will return an empty cell.
For example, now I will type “Los Angeles,” and wherever the city name is there 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 is available, we need to stick together these row numbers one under the other, so in the third helper column, we need to stack all these row numbers of the entered city name.
This formula will look for the smallest value in the matched city list based on actual row numbers, and 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, and if the error value comes, it will return an empty cell as a result.
- Step 15: Now, create an identical table format like the below one.
In this new table, we need to filter the data based on the city name we type in the excel search box. This can be done by using a combination of IFERROR, INDEX, and COLUMNS functions in excelCOLUMNS Functions In ExcelCOLUMNS function falls under the category of Lookup Functions in Excel. It returns the total number of columns in the given array or collection of references. . Below is the formula you need to apply.
Copy the formula and paste it to all the other cells in the new table.
Ok, we are done with the designing part. Let’s learn how to use it.
Type the city name in the combo box, and our new table will filter the entered city data only.
As you can see I just typed just “LO” and all the related search result is filtered in the new table format.
Things to Remember here
- You need to insert a combo box in excelCombo Box In ExcelCombo Box in Excel is a type of data validation tool that can create a dropdown list for the user to select from the pre-determined list. It is a form control which is available in the insert tab of the developer’s tab. from “ActiveX Form Control” under the “Developer” tab.
- Combo box matches all the related alphabets returns the result.
This has been a guide to the search box in excel. Here we discuss how to create a dynamic search box in excel along with an example and downloadable excel templates. You may also look at these useful functions in excel –