Microsoft Office Tutorials and References

In Depth Information

**Conditional formatting formula examples**

FIGURE 19.24

Using conditional formatting to apply formatting to alternate rows

Alternate row shading can make your spreadsheets easier to read. If you add or delete rows

within the conditional formatting area, the shading is updated automatically.

This formula uses the
ROW
function (which returns the row number) and the
MOD
function

(which returns the remainder of its ﬁ rst argument divided by its second argument).

For cells in even-numbered rows, the
MOD
function returns
0
, and cells in that row are

formatted.

For alternate shading of columns, use the
COLUMN
function instead of the
ROW
function.

19

Creating checkerboard shading

The following formula is a variation on the example in the preceding section. It applies

formatting to alternate rows and columns, creating a checkerboard effect.

=MOD(ROW(),2)=MOD(COLUMN(),2)

Shading groups of rows

Here’s another row shading variation. The following formula shades alternate groups of

rows. It produces four shaded rows, followed by four unshaded rows, followed by four more

shaded rows, and so on.

=MOD(INT((ROW()-1)/4)+1,2)=1