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:
- First, select cell D5 and apply the ROW function:
=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.
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.
- 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 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.
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.
Related Articles
- How to Create Drop Down List in Multiple Columns in Excel
- How to Add Blank Option to Drop Down List in Excel
- How to Create a Form with Drop Down List in Excel
- How to Remove Used Items from Drop Down List in Excel
- How to Remove Duplicates from Drop Down List in Excel
- How to Fill Drop-Down List Cell in Excel with Color but with No Text
- [Fixed!] Drop Down List Ignore Blank Not Working in Excel
- How to Autocomplete Data Validation Drop Down List in Excel
- Hide or Unhide Columns Based on Drop Down List Selection in Excel
