Dynamic Excel Filter Search Box – Extract Data as you Type

There is a dual functionality to this – you can select a country’s name from the drop-down list, or you can manually enter the data in the search box, and it will show you all the matching records. For example, when you type “I” it gives you all the country names with the alphabet I in it.

Creating a Dynamic Excel Filter Search Box

This Dynamic Excel filter can be created in 3 steps:

1. Getting a unique list of items (countries in this case). This would be used in creating the drop down.
2. Creating the search box. Here I have used a Combo Box (ActiveX Control).
3. Setting the Data. Here I would use three helper columns with formulas to extract the matching data.

Here is how the raw data looks:

USEFUL TIP: It is almost always a good idea to convert your data into an Excel Table. You can do this by selecting any cell in the dataset and using the keyboard shortcut Control + T.

Step 1 – Getting a unique list of items

1. Select all the Countries and paste it into a new worksheet.
2. Select the country list –> Go to Data –> Remove Duplicates.
3. In the Remove Duplicates dialogue box, select the column in which you have the list and click Ok. This will remove duplicates and give you a unique list as shown below:
4. One additional step is to create a named range for this unique list. To do this:
   • Go to Formula Tab –> Define Name
   • In Define Name Dialogue Box:
     • Name: CountryList
     • Scope: Workbook
     • Refers to: =UniqueList!$A$2:$A$9 (I have the list in a separate tab named UniqueList in A2:A9. You can refer to wherever your unique list resides)

NOTE: If you use ‘Remove Duplicates’ method and you expand your data to add more records and new countries, you will have to repeat this step again. Alternately, you can also you a formula to make this process dynamic.

See Also: How to use a formula to get a list of Unique items.

Step 2 – Creating The Dynamic Excel Filter Search Box

For this technique to work, we would need to create a ‘Search Box’ and link it to a cell.

We can use the Combo Box in Excel to create this search box filter. This way, whenever you enter anything in the Combo Box, it would also be reflected in a cell in real-time (as shown below).

Here are the steps to do this:

1. Go to Developer Tab –> Controls –> Insert –> ActiveX Controls –> Combo Box (ActiveX Controls).
   • If you do not have the Developer Tab visible, here are the steps to enable it.
2. Click anywhere on the worksheet. It will insert the Combo Box.
3. Right-click on Combo Box and select Properties.
4. In Properties window, make the following changes:
   • Linked Cell: K2 (you can choose any cell where you want it to show the input values. We will be using this cell in setting the data).
   • ListFillRange: CountryList (this is the named range we created in Step 1. This would show all the countries in the drop down).
   • MatchEntry: 2-fmMatchEntryNone (this ensures that a word is not automatically completed as you type)
5. With the Combo Box selected, Go to Developer Tab –> Controls –> Click on Design Mode (this gets you out of design mode, and now you can type anything in the Combo Box. Now, whatever you type would be reflected in cell K2 in real time)

Step 3 – Setting the Data

Finally, we link everything by helper columns. I use three helper columns here to filter the data.

Helper Column 1: Enter the serial number for all the records (20 in this case). You can use ROWS() formula to do this.

Helper Column 2: In helper column 2, we check whether the text entered in the search box matches the text in the cells in the country column.

This can be done using a combination of IF, ISNUMBER and SEARCH functions.

Here is the formula:

=IF(ISNUMBER(SEARCH($K$2,D4)),E4,"")

This formula will search for the content in the search box (which is linked to cell K2) in the cell that has the country name.

If there is a match, this formula returns the row number, else it returns a blank. For example, if the Combo Box has the value ‘US’, all the records with country as ‘US’ would have the row number, and rest all would be blank (“”)

Helper Column 3: In helper column 3, we need to get all the row numbers from Helper Column 2 stacked together. To do this, we can use a combination if IFERROR and SMALL formulas. Here is the formula:

=IFERROR(SMALL($F$4:$F$23,E4),"")

This formula stacks all the matching row numbers together. For example, if the Combo Box has the value US, all the row numbers with ‘US’ in it get stacked together.

Now when we have the row numbers stacked together, we just need to extract the data in these row number. This can be done easily using the index formula (insert this formula in where you want to extract the data. Copy it in the top-left cell where you want the data extracted, and then drag it down and to the right).

=IFERROR(INDEX($B$4:$D$23,$G4,COLUMNS($I$3:I3)),"")

This formula has 2 parts:
INDEX – This extracts the data based on the row number.
IFERROR – This returns blank when there is no data.

Here is a snapshot of what you finally get:

The Combo Box is a drop down as well as a search box. You can hide the original data and helper columns to show only the filtered records. You can also have the raw data and helper columns in some other sheet and create this dynamic excel filter in another worksheet. 

Get Creative! Try Some Variations

You can try and customize it to your requirements. You may want to create multiple excel filters instead of one. For example, you may want to filter records where Sales Rep is Mike and Country is Japan. This can be done exactly following the same steps with some modification in the formula in helper columns.

Another variation could be to filter data that starts with the characters that you enter in the combo Box. For example, when you enter ‘I’, you may want to extract countries starting with I (as compared with the current construct where it would also give you Singapore and Philippines as it contains the alphabet I).

As always, most of my articles are inspired by the questions/responses of my readers. I would love to get your feedback and learn from you. Leave your thoughts in the comments section.

Note: In case you’re using Office 365, you can use the FILTER function to quickly filter the data as you type. It’s easier than the method shown in this tutorial