Ask Your Question

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

asked 2020-05-23 12:37:38 +0200

kdev gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-05-23 13:53:08 +0200

Opaque gravatar image

updated 2020-05-23 14:04:22 +0200


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.

edit flag offensive delete link more


This does not work for me :(

Sergerator gravatar imageSergerator ( 2021-03-18 00:34:38 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-05-23 12:37:38 +0200

Seen: 758 times

Last updated: May 23 '20