Some four years ago, I posted on my old blog about making a nice alternating graybar in Excel with conditional formatting. Once every 6 months or so, I need to do it again, and look up my old post on the Wayback Machine since my old blog isn’t all that accessible at the moment. I decided that was crazy, plus I had the need for something a little more sophisticated.
Generalized, the formula is
=MOD(INT((ROW()-1)/N),2) = 1, where N is the number of rows in each group. Standard graybar is strictly alternating, so the group size is one, but division by one is boring/pointless, so you can leave that out. The -1 assumes you are starting with Row 1 and want N non-formatted rows first, then N formatted rows, etc. If your range is in the middle of the spreadsheet, fiddle with this number, though anything outside the range -N to +N is the same as something in that range (-N and +N are always the same configuration). To format the “base” rows use
<formula> = 0.
I had remembered reading an article on ALA regarding zebra striping and the observed effectiveness for tables on the web. It may not be that useful according the article, but sometimes it feels right.
Bonus – the article mentioned two color single striping, so I deduced a formula for that. This probably isn’t the best way, but it works nicely… What I came up with is
=MOD(ROW()-1,4) = 1 and
=MOD(ROW()-1,4) = 3. For the base row formatting here, use
=MOD(ROW()-1,2) = 0. Conditional Formatting is pretty powerful, so play around with it if the format you want doesn’t fit into these examples. Also, these formulas without the comparison at the end can be pasted directly into a spreadsheet to see how they evaluate. Enjoy.