Show Excel column filter information in cells

We have a sheet with a huge number of columns and filtering is often used to navigate to the correct data. The problem is that sometimes its not obvious that the filter has been applied , the visual cue is very subtle. Is it possible to show some data via a formula or VBA about the filter inside another cell?

Something like this:

Just knowing if the filter is active would be a good help, knowing what columns have active filters applied to them would be icing on the cake. Ideally they update automatically.

I dont have ownership of the spreadsheet so cant make major changes to its structure or anything but VBA is fine.

Any ideas?

Answer

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 = ActiveSheet
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)