How to use Excel's conditional formatting to add zebra stripes to a spreadsheet

Reading large swathes of data in an Excel spreadsheet plays tricks on the eyes and makes mistakes more likely. But, as Paolo Lenotti of Excel with Business argues, using the conditional formatting function allows you to add zebra stripes and shading to make tables more readable

A colleague of mine once peered over my screen showing an Excel spreadsheet and exclaimed: ‘How do you do those zebra stripes?!’ It is simple and is a neat trick that allows you to shade tables of data quickly and elegantly.

By making use of the conditional formatting functionality, you can make tables more readable and impactful without time consuming manual formatting.

As an example, this forbidding data set:

Instantly becomes more accessible by adding zebra stripes:

The process is simple, comprising these five steps:

1. Select any cell within the unformatted table

2. Define a new conditional formatting rule

3. Set the rule to apply if the formula: =MOD(ROW(),2)=0 is true. (You can set the shading to apply every 3 – or more – lines by changing the ‘2’ in this formula to another number.)

4. Use the Format button to set the formatting that will be applied to alternate rows (in the above screenshot, blue shading).

5. Copy the formatting from the selected cell to the whole table using Format Painter

Excel with Business online training

AAT members have free access to this and many other Excel tips through their MyAAT account. Excel with Business‘s online course is designed to teach the features of Microsoft Excel that are useful to AAT members, saving time and offering a tailored learning experience.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.