Google Sheets Formatting – How to Color Every Other Row in Filter View

conditional formattinggoogle sheets

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?

Best 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.

Related Topic