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, such that it filters the data as you type in a search box.
Something as shown below:
There is a dual functionality to this – you can select a country’s name from the drop down, or you can type 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 the Dynamic Excel Filter
This Dynamic Excel filter can be created in 3 steps:
- Getting a unique list of items (countries in this case). This would be used in creating the drop down.
- Creating the search box. Here I have used a Combo Box (ActiveX Control).
- Setting the Data. Here I would use three helper columns with formulas to extract the matching data.
Here is how the raw data looks:
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
- Select all the Countries and paste it in a new worksheet.
- Select the country list –> Go to Data –> Remove Duplicates.
- 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:
- 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)
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.
Step 2 – Creating The Dynamic Filter Search Box
For this technique to work, we would need to set a Combo Box and link it to a cell. This way, anything that you type in the Combo Box would also be reflected in a cell in real-time.
Here are the steps to do this:
- 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.
- Click anywhere on the worksheet. It will insert the Combo Box.
- Right-click on Combo Box and select Properties.
- 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)
- 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:
This formula will search for the content in the combo 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 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 (“”)
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).
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.