A drop-down list filter is basically a list of unique names. If you select any of the items from the drop-down list, you will get the corresponding items related to your selection. In this article, you will learn to create a drop-down list filter based on cell a value in Excel step by step.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Steps to Create of an Excel Filter Using Drop Down List Based On Cell Value
Step-1: Make a Unique List to Create a Drop Down List Filter Based on Cell Value in Excel
To create a drop-down list filter you have to create a unique list first. Then you can do the rest based on the list.
So let’s create a unique list of items first.
To make a duplicate free unique item’s list,
❶ Copy the items from your data table first. For instance, I am separating the items from the Category column of my data table.
❷ Select the data to make a unique list and then go to DATA > Remove Duplicates.
❸ The Remove Duplicates dialog box will appear. Make sure that everything is as per your preferences. Then hit the OK command.
So you’ve just created a list of unique items. Now to add the drop-down list filter,
❹ Select a cell and then go to DATA > Data Validation > Data Validation.
Next, the Data Validation dialog box will appear.
❺ From the Settings tab, select List from the Allow box.
❻ Include the cell range where you’ve created the unique list of items. This option is available under the Source box.
❼ Then hit the OK command.
Finally, you will get your drop-down list filter in Excel as in the following picture.
Step-2: Make the Drop Down List Filter Work
So, we have just added the drop-down list filter in Excel. Now let’s filter out data from our existing data table using the drop-down list filter that we’ve just created.
To do that, we need 3 extra columns added to the main data table. These are helper columns. I named those columns Row SL, Matched, and Ordered respectively.
The First Helper Column: Row SL.
In this column, we will store the serial number of the rows in our data table. To do that,
❶ Insert the following formula in cell F5.
The argument for the ROWS function is an array. Where,
- $E$5 is the first cell of the Row SL. You can add a dollar sign by pressing the F4 key to lock the cell address.
- E5 is also the first cell of the Row SL.
So what the formula actually does is, it calculates the difference from cell $E$5 to E5. As we drag the Fill Handle icon from cell F5 to cell F12, $E$5 stays fixed but E5 gradually changes. The distance between the two cell addresses keeps increasing. Thus we get the serial number of the row of our data table.
💡 Note: You can add the serial number manually if you like.
❷ Then press the ENTER button to execute the formula.
❸ Drag the Fill Handle icon from cell F5 to cell F12.
The Second Helper Column: Matched
In this column, we want to return the serial number of only those rows that match with the item selected in the drop-down list filter in Cell, K4.
To do that,
❶ Type the following formula in Cell G5.
In the above formula,
- B5 is the cell address of the first item to match with the selected item of the drop-down list filter.
- $K$4 is the cell address of the drop-down list filter.
- F5 is the cell address of the value to return if there is a match between B5 and $K$4.
- “” is used to return a blank if there are no matches between B5 and $K$4.
❷ Press the ENTER button.
❸ Drag the Fill Handle icon from cell G5 to G12.
The Third Helper Column: Ordered
In the second helper column, Matched the row numbers may not appear in cells back to back. To make sure that the row numbers appear in one cell after another, the Ordered column will be in need of.
❶ In cell H5, insert the following formula:
- $G$5:$G$12 is the range of the cells where the SMALL function will look for the smallest number.
- F5 helps the SMALL function to find the smallest numbers sequentially. As it contains 1 and as F5 becomes greater the number that it contains increases each time by 1.
- “” is used to keep a cell blank with the help of the IFERROR function, if any error occurs due to the back of the value searched by the SMALL
❷ To execute the formula, hit the ENTER button.
❸ Finally drag the Fill Handle icon from cell H5 to H12.
So we are done with the helper column columns.
Step-3: Drop Down List Filter in Action
To make the drop-down list filter work,
❶ Copy the data table to another location. Then clear all the contents from it using the Clear Contents command. You can also press the DELETE key by selecting all the cells in the copied table to do so.
❷ In the very first cell of the copied data table, insert the following formula:
- $B$5:$E$12 is the cell range of the original data table.
- $G5 is the first cell of the second helper column.
- $M$5:M5 is the cell range of the first column of the copied data table.
- “” is used to leave all the cells blank with the help of the IFERROR function, if data is unavailable for the selected item in the drop-down list filter.
❸ Press ENTER to execute the formula.
❹ Drag the Fill Handle icon all over the copied data table to apply the above formula throughout every cell of the data table.
To sum up, we have discussed the step-by-step procedure to create a drop-down list filter based on a cell value in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.