In Google Sheets, it seems that masking an odd number of rows in a range somehow defeats the alternate color formatting (adjacent rows will occur with identical colors). Is there a workaround using, e.g. conditional formatting instead?
2
-
With "masking a row", are you referring to hiding a row?– doubleunaryOct 30, 2022 at 12:34
-
Likely. I am using the French localization of Sheets which has the "Masquer" option in the local menu. The result is actually hiding the row!– jmichelOct 30, 2022 at 12:53
Add a comment
|
2 Answers
I guess that you could make a conditional formatting with the personalized formula option and put something like:
=odd(row(A1))
You should replace A1 with the first cell of the range you're working with. Then every odd row will be applied the formatting you choose If this doesn't help, share a sample spreadsheet ;)
Edit: just corrected the formula.i forgot the row part
-
This does not seem to work if some rows are hidden (I wrote "masked"), which was the meaning of my question. Hiding rows does not affect the numbering.– jmichelOct 30, 2022 at 13:08
-
I'm sorry, didn't understand before what you meant with masked. Later I'll try some little coding– MartínOct 30, 2022 at 14:37
-
odd()is not the right function to use in this context. You probably meanisodd(). That does not detect hidden rows though. Oct 30, 2022 at 17:27 -
Yes, you're right. Isodd was the right option, and I didn't understand what he meant with masked, I thought that it was done kind of formatting like passwords that somehow interrupted the zebra format. Idiomatic things of two non English speakers!– MartínOct 30, 2022 at 17:32
That can be done by using a helper column. Put this formula in cell H1 (in row 1):
=sequence(rows(A1:A))
You can then format a range with zebra stripes with this conditional formatting custom formula rule:
=isodd(subtotal(103, $H$1:$H1))
In $H$1:$H1, you should replace the two instances of 1 with the row number of the first row in the range you are formatting.
Note that the helper column should not be be hidden, although rows within that column may get hidden per your preference. You can resize the helper column to a minimal width of 2 if it gets in the way.
answered Oct 30, 2022 at 13:24
-
Nice option, didn't know the use of subtotal! I'm guessing that the H column could be replaced with any column that would always have some value in it (would not be empty), right??– MartínOct 30, 2022 at 18:04
-
I meant that the helper column wouldn't be necessary if there is another column that's always non blank– MartínOct 30, 2022 at 20:52
-
The
isodd(subtotal())formula requires a helper column. It is irrelevant whether the helper column is an existing column or a new column, as long all cells in the column are non-blank. Oct 30, 2022 at 20:59 -
Great, it was for my own clarifying and for @jmichel if that spreadsheet already has a full column– MartínOct 30, 2022 at 21:05
-
My spreadsheet does have a full column, which I cas use a the helper column. It solved my problem.Great trick !– jmichelOct 30, 2022 at 22:36