Display AutoFilter Criteria
Use a custom function to display the criteria used for any column in a table with an AutoFilter applied.
Excel's AutoFilter is one of its most useful features, but one small drawback is that it's hard to tell the criteria being applied to the data at a glance. A custom function can display the criteria being used for each column of the table that has had an AutoFilter applied.
Let's say you have some data set up in a table and you have applied AutoFilters. First, you need to ensure that you have at least two rows above the table. Then, right-click on your sheet tab, select ViewCode (or press Alt/Option-F11), select Insert → Module, and paste the following code:
Function AutoFilter_Criteria(Header As Range) As String
Dim strCri1 As String, strCri2 As String
Application.Volatile
With Header.Parent.AutoFilter
With .Filters(Header.Column - .Range.Column + 1)
If Not .On Then Exit Function
strCri1 = .Criteria1
If .Operator = xlAnd Then
strCri2 = " AND " & .Criteria2
ElseIf .Operator = xlOr Then
strCri2 = " OR " & .Criteria2
End If
End With
End With
AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2
End Function
Close the window to get back to your worksheet and save your workbook. Now to add the custom function to each cell two rows above the column heading. Click in cell B1, enter the following formula, and press Enter:
=AutoFilter_Criteria(B3)
