How to apply alternate row background colors? And how to apply this in tables that already have conditional formatting?

How can I get alternate row background colors like this:

image description

(Note the alternate row background on the 1st table and the alternate col background on the 2st table)

I also have some tables with conditional formatting (ex: for colA, if AnyCell=“a” apply StyleX. “StyleX” change both Cell font and background). Can I add alternate row background colors to them too? Preferably an easy way to do this…


Q1 How to apply alternating rows?

  • Define 2 cell styles with your desired background (e.g. BGGrey and BGWhite)
  • Format -> Conditional -> Condition...
  • Condition 1: Formula is ISEVEN(ROW()) Apply Style: BGGrey
  • Condition 2: Formula is ISODD(ROW()) Apply Style: `BGWhite?
  • Range: A2:AMJ1048576 (assuming row 1 contains a header)

In case you what that ro apply for columns just replace function ROW() by COLUMN().

Please note This reduces direct formatting options of cells (e.g. in this case you can’t change the background color any longer, since conditional formatting takes precedence).

Q2 Add alternating rows colorization to existing conditional formatting possible?

Yes - but this requires additional effort, since you would need to exclude the ranges of existing conditional formatting from the ranges above in Q1 and add the Q1 rules after the existing conditional formatting to assure precedence of rules.

This does not work for me :frowning: