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: