Shade Alternate Rows with Excel Conditional Formatting

Shading Alternate Rows with Conditional Formatting Forumulas in Excel
Shading Alternate Rows with Conditional Formatting. © Ted French

 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 normally this is done using Excel's preset conditions.

In addition to the pre-set options, 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 easier.

of 08

Excel Shading Rows/Columns Formula

Another advantage to using the formula to add row shading is that the shading is dynamic which means 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.

Note: Alternate rows is 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.

of 08

Example: Shading Rows Formula

The first step is to highlight the range of cells to be shaded since the formula only affects these selected cells. 

  1. Open an Excel worksheet—a blank worksheet will work for this tutorial
  2. Highlight a range of cells in the worksheet
  3. Click on the Home tab of the ribbon
  4. Click on the Conditional Formatting icon on to open the drop-down menu
  5. Choose New Rule option to open the ​New Formatting Rule dialog box
  6. Click on the Use a Formula to determine which cells to format option from the list at the top of the dialog box
  7. Enter the following formula into the box below the Format values where this value is true option in the bottom half of the dialog box
    =MOD( ROW( ), 2) =0
  8. Click the Format button to open the Format Cells dialog box
  9. Click the Fill tab to see the background color options
  10. Select a color to use for shading the alternate rows of the selected range
  11. Click OK twice to close the dialog box and return to the worksheet
  12. Alternate rows in the selected range should now be shaded with the chosen background fill color
of 08

Interpreting the Formula

 How this formula is read by Excel is:

  • the number  in the formula determines that the shading pattern repeats every second row in the selected range
  • 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.
of 08

What MOD and ROW Do

The pattern 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 and returns the remainder or modulus as it is sometimes called.

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.

For example, in the image above, when the last row in the selected range 18 is divided by 2 by the MOD function, the remainder is 0, so the condition of 0=0 is TRUE, and the row is shaded.

Row 17, on the other hand, when divided by 2 leaves a remainder of 1, which does not equal 0, so that row is left unshaded.

of 08

Shading Columns Instead of Rows

As mentioned, the formulas 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

Note: Changes to the shading rows formula for altering the shading pattern outlined below also apply to the shading columns formula.

of 08

Change the Formula, Change 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 a 3 or a 4.
of 08

Divisor can't be Zero or One

 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 zero inside the brackets in place of the 2, such as:

=MOD( ROW( ), 0) =2

you will get no shading at all in the range.

Alternatively, if you try to use the number one for the divisor so the formula looks like:

=MOD( ROW( ), 1) =0

every row in the range will be shaded. This occurs because any number divided by one leaves a remainder of zero, and remember, when the condition of 0=0 is TRUE, the row gets shaded.

of 08

Change the Operator, Change the Shading Pattern

 To really change up the pattern, change the conditional or comparison operator (the equal sign) 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.