Creating a Drop Down Filter to Extract Data Based on Selection in Excel

The Filter feature in Excel helps us to extract data according to our selections. But the problem with the feature is that the row numbers and the cell references remain unchanged. Creating a simple drop-down filter will solve this problem. In this article, we’ll show you the effective ways for Creating a Drop Down Filter to Extract Data Based on Selection in Excel.

To illustrate, we’re going to use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales of a company.

creating a drop down filter to extract data based on selection


Download Practice Workbook

Download the following workbook to practice by yourself.


4 Ways for Creating a Drop Down Filter to Extract Data Based on Selection in Excel

1. Create a Drop Down Filter to Extract Data Based on Selection with Helper Columns in Excel

In our first method, we’ll introduce 3 helper columns to create the Drop Down Filter. Therefore, follow the steps below to perform the task.

STEPS:

  • First, select cell D5 and type the formula:
=ROWS($B$5:B5)

Create a Drop Down Filter to Extract Data Based on Selection with Helper Columns in Excel

  • Then, press Enter and use the AutoFill tool to complete the series.

Create a Drop Down Filter to Extract Data Based on Selection with Helper Columns in Excel

  • After that, select cell G5 or anywhere you want to create the Drop Down Filter.
  • Next, select Data Data Tools Data Validation.

Create a Drop Down Filter to Extract Data Based on Selection with Helper Columns in Excel

  • As a result, the Data Validation dialog box will pop out.
  • Under the Settings tab, select List in Allow, and type TV, AC in the Source box.

Create a Drop Down Filter to Extract Data Based on Selection with Helper Columns in Excel

  • Subsequently, press OK.
  • Hence, it’ll create the desired filter.

Create a Drop Down Filter to Extract Data Based on Selection with Helper Columns in Excel

  • Now, select cell E5 and type the formula:
=IF(B5=$G$5,D5,"")
  • Press Enter.

Create a Drop Down Filter to Extract Data Based on Selection with Helper Columns in Excel

  • Afterward, fill the rest with AutoFill.
  • Then, select F5. Type the formula:
=IFERROR(SMALL($E$5:$E$10,D5),"")
  • Press Enter.

Create a Drop Down Filter to Extract Data Based on Selection with Helper Columns in Excel

  • Again, use the AutoFill tool to complete the rest.
  • Next, select cell I5 and type the formula:
=IFERROR(INDEX($A$5:$C$10,$F5,COLUMNS($I$5:I5)),"")
  • Press Enter.

Here, the COLUMNS function returns the number of columns in the range $I$5:I5. The INDEX function returns the cell reference or cell value present at the intersection of the row number given in F5 and the column number given in I5. The IFERROR function returns blank cells if an error is found in the expression.

  • Finally, use the AutoFill tool to complete the series. Thus, you’ll get your desired data based on your selection.

  • Similarly, choose AC from the Drop Down Filter, it’ll automatically update the dataset.

Read More: How to Create Drop Down List with Filter in Excel (7 Methods)


2. Excel FILTER Function for Creating a Drop Down Filter to Pull Data Based on Selection

We’ll use the FILTER function in this method to Create a Drop Down Filter to Extract Data Based on Selection in Excel. So, learn the below steps to carry out the operation.

STEPS:

  • Firstly, select the range A4:C10.
  • Then, under the Insert tab, select Table.

Excel FILTER Function for Creating a Drop Down Filter to Pull Data Based on Selection

  • As a result, a dialog box will appear. There, select OK.
  • It’ll automatically create a table with the name Table1.
  • Now, open a blank sheet and select cell B2. Type the formula:
=UNIQUE(Table1[Product])

Excel FILTER Function for Creating a Drop Down Filter to Pull Data Based on Selection

  • Press OK and it’ll spill the unique product names.

Excel FILTER Function for Creating a Drop Down Filter to Pull Data Based on Selection

  • Subsequently, select cell E5 or any other cell of the main sheet.
  • After that, go to Data Data Tools Data Validation.

