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.
- You will get a simple array.
- Copy the following IF function for the second helper column (Helper 2):
- For the third helper column (Helper 3), use the following 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.
- Copy the following 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.
- 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.
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.
- Provide a name to the newly created table (say, Table4).
- Copy 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.
- 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:
- 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.
- 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!