Shading Alternate Rows

Shading alternate rows is a very common task for many spreadsheet users. Simple as it might seem, I have been asked many times by my colleagues about how this is to be done. In this tip, I show how the underused Format Painter tool is just what is required to accomplish this. The steps are as follows:

1. Shade the first couple of rows
2. Use the format painter to copy the formats of the shaded rows
3. Drag to select the rows to be shaded.

The drawback of this tip is that the shading may be destroyed if rows are deleted, but the process can be repeated again in that event. See the video below.

How to Quickly Shade Alternate Rows in Excel - A funny movie is a click away

Contribution from visitors

I am grateful to Alexander Weinder from Germany who contributed this spreadsheet where Conditional Formatting is used to create a variety of shaded cells. He uses the MOD and INT functions in the conditional formatting formulae. Each sheet on his spreadsheet shows a different shading, created using different formulate. See the pictures below.

Shading rows with conditional formatting