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

Let's keep in touch

We promise not to spam you; expect an email a month about what’s happening at KRS and our views on the software development industry in general.

By signing up for this newsletter I agree to krs.co.za’s Privacy Policy