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

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

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


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 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, here 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

  • Next, use the following IF function formula for the 2nd helper column (Helper 2).
=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 Create Excel Drop Down List for Data Validation (8 Ways)


Similar Readings


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

If you are working in Excel 365, you can filter data using the FILTER function. Before starting the process I have 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, 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: Excel Data Validation Based on Another Cell Value


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

Hosne Ara
Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo