Shading alternate rows or columns

This is simple enough, but surprisingly few people know how easy it is. It’s a question I get a lot. If you have headers, you can select a range and use a table style from the Styles tab on the Ribbon, (Format as Table). There are presets to choose from (Light, Medium, Dark).

Using a table has some advantages, like automatic column filters and totals, but some find this intrusive – you have to go along with Excel’s way of doing things. Further, if you need to return the table as a normal range, row banding colours are lost.

If you don’t want to go the way of Tables, here’s how to do it in Excel 2010:

1. Select the (entire) rows you want shaded, and click:
2. Conditional Formatting,

• Manage Rules
• New Rule
• Use a formula to determine which cells to format

This is the formula: =MOD(ROW(),2)=0

• Click Format
• Fill tab
• Select a fill colour
• Apply

It works the same way with columns: =MOD(COLUMN(),2)=0

Bio: This article was written by Jasper Cook, Excel guru and trombonist of note. Call or email us if KRS and Jasper can assist you with your Excel: enquiries@krs.co.dev