To get a better visualization and comparison, we may need to extract some specific data based on certain criteria. In this article, we will show you how to extract data based on a drop-down list selection in Excel.
How to Extract Data Based on a Drop Down List Selection in Excel: Easy Steps
In the image below, a sample data set is provided to accomplish the tutorial to show how to extract data from the drop-down list. We will use Data Validation to make a drop-down list. Later on, we will use the FILTER function, to filter the extracted data.
Step 1: Create a Table to Extract Data Based on a Drop Down List Selection in Excel
- Select the Table.
- Click on the Insert.
- Click on the Table Design, and give a name (Sales) to it.
Step 2: Extract the Unique Data Based on a Drop Down List Selection in Excel
- To make a list with the unique Values in the Branch column, apply the UNIQUE function.
- Therefore, you will get the unique values for the Branch.
Step 3: Insert a Data Validation List to Find Data Based on a Drop Down List Selection in Excel
- To create a Data Validation list, click on Data.
- Then, click on the Data Validation.
- Select the List from the Allow.
- Press Enter.
- In the source box, select the List.
- Finally, press Enter.
- As a result, you will see that, Data Validation drop down list is created.
Step 4: Apply the FILTER Function to Extract Data Based on a Drop Down List Selection in Excel
- In the FILTER Function, add the Table ‘Sales’ as the array element by using the formula.
- In the Include argument, add the Branch Use the following formula.
=FILTER(Sales,Sales[Branch] = H4
- H4 is the cell of the drop-down selection box.
- In the ‘if empty’ argument, type “Nothing Found”.
=FILTER(Sales,Sales[Branch] = H4,"Nothing Found")
- Now, select any option (Texas), to extract all the related value.
- Therefore, you will find all the values regarding ‘Texas’.
Notes. The FILTER function is only available in Microsoft 365.
Step 5: Insert Another Criterion to Extract Data Based on a Drop Down List Selection
- To insert another criterion, make a unique list with another column (Products). Type the formula in a cell.
- Therefore, another unique list will be created for the ‘Products‘ column.
- Make another Data Validation drop down list with selecting the cell values.
- Then, press Enter.
Step 6: For Multiple Criteria Extract Data Based on a Drop Down Selection List in Excel
- After creating another drop-down list, it will show like the image below.
- Type the following formula to apply both the criteria.
=FILTER(Sales,(Sales[Branch] = H4)*(Sales[Products]=H6),"Nothing Found")
- Select any two options from the two drop down list.
- As a result, you will get the value of the rows, satisfied both the criteria.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Finally, I hope you now know how to convert text to date using Excel VBA. All of these tactics should be performed while your data is being educated and practiced. Look over the practice book and put what you’ve learned to use. Because of your generous support, we are motivated to continue delivering initiatives like these.
Please do not hesitate to contact us if you have any questions. Please let us know what you think in the comments area below.
Stay with us and keep learning.
- Conditional Drop Down List in Excel
- How to Use IF Statement to Create Drop-Down List in Excel
- How to Create Dynamic Dependent Drop Down List in Excel
- Excel Dependent Drop Down List
- How to Make Dependent Drop Down List with Spaces in Excel
- Excel Formula Based on Drop-Down List
- How to Populate List Based on Cell Value in Excel
- How to Change Drop Down List Based on Cell Value in Excel