Weekly Computer Tip # 266
5 April 2009

improve legibility of your workbooks
(Excel 2000-2007)

The other day I was struggling to read the data in an Excel spreadsheet. Even though I had put lines between every row I still had to use a piece of paper as a ruler to ensure I was matching up the data on the same line. (Been there, done that?)

In Excel 2007 you can very easily improve legibility using one of the predefined tables styles (simply click "Format as Table" in the Styles group on the Home tab). But the client was still using an earlier version so I suddenly remembered a great tip from Jonathan and Tony (thanks guys!) they sent about a year ago. At the time Jonathan asked whether I remember "stock listing" paper that mainframe computers used to churn out; we both did but for those of you who don't ... it had faint green horizontal lines to aid reading. Obviously you can do this manually in Excel but as soon as you would delete a row you have two rows with the same colour. So Tony came up with a cunning formula that works a treat in previous versions.

Here's how:

  1. On the worksheet, select the range of cells that you want to have the alternate row colours.
  2. Select the Format, Conditional Formatting command.
  3. In the Conditional Formatting dialog box, change "Cell Value Is" to "Formula Is".
  4. Type the following formula in the box to the right of "Formula Is": =MOD(ROW(),2)
  5. Click on the Format button.
  6. Select the Patterns tab and choose a (pale) colour.
  7. Click OK twice.

It's a great way to improve legibility in your older spreadsheets.

By the way, I just know that if I would send a tip next week I'll get hundreds of "out of office" messages, so I've decided to skip a week. Happy Easter!