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.”