fbpx

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

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

    You want to learn

    Welcome to Chandoo.org

    Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

    Read my storyFREE Excel tips book

    Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
    Rebekah S
    Reporting Analyst
    Excel formula list - 100+ examples and howto guide for you

    From simple to complex, there is a formula for every occasion. Check out the list now.

    Calendars, invoices, trackers and much more. All free, fun and fantastic.

    Advanced Pivot Table tricks

    Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

    Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

    9 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

    7. Rene says:

      Woo,, this is very very useful, my whole life been wondering how to find that column is filtered in that huge database,
      Best regards everyone.

    8. Mohit Singh says:

      Sir, In my excel sheet 200 columns and i first select all and filtered them then i will filter some 5 columns by different values like name, date,etc. so how can i find those columns which i filtered by different values in the total 200 columns. Please give me an answer.

    9. Harold Hawken says:

      I found the original formula to be effective:

      dim rngB as Range
      Set rngB = Sheets("Sheet1").Range("B2:B50000")
      x = WorksheetFunction.CountA(rngB)
      y = WorksheetFunction.Subtotal(3, rngB)

      However, if the AutoFilter is ON and nothing is filtered, x=y. I need to know absolutely if AutoFilter is ON or OFF.
      Thank you!

    Leave a Reply