Most of the time, conditional formatting is used to change cell or font colors in response to the data entered into a cell such as an overdue date or a budget expenditure that is too high, and usually, this is done using Excel's present conditions. However, it is also possible to create custom conditional formatting rules using Excel formulas to test for user-specified conditions.

One such formula that combines the **MOD** and **ROW** functions can be used to automatically shade alternate rows of data that can make reading data in large worksheets, much more manageable.

### Shading Rows/Columns in Excel

Another advantage to using a formula to add row shading is that the shading is dynamic, meaning it changes if the number of rows changes. If rows are inserted or deleted the row shading adjust itself in order to maintain the pattern.

Alternate rows are not the only option with this formula. By changing it slightly, as discussed below, the formula can shade any pattern of rows; it can even be used to shade columns instead of rows if you so choose.

**Shading Worksheet Rows in Excel**

The first step is to highlight the range of cells to be shaded since the formula only affects these selected cells. The instructions to shade rows with conditional formatting will use the following formula:

=MOD(ROW(),2)=0

- Open an
**Excel**worksheet—a blank worksheet will work for this tutorial. **Highlight**a range of cells in the worksheet.- Click on the
**Home****tab**of the ribbon. - Click on the
**Conditional Formatting**icon. - Choose the
**New Rule**option to open the**New Formatting Rule**box**.** - In the
**Style**box, select the**Classic**option. - Click on the
**Format only top or bottom ranked values**box to then change the value to**Use a formula to determine whch cells to format**. - Enter the formula given above into the box below the
**Use a formula to determine which cells to format**option. - Click the
**drop-down box**next to**Format with**to choose the color scheme for your alternating rows. - Click
**OK**to close the dialog box and return to the worksheet. - Alternate rows in the selected range should now be shaded with the chosen background fill color.

### Interpreting the MOD Formula

The pattern we designed depends on the **MOD** function in the formula. What **MOD** does is divides the row number (determined by the **ROW** function) by the second number inside the brackets (**2**) and returns the remainder or modulus as it is sometimes called.

=MOD(ROW(),2)=0

At this point, conditional formatting takes over and compares the modulus with the number after the equal sign. If there is a match (or more correctly if the condition is **TRUE**), the row is shaded, if the numbers on either side of the equal sign don't match, the condition is **FALSE** and no shading occurs for that row.

The condition of **=0** in the formula determines that the first row in the range is not shaded, which is done because this row often contains headings that have their own formatting.

**Choosing to Shade Columns Instead of Rows**

The formula used to shade alternate rows can be modified to allow for shading columns as well. The change required is to use the **COLUMN** function instead of the **ROW** function in the formula. In doing so, the formula would look like this:

=MOD(COLUMN(),2)=0

**Changing the Shading Pattern**

Changing the shading pattern is easily done by changing either of the two numbers in the formula.

- To have the row shading begin with the first row instead of the second, at the end of the formula, change
**=0**to**=1.** - To have every third or fourth row shaded instead of alternate rows, change the
**2**in the formula to**3**or**4**.

The number inside the brackets is called the divisor since it is the number that does the dividing in the **MOD** function. If you remember back in math class dividing by zero wasn't allowed, and it isn't allowed in Excel either. If you try to use a **0** inside the brackets in place of the **2**, you will get no shading at all in the range.

Additionally, to really change up the pattern, change the conditional or comparison operator (**=**) used in the formula to the less than sign (**<**). By changing **=0** to **<2** (less than 2) for example, two rows together can be shaded. Make that **<3**, and the shading will be done in groups of three rows.

The only caveat for using the less than operator is to make sure that the number inside the brackets is larger than the number at the end of the formula. If not, every row in the range will be shaded.