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: