Home > Support > Hints and Tips > Tip of the Week

Weekly Computer Tip # 266
5 April 2009

Start each week with a free tip

To subscribe, please type your email address in the box below:

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!

Until next week.

Karen
Email this page to a friend

Enter recipient’s email address in the box below:



PS I’m committed to sharing the best of what I know with others so please don’t keep me a secret. If you enjoyed today’s tip, please forward it to anyone you feel may benefit. Alternatively, feel free to reprint it (with full copyright and subscription information) in your newsletters and message boards.


Home   Training   Support   Clients   Contact Details   Virtual Press Room   Disclaimer  

© Roem Ltd. All Rights Reserved
April 2009