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
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.
-
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?– DeadEliCommented Aug 14, 2015 at 22:44
-
Ah, I missed that point. Unfortunately, no. Updated my answer.– user79865Commented Aug 14, 2015 at 23:02