It's ok, no zebras get hurt in this process, it's actually all built in to Excel's Conditional Formatting 🙂
Conditional Formatting - Shade Alternate Rows
To create this effect we need to create a new rule which uses a formula to determine which cells to format.
Conditional Formatting Using a Formula
- Select the cells you want to shade.
- On the Home tab of ribbon select Conditional Formatting > New Rule
- Select 'Use a formula to determine which cells to format' > enter your formula in the 'Edit the Rule Description' field.
- Click the Format button and from the Format Cells dialog box select the Fill tab > choose your weapon (colour, pattern, fill effect etc.):
For alternating bands your formula is =MOD(ROW(),2)
Conditional Formatting - Shade multiple rows
You can use a slight variation of the above formula to shade multiple rows like this:
To achieve the above effect you use this formula:
In this formula the 6 states how many rows in the set; 3 shaded 3 not shaded. And the 3 stipulates how many rows are shaded.
You can play around with these numbers to change the number of rows you want shaded and not shaded.
Conditional Formatting - Shade Alternate Rows in a Filtered Table
When your data is filtered you need a slightly different formula for your shaded bands to change as your list is filtered. Otherwise you'll end up with sporadic groups of shaded and unshaded bands.
The formula you want to use is:
Where cell M4 is the first cell in the header row of your table.
One alternative to the above examples (excluding the Filtered Table) is to simply use Excel's Table tool which automatically inserts shaded bands.
If you liked this trick please share it with your friends on Facebook.