Dynamic Excel Filter Search Box – Extract Data as you Type

Excel Filter is one of the most used functionalities when you work with data. In this blog post, I will show you how to create a Dynamic Excel Filter Search Box, such that it filters the data based on what you type in the search box.

Something as shown below:

Dynamic Excel Filter Search Box - Demo of the excel functionality where you can filter 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.

Download Example File and Follow Along

Watch Video – Creating a Dynamic Excel Filter Search Box

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:

Dynamic Excel Filter Search Box - This 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.Clean Data in Excel - 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:Dynamic Excel Filter - Getting a unique list of values
  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)Dynamic Excel Filter - Named Range

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).

Dynamic Excel Filter - Text reflected in real time

Here are the steps to do this:

  1. Go to Developer Tab –> Controls –> Insert –> ActiveX Controls –> Combo Box (ActiveX Controls).
  2. Click anywhere on the worksheet. It will insert the Combo Box.Dynamic Excel Filter - Combo Box Inserted
  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)Creating a Dynamic Excel Filter Search Box - Combo Box Properties
  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)Dynamic Excel Filter - Text reflected 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.Dynamic Excel Filter - Helper Column 1

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 (“”)Dynamic Excel Filter - Helper Column 2

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.Dynamic Excel Filter - Helper Column 3

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:Dynamic Excel Filter - Final Output

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. Dynamic Excel Filter - Demo of the Search Box

Download the Dynamic Excel Filter Example FileDownload File Pic

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.

You May Also Like the Following Excel Tutorials:

Online Excel Tips Tutorials Training and Videos

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)