Is there a way to see which filters are active in Excel, other than just the funnel icons?

Question

This question is actually for the Excel GUI, rather than Excel "programming", per se. However, if this is unavailable in the GUI, I would be curious if there's a VBA solution (although, I have basically 0 VBA knowledge/experience).

Is there a way to see which filters are active in Excel, other than just by looking at the funnel icons? As shown in my attached screenshot, some spreadsheets can have columns that extend off the visible screen, so it can be easy to miss the funnel icon indicating an active filter. (Additionally, I think it can be pretty easy to overlook the icon, even amidst only a few columns.)

Ideally, there would be some kind of list showing which columns/headers are actively filtered.

Thais

As far as I know you'll have to take the VBA route to visualize this information. Depending on the filters the solution to your post might even get a bit complex. It might be easy if the filter is just a simple list of selected items. But what about date filters such as between 01/01/2000 and 12/31/2000? Do you want to list (in this case) all the dates or do you expect to see this range? What about text filters such as Den*? In this case there might be once again a list such as Denver, Denton, Denison, etc. Do you want the list or do you expect all cities starting with Den? – Dasteel

@Dasteel The idea is simply to list which headers are actively filtered. I hope this addresses your questions. – Thais

Well, then I guess my answer addresses your post correctly. Just copy the code into an empty module and then use the new formula in any cell of your Excel file =FilterCrit(). – Dasteel

Answer 1

If you merely want a simple list of the columns where a filter is applied then the following VBA code may suffice:

Option Explicit

Function FilterCrit() As String

Dim i As Long
Dim ws As Worksheet
Dim Filter As String

'Application.Volatile

Set ws = ThisWorkbook.Worksheets(1)

If Not ws.FilterMode Then
    FilterCrit = "not filtered"
    Exit Function
End If
For i = 1 To ws.AutoFilter.Filters.Count
    If ws.AutoFilter.Filters(i).On Then
        FilterCrit = FilterCrit & "Filter on column " & i & Chr(10)
    End If
Next i

End Function

This will iterate the columns and if a filter is applied on any of these columns then it will be listed.

By default all UDFs user defined functions are not volatile and therefore do not automatically recalculate. Yet, you can force them to automatically recalculate with Application.Volatile. But it is highly recommend not to use this option as it can severely slow down your Excel file. Alternative solutions are recommended here: Alternative to Application.Volatile to auto update UDF

Dasteel

@Dasteel Does this function refresh automatically, or does it need to be executed manually? (I'm unsure if the automation is something that needs to be coded or if I'm missing something). Thanks for sharing, by the way. – Thais

@DanielFletcher I just updated the solution to address your question. Let me know if you have any further questions. – Dasteel

@Comintern Thanks for the note. I updated the answer. – Dasteel

Sorry for the delayed-by-a-month reply. I really like the auto-calculation with Application.Volatile. Any idea, in quantitative or more specific terms, how much it slows down an xls? Thanks. – Thais

@DanielFletcher That's a new question and much too complex to answer in a comment (or include it in the above answer). Post a new question and you shall see. If you believe that there is any relevance to this post / question then you can link it. But (if I am not completely mistaken then) the above code bears no real relevance to this new answer. The above UDF sample code might merely serve as an approximate guesstimate. I shall look forward to this new post and will try to do my best to come up with a fair answer. Yet, I believe that others might know better than me. – Dasteel

How can one apply the found filter to an identical table on a different sheet? – Spinel

Answer 2

This will work to highlight columns which contain active filter. This code just Bolds and sets the font color Red, but you can modify the style changes as per your need.

Sub test()
    Call markFilter(ActiveSheet)
End Sub


Sub markFilter(wks As Worksheet)

    Dim lFilCol As Long

    With wks
        If .AutoFilterMode Then
            For lFilCol = 1 To .AutoFilter.Filters.Count

                '/ If filter is applied then mark the header as bold and font color as red
                If .AutoFilter.Filters(lFilCol).On Then
                    .AutoFilter.Range.Columns(lFilCol).Cells(1, 1).Font.Color = vbRed
                    .AutoFilter.Range.Columns(lFilCol).Cells(1, 1).Font.Bold = True
                Else
                     '/ No Filter. Column header font normal and black.
                    .AutoFilter.Range.Columns(lFilCol).Cells(1, 1).Font.Color = vbBlack
                    .AutoFilter.Range.Columns(lFilCol).Cells(1, 1).Font.Bold = False
                End If
            Next
        Else
            '/ No Filter at all. Column header font normal and black.
            .UsedRange.Rows(1).Font.Color = vbBlack
            .UsedRange.Rows(1).Font.Bold = False
        End If
    End With
End Sub

Deify

Answer 3

Example

Sub Active_Filter()
    Dim Sht As Worksheet
    Dim lngCount As Long
    Dim i As Long

    Set Sht = ActiveSheet

    If Sht.FilterMode Then
        lngCount = Sht.AutoFilter.Filters.Count

'       // Go through each column look for active Filter
        For i = 1 To lngCount Step 1
            If Sht.AutoFilter.Filters(i).On Then
                Debug.Print "Filter is set on " & Sht.Columns(i).Address
            End If
        Next i

    End If

End Sub

Enforce

Answer 4

Data > Sort & Filter > Advanced : and your filtered column is in the middle of your screen.

Imelda

This solution does not work if there are multiple columns with filters applied. – Hako