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.

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

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

• After that, select cell G5 or anywhere you want to create the drop down filter.
• Next, select Data âž¤ Data Tools âž¤ Data Validation.

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

• Subsequently, press OK.
• Hence, itâ€™ll create the desired filter.

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

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

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

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.

• 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])`

• Press OK and itâ€™ll spill the unique product names.

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

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

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.

STEPS:

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

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

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

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

• 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``````

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

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

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

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

Advanced Excel Exercises with Solutions PDF