You are reading the article Dynamic Excel Filter Search Box (Extract Data As You Type) updated in September 2023 on the website Khongconthamnam.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 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:
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
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:
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.
Select all the Countries and paste it into a new worksheet.
One additional step is to create a named range for this unique list. To do this:
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.
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:
If you do not have the Developer Tab visible, here are the steps to enable it.
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)
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)),"")
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.
Download the Dynamic Excel Filter Example File
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).
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.
You May Also Like the Following Excel Tutorials:
You're reading Dynamic Excel Filter Search Box (Extract Data As You Type)
Update the detailed information about Dynamic Excel Filter Search Box (Extract Data As You Type) on the Khongconthamnam.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!