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

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

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

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

• You’ll receive a drop-down list.

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

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

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

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)),"")`

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

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

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

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

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

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

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

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.

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

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

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

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF