Filter more obvious

Question

I know that a filtered list has rows that are blue, a drop down with a filter on it and the filter number in the lower status bar, but is there anything else that I can do to automatically make it even more obvious that a file has a filter on it?  Thanks for any tips.

Answer 1

You can use the CountA and SubTotal functions in Conditional Format because COUNTA counts all cells in the range and SUBTOTAL only counts the visible cells so when they are not equal, Filtering has been applied.

Select the entire AutoFilter range (without any filters applied)

Select Conditional Format -> New Rule

Select "Use a formula to determine which cells to format" and enter the following formula where column A is one of the filtered columns.

=COUNTA($A:$A)<>SUBTOTAL(3,$A:$A)

Click the Format button and set the required format.

Answer 2

I was about to come back and edit my post when I see you have already answered.

If the number of rows in the AutoFiltered range is variable then you could select the entire columns initially but the formula needs to be a little different so that it does not format the blank rows below the data.

If selecting the entire columns then use the following formula where A1 is the first cell of the selection and column A is a column in the selected range. Note that while the NOT(ISBLANK(A1)) is applied to only the first cell of the selection, excel automatically applies the condition to the remaining cells in the selection.

=AND(COUNTA($A:$A)<>SUBTOTAL(3,$A:$A),NOT(ISBLANK(A1)))

Answer 3

You can use the CountA and SubTotal functions in Conditional Format because COUNTA counts all cells in the range and SUBTOTAL only counts the visible cells so when they are not equal, Filtering has been applied.

Select the entire AutoFilter range (without any filters applied)

Select Conditional Format -> New Rule

Select "Use a formula to determine which cells to format" and enter the following formula where column A is one of the filtered columns.

=COUNTA($A:$A)<>SUBTOTAL(3,$A:$A)

Click the Format button and set the required format.

 

 

How do you figure this stuff out?  Amazing!  Thanks.