Search

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

Share

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?

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.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### 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

Excel School made me great at work.
5/5

– Brenda

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.

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.

### Announcing Power BI Dashboard Contest (win \$500 prizes!)

Hey there, I have a SUPER exciting announcement! April is about to get a whole lot sweeter with our Power BI Dashboard Contest! Your mission, should you choose to accept it: Craft the most EPIC dashboard for the Awesome Chocolates CEO with sales & financial insights! Winners stand a chance to score up to \$500 in Amazon Gift Cards, plus some serious bragging rights!

## Related Tips

Learn Excel

Excel Howtos

Learn Excel

Learn Excel

Excel Howtos

Excel Howtos

### 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.
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,

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.
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!

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.