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().

The formula:

=COUNTA(Table1

[Data])=SUBTOTAL(103,Table1[Data])

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.

=IF(COUNTA(Table1[Data])=SUBTOTAL(103,Table1[Data]),”Unfiltered”,”Filtered”)

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.

=IF(ROWS(Table1[Data])<>COUNTA(Table1[Data]),”Blanks exist”,IF(COUNTA(Table1[Data])=SUBTOTAL(103,Table1[Data]),”Unfiltered”,”Filtered”))

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.

=SUMPRODUCT(–(SUBTOTAL(3,OFFSET(INDEX(Table1[Data],1,1),ROW(Table1[Data])

-ROW(INDEX(Table1[Data],1,1)),0))=1))=ROWS(Table1[Data])

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.

http://www.xldynamic.com/source/xld.SUMPRODUCT%20Part%202.html#visible

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.