How to AutoFilter Rows Based on Cell Values in Excel?

Generally, in Excel, we can always apply a filter to a list just by using a slicer, and we can click on the cell value you want to filter internally. But if you want to apply the filter based on a cell that is not inside the table, we cannot do it with the help of Slicer. We can complete this process with the help of the VBA application. We can apply the filter using an external value.

This tutorial will help you understand how you can auto-filter rows based on a cell value in Excel.

Step 1

Here we will insert the VBA code for the sheet, then click Enter. Let us see a straightforward process to know how we can auto-filter rows based on a cell value in Excel. We will be using the help of the VBA application to complete our process. We will be filtering the data based on where the results fail.

To begin, right-click on the sheet name and select View Code to open the VBA application, or use the command Alt+11 to open the VBA application and type the programme in the text box as shown in the image below.

Program

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated By Nirmal
   If Target.Address = Range("E2").Address Then
      Range("A1:C16").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
   End If
End Sub

In the program, A1:A16 is the range on which you want to apply the filter, and E1:E2 is the range of cells you want to apply the filter based on.

Step 2

Now save the sheet as macro enabled and close the vba application using the command "Alt + Q", and when you click on Enter from the cell "E1", then the data will be filtered automatically as shown in the below image.

Conclusion

In this tutorial, we used a simple example to demonstrate how you can autofilter rows based on a cell values in Excel.