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.

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.

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.

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

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

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

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

• Consequently, we will get the below output.

• Next, use the following IF function formula for the 2nd helper column (Helper 2).

• And for the 3rd helper column (Helper 3) use the below formula.

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.

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.

• 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

• Data Validation Drop Down List with VBA in Excel (7 Applications)
• Default Value in Data Validation List with Excel VBA (Macro and UserForm)
• How to Remove Blanks from Data Validation List in Excel (5 Methods)
• [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)
• How to Use Custom VLOOKUP Formula in Excel Data Validation

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.

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

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.

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

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

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.

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

• 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

• Excel Data Validation Alphanumeric Only (Using Custom Formula)
• Apply Custom Data Validation for Multiple Criteria in Excel (4 Examples)
• Autocomplete Data Validation Drop Down List in Excel (2 Methods)
• How to Make a Data Validation List from Table in Excel (3 Methods)
• Create Data Validation Drop-Down List with Multiple Selection in Excel
• How to Apply Multiple Data Validation in One Cell in Excel (3 Examples)