Step 1 – Set up a Unique List to Create a Drop-Down List Filter Based on the Cell Value in Excel
- Copy the data. Here, the data in the Category column.
- Select the data and go to DATA > Remove Duplicates.
- In the Remove Duplicates dialog box, click OK.
The list of unique items is created. To add the drop-down list:
- Select a cell and go to DATA > Data Validation > Data Validation.
In the Data Validation dialog box:
- In Settings, select List in Allow.
- Enter the cell range in Source.
- Click OK.
This is the output.
Read More: How to Create a Drop Down List with Unique Values in Excel
Step 2 – Prepare the Drop Down List to Filter data
- Insert 3 helper columns: Row SL, Matched, and Ordered.
The First Helper Column: .
Use the Row SL column to store the serial number of the rows in the data table:
- Enter the following formula in F5.
=ROWS($E$5:E5)
The argument for the ROWS function is an array:
- $E$5 is the first cell in Row SL. Add a dollar sign by pressing F4 to lock the cell address.
- E5 is also the first cell in Row SL.
The formula calculates the difference between $E$5 and E5. As you drag the Fill Handle icon from F5 to F12, $E$5 is locked but E5 changes. The distance between the two cell addresses increases and you get the serial numbers of the row.
- Press ENTER.
- Drag down the Fill Handle to see the result in the rest of the cells.
The Second Helper Column: Matched
Use this column to return the serial number of rows that match the item selected in the drop-down list in K4:
- Use the following formula in G5.
=IF(B5=$K$4,F5,"")
In the formula,
- B5 is the cell address of the first item to match with the selected item of the drop-down list.
- $K$4 is the cell address of the drop-down list.
- 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 ENTER.
- Drag down the Fill Handle to see the result in the rest of the cells.
The Third Helper Column: Ordered
Use this column to see row numbers in one cell after another:
- In H5, use the following formula:
=IFERROR(SMALL($G$5:$G$12,F5),"")
- $G$5:$G$12 is the cell range in which the SMALL function will look for the smallest number.
- F5 helps the SMALL function find the smallest numbers sequentially, as it contains 1, and the number increases by 1 when you drag the Fill Handle.
- “” is used to keep a cell blank with the help of the IFERROR function, if an error occurs.
- Press ENTER.
- Drag down the Fill Handle to see the result in the rest of the cells.
Read More: How to Create Drop Down List with Filter in Excel
Step 3: Use the Drop-Down List to Filter data
- Copy the data table to another location.
- Clear the content using the Clear Contents command. You can also select the table and press DELETE.
- In the first cell of the copied table, use the following formula:
=IFERROR(INDEX($B$5:$E$12,$G5,COLUMNS($M$5:M5)),"")
- $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 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.
- Drag down the Fill Handle to see the result in the rest of the cells.
Download Practice Workbook
Download the Excel file.
Related Articles
- Conditional Drop Down List in Excel
- How to Use IF Statement to Create Drop-Down List in Excel
- How to Create Dynamic Dependent Drop Down List in Excel
- Excel Dependent Drop Down List
- How to Make Dependent Drop Down List with Spaces in Excel
- Excel Formula Based on Drop-Down List
- How to Create Dependent Drop Down List with Multiple Words in Excel
- How to Extract Data Based on a Drop Down List Selection in Excel
- How to Populate List Based on Cell Value in Excel
- How to Change Drop Down List Based on Cell Value in Excel
<< Go Back to Excel Drop Down List Filter | Excel Drop-Down List | Data Validation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!