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.
See Also: The Ultimate Guide to Find and Remove Duplicates in Excel.
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: =IFERROR(SMALL($F$4:$F$23,E4),””)
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):
=IFERROR(INDEX($B$4:$D$23,$G4,COLUMNS($J$3:J3)),””)
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).
Download the Example File
Did you like the tutorial? Let me know your thoughts in the comments section.
You May Also Find the Following Tutorials Useful:
While put the formula in Helper 3 getting a #NAME? error.
Please help.
Does anyone know how I could do this, but add a second filter in addition to the first? Ie. I want to filter for India and then within what’s filtered for India I want to also filter by Sales Rep name?
This tutorial was extraordinarily helpful in demonstrating this technique and enabling me to accomplish a specific task I was trying to complete. Thank you!
Hi Sumit,
Thank you for your tutorial, I have used your technique last year in an attendance sheet by creating a drop down list with department names and then it lists the staff name and ID. I have created the helper table on each tab of the sheet representing the year and using the drop down list of each sheet.
My problem is when adding newly hire employee or removing retired employee i have to make it manually on each sheet.
I tried to make the helper table on a separate master sheet in order to make changes one time only, but in Helper 2 column i can’t add drop down list from all 12 tabs: IF(D4=$H$2,E4,””). I mean instead of $H$2, add ‘June20′! $C3:’July 21’!$C3
Is there a away to show you the sheet and help me to have more than one drop down list in the formula of Helper
Thank you 2.
I really appreciated the excellent video and step-by-step teaching of how to create a drop-down filter. My question: Is there a way to add a “Show All” to the drop-down filter so that all filtered data in the table becomes visible? Or, have the data table already populated when the worksheet is first open then use the drop-down filter to filter the data in the same table? Thanks.
Very helpful.This is I wanted for a long time. Based on this video I created a table.In the unique list there are names like Sandiya and Balasandiya. When I extract the details for Sandiya, the details for Balasandiya are also extracted but not in vice versa.How to correct it? Please help me.
Hey mate. Love your Excel tips. Cheers
Hi Sumit okay lets start at A. I have two sheets, data sheet and main sheet. on main sheet I have drop down on cell D6 with values that match the values in row 8 on data sheet. If any value is true I want that complete column to be returned on main page. There could be up to 4 values that could match any of the values matched in row 8 on data sheet. My first attempt was with this: =IFERROR(INDEX(Inverter!$C$2:$T$15;;Inverter!C$19;ROWS(Inverter!$C$21:C$21));””), this works okay but only return the value of row 2 even with drag across all 4 columns match but only with top row. As soon as I drag the formula down the same value as in the top cell of each column return.
Hi Sumit,
I’m struggling to work out which formulas I need to be using.
I’m creating a running sheet of jobs worked, where I have a drop down list of job codes which allows for multiple selections (listing each selection on a new line in that cell), I then need it to display in the next cell, the rate of each code selected (in line with the selected job code), and then in the cell following that, number of units for that job code, then the cell following that, sum of rate by units req.
please help.
I can send you a file of where I’m currently at, please let me know where to send it.
regards
Steve
Hi sumit, Is there any way of showing all items in a product which is in dropdown list?
Greetings Sumit, I’m completely stumped, I’m trying to do this in the opposite direction. I have a Row which will be the main position of the primary selector. After selecting which item in the drop box i need; rather than having the information populate in different columns; I need the extractor to populate the data beneath that primary select in the same row and create additional rows if possible. I have a visual representation of what I need; is this even possible? Please respond.
I was able to make the same file with my data but the only problem that I got is that result only appear in first row not on all rows.
Dear sir, when i make like this including date format and number, My answer is wrong. How to do this. Can i send you my file. Thanks
I follow all the steps but when i my country in the drop down list menu – it did not populate with country selected
Brilliant tip. Did just what I wanted.
This is great! How can I achieve this same result for a comma delimited column?
Thank you very much, this was the best lesson I have seen! However, I have a little different challenge and I need to add multiple dropdown selections and produce a consolidated list of only correct matches. Can you please help me?
I need this too
How do I repeat this on the next drop down with the same information needed? I’m using it to pull equipment used on a test.
I’ve used your method and got what i want, but I need some more help, as I’ve a ledger of some consumers which contains some data like consumer name, consumer number (unique number), city, and area or street they live. I want to extract filtered data using more than one dependent drop down list, 1st one is “city” and another one is “area or street they live in”. what to do?
if i use the above example, i only get one type of data which is dependent on “area or street they live in, but i wanted to filter it out with both city and street…
plz help me..
Hi Sumit
Great tutorial. It works a treat.
I have an issue; if a record (row)on a separate data worksheet is deleted or inserted, the helper1 and 2 columns receive a #REF error as the reference is broken. I tried a number of solutions but couldn’t get it to work. I have ended up protecting rows and columns in the sheet. Any ideas?
Thanks again.
Hi Everyone,
I need a favor of yours. I have just implemented the same into Google spreadsheet and it’s creating an issue. I and created the same in Excel and it’s working fine.
In google sheet, the logic =IFERROR(INDEX(Data!$A$4:$C$52,Data!F4,1),””) is not working especially when there is no reference instead of printing blank it’s breaking.
Please let me know if you have any solution here.
Thanks in advance.
Hello, I have an excel sheet with multiple columns containing different information. In the drop down list for each column, multiple values can be selected. How do I pull data from a drop down list with multiple values?