Excel Graybar, with feeling!

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.

Advertisements

1 Response to “Excel Graybar, with feeling!”


  1. 1 Eric March 28, 2014 at 8:57 am

    Love it, Matt. Thanks!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Posts

October 2009
S M T W T F S
« Dec   Nov »
 123
45678910
11121314151617
18192021222324
25262728293031

del.icio.us bookmarks

Thanks

I would like to thank Mike Hellrich for hosting a couple redirects for me while I sort out my web server problems.

%d bloggers like this: