Aung Shine
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.
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)
- Then, press Enter and use the AutoFill 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.
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.
- 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.
Read More: Create Excel Filter Using Drop-Down List Based on Cell Value
Similar Readings:
- How to Copy Filter Drop-Down List in Excel (5 Ways)
- How to Make Dependent Drop Down List with Spaces in Excel
- Create a Searchable Drop Down List in Excel (2 Methods)
- How to Create Excel Drop Down List with Color (2 Ways)
- Excel Drop Down List Not Working (8 Issues and Solutions)
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.
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.
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.
- 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.
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
- How to Create a Form with Drop Down List in Excel
- Hide or Unhide Columns Based on Drop Down List Selection in Excel
- How to Create Dependent Drop Down List with Multiple Words in Excel
- Remove Used Items from Drop Down List in Excel (2 Methods)
- How to Remove Duplicates from Drop Down List in Excel (4 Methods)
