Shade Alternating Rows of a Spreadsheet

BY STANLEY ZAROWIN


How do you set up a worksheet with alternating colored rows like the traditional columnar pad design?

 There are many ways, but the easiest, for both Excel 2003 and 2007 is with a Conditional Formatting formula. Follow these steps:

In Excel 2003, position your cursor at A1 and press Ctrl+A to select all the cells on the worksheet. Or, if you just want a portion of the worksheet to be alternately colored, highlight your target range and go to the taskbar and select Format and Conditional Formatting and under Condition 1, press the down arrow and choose Formula Is and in the next box enter =MOD(ROW(),2) and click on Format and go to the Patterns tab and select your color and click on OK.

Bonus: If you subsequently add or delete columns within that range, the color pattern will automatically adjust to the size you select. And if you replace the word ROW in the formula with COLUMN, the color scheme will be installed for every other column instead of every other row.

In Excel 2007, follow the same basic procedure. On the Home ribbon, go to Conditional Formatting and New Rule.

Click on Use formula to determine which cells to format and enter the same formula as above (see screenshot below).

 

 

 

 

Then click on Format, the Fill tab and select a color.

SPONSORED REPORT

Keeping client information safe in an age of scams and security threats

A look at the Dirty Dozen tax scams and ways to protect taxpayer information.

TAX PRACTICE CORNER

More R&D tax help

"Can I use the R&D credit?" PATH Act enhancements make the credit more attractive to a wider range of taxpayers.

QUIZ

Learn to choose between ‘who’ and ‘whom’

Writers can stumble over who and whom (or whoever and whomever). If you write for business, this quiz can help make your copy above reproach.