MS Excel – Custom Filter (Dynamic Filter)
Excel Filter is one of the most used functionalities when you work with data. In this video I will show you how to filters the data based on what you select from the drop down list.
Creating a Dynamic Excel Filter
Step-1 Data Entry and drop down menu
In first step, we’ve to create drop-down list after inserting data
Step-2 Preparation of Supporting columns
Support Column-1 Enter the serial number for all the record. You may use ROWS() formula to do this.
Support Column-2 We will check whether the text selected from the drop-down menu matches the text in the cells in the country column. This will be done using a combination of IF, ISNUMBER and SEARCH functions.
Support Column-3 We need to get all the row numbers from Support Column 2 stacked together. To do this, we can use a combination if IFERROR and SMALL formulas.
Step-3 Preparation of final table
In this step, 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 to the whole table.
You can learn preparation of dependent drop-down list from the following video:
• MS Excel - Dependent Dropdown List
Subscribe the channel for latest/updated videos.