Excel Data Validation Drop Down List with Filter (2 Examples)

Let’s consider a dataset containing area-wise sales data of several fruits. We will create a Data Validation drop-down list of areas mentioned in the dataset and use the list to draw fruit sales data.

2 Examples to Apply Excel Data Validation Drop Down List with Filter


Method 1 – Filter Values from the Data Validation Drop Down List Using Helper Columns

Let’s add three helper columns to the dataset which will be used to pull data.

Steps:

  • List all the unique Areas separately.

Filter Values from Data Validation Drop Down List Using Helper Columns

  • Click on the cell where you want to put the drop-down list (here Cell H5).

  • From the Excel Ribbon, go to Data and Data Tools, then select Data Validation and choose the Data Validation option.

Filter Values from Data Validation Drop Down List Using Helper Columns

  • The Data Validation dialog box will appear. Go to the Settings tab, choose List from Allow section and specify the Source as the unique list you created.
  • Press OK.

Filter Values from Data Validation Drop Down List Using Helper Columns

  • You’ll receive a drop-down list.

Filter Values from Data Validation Drop Down List Using Helper Columns

  • Copy the following formula in the first helper column (in Cell D5). Press Enter and use the Fill Handle (+) tool to copy the formula over the entire column.
=ROWS($A5:A$5)

  • You will get a simple array.

Filter Values from Data Validation Drop Down List Using Helper Columns

  • Copy the following IF function for the second helper column (Helper 2):
=IF(C5=$H$5,D5,"")

Filter Values from Data Validation Drop Down List Using Helper Columns

  • For the third helper column (Helper 3), use the following formula:
=IFERROR(SMALL($E$5:$E$14,D5),"")

Filter Values from Data Validation Drop Down List Using Helper Columns

Here, the SMALL function returns k-th smallest values in the range E5:E14. Later, the IFERROR function returns blank if the result of the SMALL formula is an error.

  • Copy the following formula in Cell J5 and press Enter.
=IFERROR(INDEX($A$5:$C$14,$F5,COLUMNS($J$5:J5)),"")

Filter Values from Data Validation Drop Down List Using Helper Columns

Here, the INDEX function draws the data based on the wow number. Then the COLUMNS function returns the column number in the range $J$5:J5. Finally, the IFERROR function returns blank if the result is an error.

  • Drag the Fill Handle two cells to the right to get all the data in a row.

  • Drag the Fill Handle down for as many rows as you have in the original table (to ensure you get all the results).

Filter Values from Data Validation Drop Down List Using Helper Columns

  • If you choose the Phoenix area from the drop-down list, rows corresponding to Phoenix will be filtered as below.

Read More: How to Make a Data Validation List from Table in Excel


Method 2 – Use the Excel FILTER Function to Extract Data Based on a Data Validation Drop Down List

If you are working in Excel 2019 and later versions or in Microsoft 365, you can filter data using the FILTER function.

Steps:

  • We converted the data range to an Excel table by pressing Ctrl + T. If you add new records to a table, the drop-down list gets updated according to the newly added data.

Excel FILTER Function to Extract Data Based on Data Validation Drop Down List

  • Provide a name to the newly created table (say, Table4).

Excel FILTER Function to Extract Data Based on Data Validation Drop Down List

  • Copy the following formula in Cell F5 and hit Enter.
=SORT(UNIQUE(Table4[Area]))

Excel FILTER Function to Extract Data Based on Data Validation Drop Down List

Here, I have used the SORT function along with the UNIQUE function to sort the above Area data.

  • The above formula returns sorted unique data as an array (outlined in blue).

Excel FILTER Function to Extract Data Based on Data Validation Drop Down List

  • Create the drop-down list in Cell H5 (choose Data Validation in the Data tab).
  • From the Data Validation dialog box, choose List from Allow section and input the following formula in the Source field:
=F5#
  • Press OK.

Excel FILTER Function to Extract Data Based on Data Validation Drop Down List

The # symbol indicates we are considering the whole array of Cell F5 as the source for the drop-down list.

  • This creates a drop-down validator.

Excel FILTER Function to Extract Data Based on Data Validation Drop Down List

  • Copy the following formula in Cell F11 and press Enter.
=FILTER(Table4,Table4[Area]=H5,"No Data Found")

Excel FILTER Function to Extract Data Based on Data Validation Drop Down List

  • Drag the fill handle to the right to show the results in all columns, then drag it down to cover more rows.
  • Change the area from the drop-down list and thus filter the corresponding rows based on the area selected.

Read More: How to Use Data Validation List from Another Sheet


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Related Articles


<< 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!
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo