4

In Google Spreadsheets, I can use conditional formatting to color every other row in a table a certain color to make looking at wide rows easier. My problem is that I also want to use filter views so multiple people can sort the data differently simultaneously (which is why I can't just use basic filters), and the custom formatting is applied before the rows are filtered, which causes the filter views to have some adjacent rows be the same color, since they were originally an odd number of rows away. Is there any way to color the filtered rows differently or color them after the sort so colors would alternate?

1 Answer 1

3

One can have alternating coloring for filtered views that involve only hiding some rows. This can be done by using the command subtotal which ignores the cells hidden due to a filter.

You should have a column, for example A, which is non-blank in every row that contains data. Then apply conditional formatting to A1:Z (for example) with custom formula

=iseven(subtotal(3, $A$1:$A1))

The code 3 means "count nonempty cells in the range". I.e., like counta but with filtered-out cells hidden.


If the cells are also sorted in the filtered view, there is nothing to be done: since the sort happens after any formatting is done, there is no function that can see into the future and predict how the rows will be sorted. The only suggestion I have is the same as in my earlier answer: replace filtered view with sort and filter on another sheet. Then, the conditional formatting can be applied with the simple iseven(row()) and will remain alternating.

2
  • That's better than what I have. It handles not showing certain rows, but it doesn't seem to work for sorting the rows. Do you have anything that could fix that?
    – DeadEli
    Commented Aug 14, 2015 at 22:44
  • Ah, I missed that point. Unfortunately, no. Updated my answer.
    – user79865
    Commented Aug 14, 2015 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.