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

In this article, I will discuss how you can filter Excel data using a Data Validation drop-down list. Usually, in Microsoft Excel, we use the Filter option to extract particular data. However, you can use the drop-down list to filter data. To perform the task, initially, I will create a drop-down list using Data Validation in Excel. Later based on the drop-down item selection, I will filter out corresponding rows.


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

Let’s consider, we have a dataset containing area-wise sales data of several fruits. In this article, I will create a  Data Validation drop-down list of areas mentioned in the dataset. Then, I will use the drop-down list to draw fruit sales data.

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


1. Filter Values from Data Validation Drop Down List Using Helper Columns

In this method, I will add 3 helper columns to the master dataset. Later, I will draw data based on the drop-down selection. Before entering helper formulas, I will create a drop-down list containing unique Areas. Follow the below steps to do the task.

Steps:

  • Before creating the drop-down list, list all the unique Areas as below.

Filter Values from Data Validation Drop Down List Using Helper Columns

  • Then, click on the cell where you want to locate the drop-down list (here Cell H5).

  • From Excel Ribbon, go to Data > Data Tools > Data Validation > Data Validation.

Filter Values from Data Validation Drop Down List Using Helper Columns

  • As a result, the Data Validation dialog box will appear. Then, go to the Settings tab, choose List from Allow section and specify the Source. After that press OK.

Filter Values from Data Validation Drop Down List Using Helper Columns

  • Upon pressing OK, we receive the drop-down list.

Filter Values from Data Validation Drop Down List Using Helper Columns

  • Now, come to the helper columns. Type the below formula in the first helper column (in Cell D5) using the ROWS function. Press Enter and use the Fill Handle (+) tool to copy the formula over the entire column.
=ROWS($A5:A$5)

  • Consequently, we will get the below output.

Filter Values from Data Validation Drop Down List Using Helper Columns

=IF(C5=$H$5,D5,"")

Filter Values from Data Validation Drop Down List Using Helper Columns

  • And for the 3rd helper column (Helper 3) use the below 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.

  • Now, suppose for the Baltimore area, I want to filter all the corresponding fruit sales data. To get the expected result, type the below 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.

  • Once you enter the above formula, the following will be the result. Drag the Fill Handle to the right to get all the data in a row.

  • Then, drag the Fill Handle down as below and get the ultimate fruit sales data for the Baltimore area.

Filter Values from Data Validation Drop Down List Using Helper Columns

  • Now, 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


2. Excel FILTER Function to Extract Data Based on 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. Before starting the process I converted the data range to an Excel table by pressing Ctrl + T. This is because, 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

  • For ease of operation, I will give a name to the newly created table (say, Table4).

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

Now let’s follow the below steps to perform the main task.

Steps:

  • First, we will create a unique list of areas using the UNIQUE function. To do that, type 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.

  • Upon entering the formula here is the result we got. The above formula returns sorted unique data as an array (outlined in blue color).

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

  • Now create the drop-down list in Cell H5. Follow the below path to bring the Data Validation dialog box: Data > Data Tools > Data Validation > Data Validation. From that dialog, choose List from Allow section and type the below formula in the Source field. Then press OK.
=F5#

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

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

  • Once you press OK, the below drop-down list will be created.

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

  • Now, let’s consider that I want to draw fruit sales data for the Long Beach area. To get the desired result, type the below 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

  • Finally, upon entering the FILTER formula, we will get all sales data for the Long Beach area. You can 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.


Conclusion

In the above article, I have tried to discuss two methods to filter data using the Data Validation drop-down list in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


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