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

Get FREE Advanced Excel Exercises with Solutions!

The Filter feature in Excel helps us to extract data according to our selections. However, 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 of 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


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

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:

=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 Fill Handle 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 Make Multiple Selection from Drop Down List in Excel


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 a Searchable Drop Down List in Excel


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


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

We’ll apply the 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.


Download Practice Workbook

Download the following workbook to practice by yourself.


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo