Watch Video – Extract Data Using a Drop Down List in Excel
In this tutorial, I will show you how to create a drop-down filter in Excel so that you can extract data based on the selection from the drop-down.
As shown in the pic below, I have a created a drop-down list with country names. As soon as I select any country from the drop-down, the data for that country gets extracted to the right.
Note that as soon as I select India from the drop-down filter, all the records for India are extracted.
Extract Data from Drop Down List Selection in Excel
Here are the steps to create a drop-down filter that will extract data for the selected item:
- Create a Unique list of items.
- Add a drop-down filter to display these unique items.
- Use helper columns to extract the records for the selected item.
Let’s deep dive and see what needs to be done in each of these steps.
Create a Unique List of Items
While there could be repetitions of an item in your dataset, we need unique item names so that we can create a drop down filter using it.
In the above example, the first step is to get the unique list of all the countries.
Here are the steps to get a unique list:
- Select all the Countries and paste it at some other part of the worksheet.
- Go to Data –> Remove Duplicates.
- In the Remove Duplicates dialogue box, select the column where you have the list of countries. This will give you a unique list as shown below.
Now we will use this unique list to create the drop-down list.
Creating the Drop Down Filter
Here are the steps to create a drop down list in a cell:
- Go to Data –> Data Validation.
- In Data Validation dialogue box, select the Settings tab.
- In Settings tab, select “List” in the drop down, and in ‘Source’ field, select the unique list of countries that we generated.
- Click OK.
The goal now is to select any country from the drop-down list, and that should give us the list of records for the country.
To do this, we would need to use helper columns and formulas.
Create Helper Columns to Extract the Records for the Selected Item
As soon as you make the selection from the drop down, you need Excel to automatically identify the records that belong to that selected item.
This can be done using three helper columns.
Here are the steps to create helper columns:
- Helper Column #1 – Enter the serial number for all the records (20 in this case, you can use ROWS() function to do this).
- Helper Column #2 – Use this simple IF Function function: =IF(D4=$H$2,E4,””)
- This formula checks whether the country in the first row matches the one in the drop down menu. So if I select India, It checks whether the first row has India as the country or not. If it’s True, it returns that row number, else it returns blank (“”). Now when we select any country, only those row numbers are displayed (in the second helper column) which has the selected country in it. (For example, if India is selected, then it will look like the pic below).
Now we need to extract the data for these rows only, which displays the number (as it is the row that contains that country). However, we want those records without the blanks one after the other. This can be done using a third helper column
- Third Helper Column – Use the following combination of IFERROR and SMALL functions:
This would give us something as shown below in the pic:
Now when we have the number together, we just need to extract the data in that number. This can be done easily using the INDEX function (use this formula in the cells where you need the result extracted):
This formula has 2 parts:
INDEX – This extracts the data based on the row number
IFERROR – This function returns blank when there is no data
Here is a snapshot of what you finally get:
You can now hide the original data if you want. Also, you can have the original data and extracted data in two different worksheets as well.
Go ahead. use this technique, and impress your boss and colleagues (a little show-off is never a bad thing).
Did you like the tutorial? Let me know your thoughts in the comments section.
You May Also Find the Following Tutorials Useful:
- Dynamic Excel Filter – Extract Data as you type.
- Dynamic Search in Excel Using Conditional Formatting.
- Create Dynamic Drop Down with Search Suggestions.
- How to Extract a Substring in Excel Using Formulas.
- How to Filter Cells with Bold Font Formatting in Excel.