Creating a Drop Down Filter to Extract Data Based on Selection

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.

extract data from Drop Down Filter List Selection in Excel

Note that as soon as I select India from the drop-down filter, all the records for India are extracted.

Download the Example FileDownload File

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:

  1. Create a Unique list of items.
  2. Add a drop-down filter to display these unique items.
  3. 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:

  1. Select all the Countries and paste it at some other part of the worksheet.
  2. Go to Data –> Remove Duplicates.
  3. 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.

Unique list of items for the drop down filter

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:

  1. Go to Data –> Data Validation.
  2. In Data Validation dialogue box, select the Settings tab.
  3. In Settings tab, select “List” in the drop down, and in ‘Source’ field, select the unique list of countries that we generated.
  4. Click OK.

extract data from Drop Down List Selection in Excel - Custom Filter Data Validation Dialogue box

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).

Drop Down List Selection in Excel - Helper ColumnsNow 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:

Drop Down List Selection in Excel helper column 3Now 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:

extract data from Drop Down List Selection in Excel - extracted data

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
Download File

Did you like the tutorial? Let me know your thoughts in the comments section.

You May Also Find the Following Tutorials Useful:

  • Shubhankar Banerjee says:

    Hii..Very helpful excel functionalities..The steps helped me to develop a report completely.
    Thanks.

  • Saheli Chakraborty says:

    Hi! Thanks for sharing this, really helpful. Also, if I have to create three unique drop-down lists and pull data from source sheet automatically based on the drop-down selection. Say have data by industry, by geography and by month, now need to pull information by a combination of this 3 filters from unique drop-down lists. Can you help?

  • Les Henderson says:

    Almost exactly what I’ve been looking for. Thank you.

  • kev says:

    How do I add multiple drown down menus? For example,
    If i wanted a drop down menu for Geography and product name?

  • Phil S says:

    Hi Guys, I’m stumped with this one. I’m using the following formula to get the helper 3 coloumn. It works fine for a small array of 1000 rows, but when I increase it to 10,000 for example…. it returns BLANK? – Any Ideas?

    =IFERROR(SMALL($Q$2:$Q$1048,ROWS($Q$2:Q2)),””)

  • Phil S says:

    Hi Guys, I’m stumped with this one. I’m using the following formula in column E to return the row numbers of the name I’ve selected in column A, to get the helper 3 bit. It works fine for a small array of 1000 rows, but when I increase it to 10,000 for example…. it returns BLANK? – Any Ideas?

    =IFERROR(SMALL($Q$2:$Q$1048,ROWS($Q$2:Q2)),””)

  • Gumising Kha says:

    HI, nice tutorial, but i was made for 3 column, what if i have around 12 columns, how many helper i will create?

  • Lorena Vazquez says:

    Hello, How would the formula change on the helper columns if I’m trying to extract several columns of data. For example if I need 6 columns extracted would I need 6 helpers columns and what formulas would change?
    Thank you!

  • Jia Min says:

    Hello! is it possible for the drop down list to be multiple selection? how do i extract multiple data if i have more than one selection from the list?

  • YoonGi Scherzy says:

    Hi, thank you for this!

    I just have one more question, what if i want to add one more column after sales rep column, what is the formula for that?

  • Fernando Diaz says:

    thanks

  • arjay gervacio says:

    Do you know how to do this through Google Sheets?

  • Cat says:

    Does this pull from multiple sheets? I’d like to get a drop down to reference several sheets of values on the last page so people can see all the data relative to their names and save searching time, but there are multiple sheets worth of data to track, and compiling them into one document makes my work significantly harder.

  • Peace Olubere says:

    Wow… this works perfectly.
    Thanks a bunch.

  • Vaibhav Kumar says:

    I have a multiple drop down it has the match all the drop down and fetch the data Please help. I am able to use only one drop down to fetch the data as explained above

  • Mahesh Chiranjeevi says:

    Can anyone please help me with the below query?

    My requirement is… when i select a value on column A, then column B should list only the values related to Column A

    ColumnA ColumnB

    123 1
    123 2
    234 1
    234 2
    345 1
    456 1
    567 1
    678 1

    Expected Result

    ColumnA (drop down) ColumnB (drop down)

    123 1
    2

  • Mahesh says:

    Hi Sumit,
    Hope you can help me with this..

    My requirement is… when i select a value on column A, then column B should list only the values related to Column A

    ColumnA ColumnB

    123 1
    123 2
    234 1
    234 2
    345 1
    456 1
    567 1
    678 1

    Expected Result

    ColumnA (drop down) ColumnB (drop down)

    123 1
    2

  • James says:

    Thank you for this solution.
    IS this able to be done in Google Sheets?

    Thank you

  • Faniso C Zimunya says:

    Thanks so much. This tutorial helped me improve our processes and productivity. Looking forward to doing so much more with Excel now. (They should pay you!)

  • Nerilyn says:

    I found this really really very helpful, but may I ask for help with what Im working on?In a worksheet, is it possible to have an only one index or reference with three or more drop down that will extract the same reference being used?

  • ashley sutton says:

    Hi! this is so great thanks! Is there a way I can add one more drop down list criteria? So for example in your tutorial. I select India and get data extracted for India, but what if i want India AND only sales rep Joe. SO i would have a drop down list for India and another drop down list to just look at sales rep Joes stuff?

  • Monica says:

    is there a way to have a searchable drop down list? so that i can extract the names in the list by entering first 2 or 3 letters in the particular word and data can be extracted

  • Charmain says:

    Hi i have used this to create a purchase order based on our stock list. It works brilliantly, except i would like it to only show rows of, In my case, items to order with a quantity of 1 and above how can i do this. Thank you

  • Nguyen Thuy An says:

    Can we extract the data from multiple drop-down selection?

    • Sumit Bansal says:

      Yes you can extract using multiple drop downs as well. You’ll need to modify the condition in helper column such that it returns TRUE only when all the drop down selection match

      • Karina says:

        OK…could you share an example please?

  • Karl says:

    Can I have an excel sheet with all the data from the drop down selections on it without the drop down?

  • ZUKISA BHOLOSHA says:

    Hi, if one product is shared by two countries how can I filter that ?

  • Sachin Nikam says:

    Hi,

    Request you to please share same process in VBA code.

    Email.id : sachin.nikam@hungama.com

  • cris says:

    Hello there!

    Thank you so much for your explanation, it is great!

    I am using a file which doesn’t bring country list; however, brings some information other spreadsheet. Anyway it is not working, the “helper 3” brings the information, but doesn’t show up on “Product name or Sales Rep” and I do not know what I made wrong.

    Can you please help me? I really got stuck on these files, 2 weeks already 🙁
    Thank you,
    Cris

    • cris says:

      I forgot the file!
      Thank you!

  • Imran Sheikh says:

    greetings trump excel.com it is great platform to learn best excel warm greetings and thanks to all excel bestie’s here in this list….m here suppose to ask question but i see lawre*** has already ask the same question question thanks to Mr. sumit bansal for great help!!!!!
    shaikh imran

  • Sam says:

    This has helped! But I do have a question (apologies if this
    has already been answered in the comments).

    I am having a problem with cross referencing the data. For example I want to
    see all the people from a certain district and then filter the results by how
    many male/female in that district. When I try this it doesn’t work, I believe
    it has something to do with the ‘helper’ columns. Do you know how to make this
    work?

  • STFN says:

    Thanks a lot, this is amazing!

  • Fatai Raji says:

    If i have country, province and district details to be the cretaria for selection can you please explain how to implement that

  • Adeana Williams says:

    Hello Lawrence….how do i do this with lots of data

  • FUWEI says:

    Hi if I want to add a row into the data like example I want to insert an additional product between product 14 and product 15, the helpers do not update automatically. What can I do to make the helpers update automatically when a row is added / deleted? So that the extracted data on the right shows the new data?

    • Sumit Bansal says:

      Once you have inserted a new row, click on the first cell of each column. When you click on the cell you will see a small black square at the bottom right. Click on it and drag it down. Do it for all the columns.

  • Erik Andersson says:

    Hi! Great tutorial.

    I’m trying to make a excel sheet with product information witch can sort out and display products witch match certain criteria. Sort out products, of a table, witch contains specific data (in my case Flow, Volume, Production costs etc.). My goal is to have a worksheet with my company’s old work (I work with water cleaning systems) and with this worksheet sort out all the water cleaning systems witch match my search, and display those in some way. And then automaticly calculate a price based on those. I want to able to have multiple drop downs to make my search narrower.

    I have tried slicers but i can’t get it to work and display multiple matches. Maybe it’s easier with drop down lists?

  • jacinthe says:

    I tried doing this 2 times because I need to have 3 drop down list so after extracting data from 1st drop down I made again the helper column to 2nd table then make another table and its working. but my problem is, I want to make my drop down list dependent on what 1st drop down list chose then 2nd drop down list to 3rd drop down list. I tried following the dependent drop down list tutorial but it’s not working. please help me to make this 3 drop down list dependent to each other after extracting data from one another. Or is there any way to do this. thanks

    • Sumit Bansal says:

      Hello.. Can you share the sample file. It would be easier to guide once I can see the data

      • Heath says:

        Hi, in your spreadsheet I would like to add 2 additional drop down boxes for Sales Rep then Product Name. I want each drop down to be dependent on the first drop down boxes criteria. How can I make this possible?

  • Steve K says:

    Great tutorial!! These lessons keep opening new ideas for existing files I work with to make them better. I do need to manipulate the data from this lesson once more. I have a database that lists as columns: First name, Last name Floor, Cubicle, Job Position, Training Date, Equipment issued, issued date. When data is entered I have drop down menus for Job position and Equipment issued. Multiple equipment can be issued to the same person which creates blank cells in the other columns until another name is entered. On the next sheet I have the sort by drop down list as mentioned above. I have successfully implemented it and even get the blank lines to be ignored. However, I need that sorted table, or the first one, to be listed alphabetically by LAST NAME automatically. I cannot sort the first database by last name as the blank lines will not properly adjust with the associated name. Basically I need to sort alphabetically Helper column 3 from above or the main database taking in to account the blank cells.

  • Nitin Salve says:

    Hi….Is there is possibility to Add more 3 or 4 columns along with Product Name, Sales Representative and Geography ?. If, Yes, Kindly request you please add 4 columns. Waiting for your new editions.

  • kross3 says:

    I guesst this is the formula I’ve tried:
    =IFERROR(INDEX(‘DUES MTH 1′:’DUES MTH 12′!$E$4:’DUES MTH 1′:’DUES MTH 12′!$AI$68,’DUES MTH 1′:’DUES MTH 12’!$C4, COLUMNS($B$5)),””)

    • kross3 says:

      This is what I’m trying to perform on B5 (Sheet 2):

      IF B2 = MTH (X) B5 =IFERROR(INDEX(‘DUES MTH (X)’!$E$4:’DUES MTH (x)’!$AI$68,’DUES MTH (x)’!$C4, COLUMNS($B$5)),””)

  • kross3 says:

    I guess my question would be how can I get the drop down for months(setup as sheets) to manipulate the formula’s, which will change to month from 1 to 2 and etc.

  • kross3 says:

    I’m trying to use this concept to display data from different sheets. My project is current using this concept to display data on for each person and each month. I found out if I use the following formula below I can get data to display for month 1 for each person, but I can figure out what formula I need to display data based on the month as well.

    =IFERROR(INDEX(‘DUES MTH 1′!$E$4:’DUES MTH 1′!$AI$68,’DUES MTH 1’!$C4, COLUMNS($B$5)),””)

  • steve says:

    hi

    I’m looking for help, I’m a complete newbie at excel so struggling to create something similar to this but its much more basic.

    i need 1 list (data validation) which i worked out how to do, and i need it to extract information from 1 row.

    there are no duplicates, no multiple entries.

    just a simple drop down list that brings up a few columns of data in a row.

    example:

    NAME l PHONE l ID Number l
    steven l 07827288292 l 4332 l

    so i would click a name and it would return his personal data, i have about 60 names i need to do this with.

    i really need your help with this.

    thank you

    Steven.

  • Jon says:

    Thanks for this. It is an excellent tool. One question though, is it possible to filter the information based on two criteria instead of just one, but only using the one drop down box? Using your example, if someone was the sales rep for India and China, then they’d appear if either of those options were selected from the one drop down box. If so, how is this done? If not, what would the workaround be? Your help would be greatly appreciated. Thanks!

    • Sumit Bansal says:

      Hello Jon.. I am not sure I get your question. Would be great if you could share some data or a snapshot of what you are trying to do

  • Sharmaine says:

    I tried this. however encountered some problem, in the example, I got product name on till Product16, I cant understand why? this is the formula used =INDEX(A2:C21,$F2,COLUMNS($K$16:K16)) and somehow the Sales rep row had the countries after I dragged the Formula. Another quik question : In the index formula why did you press F4 thrice for row number and how is that different from hard coding it once( Pressing F4 a single time)

    • Sumit Bansal says:

      Hello Sharmaine.. Try changing the formula to =INDEX($A$2:$C$21,$F2,COLUMNS($K$16:K16)). I believe you did not lock the range (A2:C21) which means that as you go down the row, it changes to A3:C22 and so on..
      By pressing F4 key, you change the reference style. For example, in a cell, if you have cell reference as A1, and you drag it down, the reference would change to A2. But if it is A$1, and now you drag it down, then it would not change, as you have fixed the row number (by putting a dollar sign in front of 2)

  • Sarah says:

    This is great, I was just wondering if there was an easy solution to having up to 100 rows of data, not just 20?

    • Sumit Bansal says:

      Hi Sarah.. Thanks for commenting.. You can extend this to as many rows as you want. All you need to do is change the cell reference. For example, if you want to do it for 100 records, change the formulas:

      In Helper 3: =IFERROR(SMALL($F$4:$F$103,E4),””)

      Formula to extract data (in J4 which can be copied/dragged to all other cells):
      =IFERROR(INDEX($B$4:$D$103,$G4,COLUMNS($J$3:J3)),””)

      • Sarah says:

        AHHHH excellent, I was missing the extraction change in J4.
        Thanks so much 🙂
        This is the best tool ever.

  • Evon Chew says:

    I have try all the formula including using the “All Country”.
    I try make it to be monthly updated data. The data will be increasing by monthly. (eg. from product 20 it will increase become until product 30, product 40 & etc). For sure when we select the data we need select until the last row in excel for example:
    =IFERROR(INDEX($B$4:$D$23,$G4,COLUMNS($J$3:J3)),””)
    it will becoming this formula:
    =IFERROR(INDEX($B$4:$D$65536,$G4,COLUMNS($J$3:J3)),””)
    When I select “All Country”, it does show all the details but after the updated data It will show 0 instead of blank cell at the bottom.

    • Sumit Bansal says:

      Hi Evon.. Thanks for commenting. Can you share the formulas that you are now using in the helper columns? Or share the file so that I can have a look (using Dropbox or onedrive)

  • Keelin says:

    Hi,

    Love this model and want to build something that may be able to handle up to 76 columns of criteria!! E.g. if I choose (e.g. in your case the country), I could then view a lot of material related to this country. Also would it even be possible to put the countries at the top and the profiling criteria down the column?

    Thanks so much,

    • Sumit Bansal says:

      Thanks Keelin.. Glad you found this helpful.

      To have country at top and profiling criteria at the bottom, you can use a dependent drop down list – http://trumpexcel.com/2013/07/creating-a-dependent-validation-drop-down-list/

      Hope this helps!!

      • Keelin says:

        Thank-you soo much, I am going to try this out now 🙂

        • Keelin says:

          Thank-you the dependant drop down is an inspired idea and sooo very very helpful.
          Now the next step!……….is there a way to only bring back certain columns of material? (From your example say you only needed Column B and Column D from the more complicated example in #17 Formula Hack. Essentially I need to be able to do the following:
          1. Make a selection from 3 dependant columns at the top (tick I can do this!!)
          2. Look up a database of 1200 rows with 87 columns of data (this is a summary sheet) the first 3 columns will contain data relevant for our dependant variable choices.
          3. Bring back information from 22-25 columns based on our selection (idea that this can be a snapshot profile summary of variables like cost factors, resourcing…etc.)

          • Sumit Bansal says:

            Hi Keelin.. One straightforward solution could be to use a helper column with True and False (True if all the three selections matches the content in the three columns). Now you just need to extract all data from rows that have True. Hope I have been able to explain myself 🙂

            Also, since you have a lot of data, I recommend use helper column approach instead of formula (as shown in Formula Hack #17).

          • Keelin says:

            Thank-you Sumit, I will attempt to use the helper columns and see how I go. On another note, I have a named range that I want to transpose, This is easy and can use an array formula something like this: =OFFSET(Governance,COLUMN()-MIN(COLUMN(HGov)),0)

            My conundrum is how to base the population based on a drop down box selection of list titles.

            1. I select governance from a drop down list of (e.g Governance, Finance, HR etc. )
            2. I can transpose the named ranges which will be titled Governance, Finance, HR etc.)

            I can type in the name of the list, e.g. Governance in the array formula to transpose the range, but I cant get it to use the drop down selection cell as the list title!

            Do you think you could help?

          • Keelin says:

            Have managed to do it by =IF($E$8=”Finance”,OFFSET(Finance,COLUMN()-MIN(COLUMN(HGovernance)),0),IF(E8=”Governance”,OFFSET(Governance,COLUMN()-MIN(COLUMN(HFinance)),0)))

            But its not very elegant to say the least ! If you have a better way do please let me know!!

          • Sumit Bansal says:

            Great!! This looks like a smart solution.. Glad it worked 🙂

          • Keelin says:

            Yep but I just found a problem!!! The horizontal row I am transposing to needs to cover 7 columns.

            My formula works beautifully when I select a function with 7 range criteria, but when I select a function with only 3 or 4 the array formula brings back more information than I need and is not bringing back a null or false value for the other 3 or 4 cells I shouldnt have range criteria for 🙁

            I’m nearly there but not quite! Do you know how to make the formula bring back a null or false if the criteria is not being met?

          • Sumit Bansal says:

            You can try IF formula. If the column number is greater than the number of elements in that named range, then it should return a blank (“”)

          • Keelin says:

            Hmmm, thanks its a great idea …although cant quite figure out how to make it work (i.e. to count the number of rows in the range!)

          • Sumit Bansal says:

            Try =CountA(Named Range)

  • Lawrence says:

    is there a way to show all information? ie. I will add “All Country” in the dropdown list.

    • Sumit Bansal says:

      Hello Lawrence.. Yes, you can do this by changing the formula in Helper Column 2 to =IF(OR(D4=$H$2,$H$2=”All Countries”),E4,””)

      Now when you select ‘All Countries’ from the drop down, all the countries will be displayed

      • Lawrence says:

        Thank you so much! You are a great help.

  • >