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:
- Select the (entire) rows you want shaded, and click:
- 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