Skip to content

Extract Data Based on a Drop Down List Selection in Excel | Filter Data With Drop Down List Excel

     

    In this video I demonstrate how to filter or extract data based on a drop-down list in Excel. This method is only available to Excel 365 users. The functions used in this video are: UNIQUE, SORT and FILTER.

    Click here to download the sample file.

    House Your Data in an Excel Table

    If you’d prefer to follow the video to see how to create a table, start here https://youtu.be/2LChz9U83OM?t=36

    Your first step is to house your data in an Excel table. This will create a dynamic range, so when you add new records they will be included in the extracted data.  To house your data in a table:

    1. Click in any cell in your data
    2. Use the keyboard shortcut CTRL T
    3. In the Create Table dialog, click OK
    4. On the Table Design tab on Excel’s ribbon, in the Properties group (far left) enter a name for the table in the Table Name box.  Don’t include spaces in a table name.  In the video I called the table SalesData.

    Create the Drop-Down

    If you’d prefer to follow the video for creating the drop-down, start here https://youtu.be/2LChz9U83OM?t=77

    In the video I created a drop-down including a list of branch names.  The first step for creating the drop-down is to create a list of all the unique branch names in the SalesData table. Do this on a new worksheet.

    In cell A1 type the following =SORT(UNIQUE(SalesData[Branch]))

    The second step is create the drop-down itself.

    1. On the worksheet containing the SalesData table, select the cell where you want the drop-down to appear, in the video I selected cell I2.
    2. Select the ribbon’s Data tab and in the Data Tools group, click the Data Validation button
    3. On the Setting tab, select List from the Allow: drop-down
    4. Click into the Source: box, select the worksheet containing your list of unique branches, click in the first branch name and type a # after the cell address, eg =List!$A$1#  The # symbol indicates you are referring to the whole list starting in cell A1
    5. Click on OK to confirm

    You should now have a drop-down list of branch names.

    Extract Records Based on the Drop-Down

    If you’d prefer to follow the video for extracting records, start here: https://youtu.be/2LChz9U83OM?t=209

    To extract records based on a drop-down you can use the FILTER function.

    Start off by copying the column headings from the SalesData table and then select the cell immediately below your first copied column heading.  In that cell write the following formula:

    =FILTER(SalesData,SalesData[Branch]=I2)

    I2 is the cell containing the drop-down.

    Using More Than One Drop-Down to Extract Records

    You can extract records based on multiple drop-downs if required.  If you’d prefer to follow the video for this part of the tutorial, please start here https://youtu.be/2LChz9U83OM?t=325

    In the video I create an additional drop-down for brand and included this drop-down in the criteria for extracting records.  To achieve this, follow these steps:

    1. Create the unique list of brands in the same way as you created the unique list of branches – you can do this on the same worksheet as the branch list
    2. Create the drop-down for the brand names in the same way as you created the drop-down for the branches.  In the video I put this second drop-down in cell L2.
    3. Change the filter formula to include your additional criteria (as below):
      =FILTER(SalesData,(SalesData[Branch]=I2)*(SalesData[Brand]=L2))Multiplication is used to express AND criteria in the FILTER function and each criteria needs to be enclosed in round brackets.