Posts Tagged 'software'

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.


REPOST: Excel Graybar

Repost from my old blog so I can refer to this in a new post:

I really thought I had posted this, but I can’t find it. I like when printed Excel sheets have some formatting to let you scan the page easily, and graybar (or greenbar) is usually the best way to format. If you just need something very simple, probably the easiest way to get graybar in Excel is to use Conditional Formatting. Here’s what you do:

  1. Select the range you want formatted
  2. Click Format | Conditional Formatting…
  3. Change the dropdown to “Formula Is”
  4. Type in the formula: =MOD(ROW(), 2) = 0
  5. Click the “Format…” button and set your formatting – background color is on the Patterns tab, but you can apply any formatting you want… bold, outline, etc.

This will apply the formatting to even rows and not odd rows. If you add or remove rows, the formatting will adjust because it’s based on the row number. If you need the ODD rows to have the formatting, the formula is MOD(ROW(), 2) = 1. You can even click the “Add >>” button and use both formulas to apply different formatting to both even and odd rows.


July 2018
« Jun    


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