Show Excel column filter information in cells

Solution 1:

I believe this is possible only with VBA.

I would also assume that you don't need to check if the autofilter is applied on the correct range (see Further Reading), and that you don't have to check if AUTOFILTER is active or not (i.e. no arrows, no autofilter).

Here's a user-defined function that checks if there are any active filters in a worksheet and, if there are any, displays which columns have been filtered. I'm sure there are better functions out there; you may need to modify it to suit your needs.

Function CheckFilters(r As Range) As String

Set AWS = r.Worksheet ' Refer the r RANGE, find which Worksheet it is on
fstate = ""

If AWS.FilterMode Then
    c = AWS.AutoFilter.Filters.Count

    'go through each column and check for filters
    For i = 1 To c Step 1
       If AWS.AutoFilter.Filters(i).On Then
            fstate = fstate & r(i).Value & ", "
       End If
    Next i

    'removes the last comma
    fstate = Left(fstate, Len(fstate) - 2) 
Else
    fstate = "NO ACTIVE FILTERS"
End If

CheckFilters = fstate

End Function

To use it, enter the ff. formula into an empty cell:

=CheckFilters(A3:E3)&LEFT(SUBTOTAL(9,A3:A48),0)

Where A3:E3 contains the column labels for your data (see example below)

The &LEFT(SUBTOTAL(9,A3:A48),0) portion of the formula serves no visible purpose other than to force the cell to re-evaluate "automatically" as you change the filters. (Credit to Sean Cheshire for suggesting it). Note this is better than the solution described here because it only recalculates when the range you have defined in your parameters changes.

EXAMPLES

With Filters:

AutoFilter is Enabled but No Columns are Filtered:

Used with Conditional Formatting:

FURTHER READING

How to use AutoFilters in Excel VBA Macros
AutoFilter Object (Excel 2003 VBA Language Reference)