Microsoft Office Tutorials and References

In Depth Information

**Using selected mathematical functions**

A MOD example

Here’s a practical use of the MOD function that you can ponder:

1.
Select a range of cells such as B5:F16, click Conditional Formatting on the Home

tab on the ribbon, and then click New Rule.

2.
Select the Use A Formula To Determine Which Cells To Format option in the Select

A Rule Type list.

3.
In the text box, type the formula =MOD(ROW(), 2)=0.

4.
Click the Format button, and select a color on the Fill tab to create a format that

applies the selected color to every other row. Note that if you select a single cell

in an odd-numbered row before creating this formatting formula, nothing seems

to happen, but if you copy or apply the format to other rows, you’ll see the result.

Click OK to accept the format, and then click OK to apply the formatting.

We clicked the Conditional Formatting button and clicked Manage Rules to display the

dialog box shown in the preceding figure. The MOD formula identifies the current row

number using the ROW function, divides it by 2, and if there is a remainder (indicating

an odd-numbered row), it returns FALSE because the formula also contains the

conditional test =0. If MOD returns anything but 0 as a remainder, the condition returns

FALSE. Therefore, Excel applies formatting only when the formula returns TRUE (in

even-numbered rows). For more information about conditional formatting, see “For-

matting conditionally” in Chapter 9, “Worksheet formatting techniques.” You can also

achieve similar results (with additional functionality) by converting the cell range into

a table and using the table formatting features. For more information, see Chapter 22,

“Managing information in tables.”