How to Check whether a Table is Filtered or not using Formulas

Posted on March 29th, 2010 in Excel Howtos , Learn Excel - 6 comments

Let us start the week with a simple formula (well, to be fair, let us start the week with a strong cup of coffee, then this formula).

Often when we have large data sets, we apply data filters to select and display only information we want to see.

Some of you know that whenever we apply filters on a dataset, we can look at status bar area to find out if any filter is applied on the current worksheet. It looks like this when filters are applied:

Data Filter Status in Status Bar Area of MS Excel

But, what if you need a way to show “filtering” status thru formulas?

Well, you can do that very easily by using two simple formulas called as COUNTA() and SUBTOTAL().

Here is how it works:

Filtered or not - Data Filter Formula Test in Excel

And the formula is, (assuming we want to test the range B4:B3000)
=IF(COUNTA(B4:B3000)>SUBTOTAL(3,B4:B3000), "list filtered ["&SUBTOTAL(3,B4:B3000)&" items]","Not filtered")

How does the formula work?

  1. We are asking excel to findout how many values are there in the range B4:B3000 using COUNTA() formula.
  2. Then we are asking excel to tell how many values are there after filter in the same range using SUBTOTAL() formula [introduction to excel SUBTOTAL formula].
  3. Now, if the first value is more than second, we knew that the list is filtered. So we are showing a message like,
    • “List Filtered [x items]”.

What would you use this type of formula for?

I have used this type of formula in charts, dashboards where I need to tell whether source data is filtered or complete. What about you? Where would you use this type of formula?

More: Excel Formula tutorials, Data Filter Tricks.

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

6 Responses to “How to Check whether a Table is Filtered or not using Formulas”

  1. Stef@n says:

    Hello Chandoo

    nice trick πŸ™‚
    another solution is: define a (public) function
    and: you can see >> it was filtered by xx criterions in yy-columns
    calling with: =AF_KRIT()

    Best regards
    Stef@n

    Public Function AF_KRIT()
    Dim intCol As Integer
    Dim intFilter As Integer
    Dim rngFilter As Range
    Dim strFilter As String
    Dim WS As Worksheet
    Application.Volatile
    Set WS = ActiveSheet
    If WS.FilterMode And WS.AutoFilterMode Then
    Set rngFilter = WS.AutoFilter.Range
    For intCol = 1 To rngFilter.Columns.Count
    With WS.AutoFilter.Filters(intCol)
    If .On Then
    If strFilter "" Then strFilter = strFilter & vbLf
    strFilter = strFilter & rngFilter.Cells(1, intCol) & ": " & .Criteria1
    Select Case .Operator
    Case xlAnd
    strFilter = strFilter & " UND " & .Criteria2
    Case xlOr
    strFilter = strFilter & " ODER " & .Criteria2
    End Select
    End If
    End With
    Next intCol
    End If
    AF_KRIT = strFilter
    End Function

  2. Kapil says:

    Hi Sir,
    I am learning lots of from your Blog . You are genius.

  3. Renato says:

    Hi Chandoo!

    Great post this "How to Check whether a Table is Filtered or not using Formulas". Acctualy I'm looking for something like this, but I'm wondering if there is a way to check if a filter is applyed to a cell. I mean, in this post you can see the results of applyed filter(s) on a cell based on the results in the colum, but we can note get a answer to which filter(s) was(were) applyed.
    If it were possible to check the cell where there is the filter to see which filter was appyed it would be great.
    By the way: I got my first contact with Excel in 1999 in a Company I worked as a Support Analist and MCT, I can remember asking a friend how to edit a cell, I had no idea at that time. Latter I got envolved with Excel and accept an invitation in 2007 to produce an E-course about Excel 2007 to begginers, and I did it, it were suppose to bring me some money, but brought me nothing, the guy at the site was not fair, nor honest, but I did a good job.
    Now I'm starting my first book, it's about Excel 2010, to be sold to friends and family. And as a writter I need sources to make a good book. Can I use the ideas posted here as a source of knowledge to help me write the book? What would be needed for me to use the information got here in a legal way? I mean, avoiding piracy. I hope I can count on your help. The book is not a big business, I want to print 200 copyies in B&W, but I want it can be very useful for people with questions about Excel 2010.
    I could make a cittation showing your web address with a link in the book for people interested in getting more info.
    Please comment, or send me an E-mail.
    Regards
    Renato from Brazil

  4. Sashko says:

    Dear Purna Duggirala,

    thank you for this article. Can you think of a non-VBA way to do another trick:

    Assume we have a data file with 2000 rows and 20 columns, which have to be checked. The columns are selectively filtered all the time, and a user is going there and back across the table heading to make sure the relevant filters are applied... Would be great to make it easier to visually identify the columns with applied filters by making them stand out. (Yes, there is a sign of a filter on the drop-down button, but it is not much help because the sign is grey and not remarkable).

    The trick you have described in this article is good to let user know if any filter is applied to the table. What I need is to let user know in which raw exactly the filters is applied.

    Any ideas? I would greatly appreciate!

    Thanks a lot.
    Sashko from Ukraine.

  5. Beverley says:

    Hi Chandoo,

    This has been extremely helpful.

    Do you know if its possible to highlight (conditional formatting) to show which filter has been applied?

  6. Imran says:

    Hello Sir,
    Could you please help about the filter command. The question is if we have long range data in excel and we applied the filter on one of the desired column and later on we forget that on which column exactly we applied the filter so how we can identify that this is the column we apply the filter on that or we have to check one by one.
    Please do not refer that remove the filter on entire sheet and reply the filter again.
    Waiting for you reply.
    Thank you.

    BR.
    Imran Lone

Leave a Reply