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.
6 Steps to Extract Data Based on a Drop Down List Selection in Excel
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.
Read More: How to Add Item to Drop-Down List in Excel (5 Methods)
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 formula of the UNIQUE.
=UNIQUE(Sales[Branch])
- Therefore, you will get the unique values for the Branch.
Read More: Unique Values in a Drop Down List with VBA in Excel (A Complete Guide)
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.
Read More: Creating a Drop Down Filter to Extract Data Based on Selection in Excel
Similar Readings
- How to Create Dependent Drop Down List with Multiple Words in Excel
- Dependent Drop Down List with Spaces in Excel
- How to Add Blank Option to Drop Down List in Excel (2 Methods)
- Copy Filter Drop-Down List in Excel (5 Ways)
- How to Remove Duplicates from Drop Down List in Excel (4 Methods)
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.
=FILTER(Sales
- 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 the all the values regarding ‘Texas’.
Notes. The FILTER function is only available in Microsoft 365.
Read More: Create Excel Filter Using Drop-Down List Based on Cell Value
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.
=UNIQUE(Sales[Products])
- 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.
Read More: How to Change Drop Down List Based on Cell Value in Excel (2 Ways)
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.
Read More: VBA to Select Value from Drop Down List in Excel (2 Methods)
Conclusion
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.
The ExcelDemy team will respond as quickly as possible.
Stay with us and keep learning.
Related Articles
- How to Remove Used Items from Drop Down List in Excel (2 Methods)
- Create a Form with Drop Down List in Excel
- How to Select from Drop Down and Pull Data from Different Sheet in Excel
- Hide or Unhide Columns Based on Drop Down List Selection in Excel
- How to Create a Drop Down List with Unique Values in Excel (4 Methods)
- Create Excel Drop Down List from Table (5 Examples)
- How to Make Multiple Selection from Drop Down List in Excel (3 Ways)