Maybe you’ll never have the need for this, to use a formula to see if a range is filtered. Maybe you will. There are a few indicators we have already to tell if a range is filtered or not. We can look at the row headers which will turn to a blue font, filter drop down (assuming they’re showing) which shows a filtered icon, and the status bar which tells you how many records are showing out of the total count.
You can perform this on a standard range the same as you could a table, but using structured references ensures our ranges stay dynamic.
If these visual indicators aren’t enough for you, do not fear, we can work this out with a formula. This assumes you do not have blank cells in your data. This is not the same as =”” in a cell, that is a zero-length null string, which is a text value.
Since the SUBTOTAL() function will ignore filtered cells (can be used to ignore hidden rows as well), we will just check that vesus a formula that doesn’t ignore filtered/hidden ranges,COUNTA().
This says if I count all cells containing data in the column Data, does it equal the count of visible cells with data. We use COUNTA() versus COUNT() as to include non-numeric characters. Then we add an IF() formula around it to show what we want.
This says if the count of values matches the filtered count of values, show “Uniltered”, otherwise show “Filtered”.
To take into account blank cells, which will throw this formula off, we add another IF() function to test for this.
There is a function for counting blanks, COUNTBLANK(), where you can pass a range. We wouldn’t want to use it here because it counts zero-length null strings as a blank. Many people who write IF() or IFERROR() funtions use “” as a condition. An example would be IF(A1=”A”,”Yes”,””), whereby it wouldn’t show anything if a false condition was met. Instead we check the rows versus the absolute count of the range to better asses if there is blanks.
Another formula you could use is a SUMPRODUCT() and OFFSET() variant, and found in quite a few web searches when it comes to computing visible cells.
This example was derived from Bob Phillips’ website on SUMPRODUCT found in the below link with explanation. The condition added to the end =ROWS(Table1[Data]) is there to check visible cells against the rows in the table column.
In these example functions the table was generically named ‘Table1’. Change this for your table name, and change ‘Data’ for the column header of your choice.
This can actually be very helpful. I have seen many examples of where people had no idea that their records were filters, and/or they removed one filter, not knowing there were one or more others being filtered. A good tool for developers for sure.
Nice post ExcelAndAccess!!!
You might the VBA functions created by Stephen Bullen and myself to also be useful.
Hi David, nice to hear from ya. I’ve seen them and there’s excellent material there for sure. Thanks for the link!
Great trick with SUBTOTAL!
I think your formula does a little bit more than determining if a list is filtered or not. I think it tells if there are invisible rows within the table: filtered out OR hidden, because SUBTOTAL always excludes hidden rows. If I hide a row in the table, but not use filter, the formula would result “Filtered.”.
I think it would be better to say something like “All rows are visible” and “Something is hidden” instead of “Unfiltered” and “Filtered”.
We shared a post about working of SUBTOTAL on filtered and hidden rows here:
Hi Kris! Yes, I should have mentioned that. I meant to, because I had both examples in there, using 103 and 3 as function numbers. Oops!
Thanks for the link, it’s a good write-up. 🙂
Kris, that is a very interesting site. Thank you for your post and for the link. Do you want to add each other’s blogs to the blog roll?
Thank you for the appreciation.
We would be happy to add the blogs to each other’s blog roll!
and the FrankensTeam
I added your blog to our BlogRoll. Please let me know if you would like to have the anchor text edited, and if so what it should be.
Thank you in advance for adding our blog to your roll.