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 StringDim strCri1 As String, strCri2 As StringApplication.VolatileWith Header.Parent.AutoFilterWith .Filters(Header.Column - .Range.Column + 1)If Not .On Then Exit FunctionstrCri1 = .Criteria1If .Operator = xlAnd ThenstrCri2 = " AND " & .Criteria2ElseIf .Operator = xlOr ThenstrCri2 = " OR " & .Criteria2End IfEnd WithEnd WithAutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2End 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)