Excel FILTER Function for Creating a Drop Down Filter to Pull Data Based on Selection

  • Consequently, the dialog box will pop out.
  • Under the Settings tab, select List in Allow, and the Source box, type the formula:
=list!$B$2#

Here, the list is our newly created sheet name. It’ll look for the B2 cell value in the sheet list.

  • Afterward, select cell G5. Here, type the formula:
=FILTER(Table1,Table1[Product]=E5)
  • Press Enter and It’ll spill the data.

Here, the FILTER function filters Table1 and returns the dataset that matches cell E5.

  • You can change the Drop Down Filter to AC and get your data based on your selection.

Read More: Create Excel Filter Using Drop-Down List Based on Cell Value


Similar Readings:


3. Extract Data Based on Selection by Creating a Drop Down Filter with Excel INDIRECT Function

We can pull data upon selection from multiple sheets using the Excel INDIRECT function. For example, in the following dataset, we have 2 sheets: Sheet1 and Sheet2 which contain the data. We’ll extract the Total Sales upon our sheet selection in this method. Hence, follow the process for pulling the Total Sales value only based on our selection.

Extract Data Based on Selection by Creating a Drop Down Filter with Excel INDIRECT Function

STEPS:

  • In the beginning, select cell C4 in the sheet where we want to place the extracted data.

Extract Data Based on Selection by Creating a Drop Down Filter with Excel INDIRECT Function

  • Subsequently, select Data Data Tools Data Validation.
  • In the pop-out dialog box, select List in Allow. In the Source box type the formula:
=$C$8:$C$9

  • Then, press OK.
  • Now, select cell C6 and type the formula:
=INDIRECT("'"&C4&"'!C11")
  • Press Enter and it’ll pull the Total Sales from the sheet mentioned in cell C4.

  • Lastly, change the sheet using the Drop Down Filter. You’ll see the desired change in cell C6.

Read More: How to Extract Data Based on a Drop Down List Selection in Excel


4. VBA to Create a Drop Down Filter to Extract Data Based on Selection in Excel

We’ll apply VBA code in our last method for Creating a Drop Down Filter to Extract Data Based on Selection in Excel. Therefore, learn the process to know how to perform the task.

STEPS:

  • Firstly, we have data in sheet vba1.

VBA to Create a Drop Down Filter to Extract Data Based on Selection in Excel

  • And we have the Drop Down Filter in sheet vba2. Now, we want to filter the data in the vba1 sheet according to our Drop Down selection in the vba2 sheet.

VBA to Create a Drop Down Filter to Extract Data Based on Selection in Excel

  • Next, right-click on the sheet vba2 like it’s shown in the image below. There, select View Code.

VBA to Create a Drop Down Filter to Extract Data Based on Selection in Excel

  • As a result, the Module window will pop out.
  • Copy the below code and paste it there.
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Range("A2"), Target) Is Nothing Then
        Application.EnableEvents = False
        If Range("A2").Value = "" Then
            Worksheets("vba1").ShowAllData
        Else
            Worksheets("vba1").Range("A2").AutoFilter 1, Range("A2").Value
        End If
        Application.EnableEvents = True
    End If
End Sub

VBA to Create a Drop Down Filter to Extract Data Based on Selection in Excel

  • Then, press F5, and the Macros dialog box will pop out. Here, type VBA in the Macro Name.
  • After that, press Create.

VBA to Create a Drop Down Filter to Extract Data Based on Selection in Excel

  • Again, press F5 and select Run.

  • Afterward, close the window. From the Drop Down Filter select TV.

  • Consequently, you’ll see the filtered data in sheet vba1.

  • Likewise, select AC from the Drop Down Filter in the vba2 It’ll return the extracted data.

Read More: VBA to Select Value from Drop Down List in Excel (2 Methods)


Conclusion

Henceforth, you will be able to Create a Drop Down Filter to Extract Data Based on Selection in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung Shine

Aung Shine

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo