Welcome to this Excel tutorial, where we’ll explore the exciting world of Excel drop-down lists. Whether you’re a beginner or an advanced user, you’ll discover how these lists can make your data entry more efficient and empower you to create dynamic data reports that are easily filterable and updatable. One of the coolest things you can do with a drop-down list is to extract data from it. This skill is useful for generating new reports, filtering existing data, and gaining deeper insights from your datasets.
In this tutorial, we’ll show you how to extract data from an Excel drop-down list using two different methods:
- Method 1: Using Formulas
- Method 2: Using Power Query
Method 1: Extract Data from Excel Drop Down List using Formulas
Skills Needed: Basic Excel Skills
To extract data from a drop-down list using formulas, you’ll need to have a grasp of two essential functions:
- INDEX function: This function returns the value of a cell in a specified table or array based on row and column numbers.
- MATCH function: It helps you find the position of a specified value in a table or array.
Here’s what you need to do:
Prepare Your Data: Create a table that contains the data you want to extract. Make sure to have a header row with column names. Additionally, create a drop-down list containing the values you want to use for data extraction.
Apply the Formula: Use the following formula to extract the data:
=INDEX(table_range, MATCH(drop_down_list_value, table_column, 0))
In this formula:
table_rangeis the range of cells containing the table.drop_down_list_valueis the value currently selected in the drop-down list.table_columnis the column in the table containing the data you want to extract.
For instance, if you want to extract data from the Sales column in the Data table based on the value selected in the Product drop-down list, you’d use this formula:
=INDEX(Data, MATCH(Product, Data[Product], 0))
Method 2: Extract Data from Excel Drop Down List using Power Query
Skills Needed: Intermediate to Advanced Excel Skills
Power Query is a potent tool for data transformation and analysis in Excel. It can also help extract data from drop-down lists. Here’s how:
- Select the Drop-Down List Cell: Click on the cell containing the drop-down list.
- Access Power Query: Go to the “Data” tab and select “Get Data” > “From Other Sources” > “From Excel Files.”
- Choose the Excel File: Pick the Excel file containing the table with the data you want to extract.
- Navigate Power Query Editor: Click “Open.”
- Select Data Table: In the Power Query Editor, choose the table containing your data.
- Filter and Sort: Under the “Transform” tab, click “Filter & Sort” and then “Filter.”
- Configure Filtering: In the “Filter” dialog box, select the column with the drop-down list values.
- Specify Equals: In the “Operator” drop-down list, choose “Equals.”
- Set Value: In the “Value” field, select the value currently chosen in the drop-down list.
- Load Filtered Data: Click “OK.”
- Close and Load: Go to the “Home” tab and click “Close & Load.”
This will create a new table in Excel containing the filtered data.
Example
To give you a practical understanding, let’s look at how to extract data from a drop-down list using both methods. Consider the following sample data:
Formula Method:
=INDEX(Data, MATCH(Product, Data[Product], 0))
Power Query Method:
- Select the drop-down list cell.
- Go to the “Data” tab and click “Get Data” > “From Other Sources” > “From Excel Files.”
- Choose the Excel file containing the data table.
- Click “Open.”
- In the Power Query Editor, select the table with the data you want.
- Under the “Transform” tab, click “Filter & Sort” > “Filter.”
- In the “Filter” dialog box, select the
Productcolumn. - In the “Operator” drop-down, choose “Equals.”
- In the “Value” field, select the current drop-down list value.
These two methods allow you to effortlessly extract data from Excel drop-down lists, enhancing your data analysis capabilities.
Examples with sample data
Beginner example:
Suppose you have a simple table with two columns: Product and Sales. You also have a drop-down list that contains the product names. You want to use the drop-down list to filter the table and display the sales for the selected product.
To do this, you can use the following formula:
=INDEX(Data, MATCH(Product, Data[Product], 0))
Where Data is the range of cells that contains the table, and Product is the drop-down list cell.
For example, if the drop-down list contains the following values:
- Apples
- Oranges
- Bananas
And the table contains the following data:
| Product | Sales |
|---|---|
| Apples | 100 |
| Oranges | 50 |
| Bananas | 75 |
Then the following formula would return the sales for the selected product:
=INDEX(Data, MATCH(Product, Data[Product], 0))
If you select “Apples” from the drop-down list, the formula would return 100. If you select “Oranges”, the formula would return 50, and so on.
Advanced example:
Suppose you have a more complex table with multiple columns, and you want to use the drop-down list to filter the table based on multiple criteria. For example, suppose your table contains the following columns:
- Product
- Region
- Sales
And you have a drop-down list that contains the product names and the region names. You want to use the drop-down list to filter the table and display the sales for the selected product and region.
To do this, you can use the following formula:
=SUMPRODUCT((Data[Product]=Product)*(Data[Region]=Region),Data[Sales])
Where Data is the range of cells that contains the table, Product is the drop-down list cell that contains the product name, and Region is the drop-down list cell that contains the region name.
For example, if the drop-down lists contain the following values:
- Product: Apples, Oranges, Bananas
- Region: North America, South America, Europe
And the table contains the following data:
| Product | Region | Sales |
|---|---|---|
| Apples | North America | 100 |
| Oranges | South America | 50 |
| Bananas | Europe | 75 |
Then the following formula would return the sales for the selected product and region:
=SUMPRODUCT((Data[Product]=Product)*(Data[Region]=Region),Data[Sales])
If you select “Apples” from the product drop-down list and “North America” from the region drop-down list, the formula would return 100. If you select “Oranges” and “South America”, the formula would return 50, and so on.
You can use these two formulas as a starting point to extract data from drop-down lists in Excel. You can modify them to fit your specific needs, and you can also combine them with other Excel functions to create more complex data extraction routines.