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:
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:
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?
- We are asking excel to findout how many values are there in the range B4:B3000 using COUNTA() formula.
- 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].
- 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?
9 Responses to “How to Check whether a Table is Filtered or not using Formulas”
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
Hi Sir,
I am learning lots of from your Blog . You are genius.
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
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.
Hi Chandoo,
This has been extremely helpful.
Do you know if its possible to highlight (conditional formatting) to show which filter has been applied?
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
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.
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.
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!