How to Create an Excel Filter Using a Drop-Down List Based on the Cell Value – 3 Steps

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.

Remove Duplicates to Make a Unique List to Create a Drop Down List Filter Based on Cell Value in Excel

  • 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.

Validate Data to Make a Unique List to Create a Drop Down List Filter Based on Cell Value in Excel

In the Data Validation dialog box:

  • In Settings, select List in Allow.
  • Enter the cell range in Source.
  • Click OK.

Data Validation dialog box: Make a Unique List to Create a Drop Down List Filter Based on Cell Value in Excel

This is the output.

A Unique List to Create a Drop Down List Filter Based on Cell Value in Excel

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 First Helper Column to Create Excel Filter Using Drop-Down List Based on Cell Value: Row SL.


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 second helper column to Create Excel Filter Using Drop-Down List Based on Cell Value


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


<< Go Back to Excel Drop Down List FilterExcel Drop-Down List | Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo