Use conditional formatting to change cell or font colors for data you want to stand out. Quickly apply conditional formatting with Excel’s built-in formatting rules. Or customize the formatting by adding a formula to a conditional formatting rule.

Shade Rows and Columns in Excel

An advantage of 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 adjusts to maintain the pattern.

The instructions in this article apply to Excel 2019, 2016, 2013, 2010; Excel for Microsoft 365, and Excel for Mac.

Shade 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 uses the formula:

Alternate rows are not the only option. By changing the conditional formatting rule slightly, the formula shades any pattern of rows. It also shades columns instead of rows.

To apply conditional formatting using a formula:

  • Open an Excel worksheet. To follow along with this tutorial, use a blank worksheet.
  • Highlight a range of cells in the worksheet.
  • Select Home.
  • Select Conditional Formatting.
  • Choose New Rule to open ​the New Formatting Rule dialog box.
  • Select Use a formula to determine which cells to format.
  • In the Format values where this formula is a true text box, enter the formula =MOD(ROW(),2)=0.
  • Select Format to open the Format Cells dialog box. Except on a Mac, where you select Format with.
  • Select the Fill tab and choose a color for the alternate rows. Select OK when you’re finished to return to the New Formatting Rule dialog box.
  • Select OK to close the New Formatting Rule dialog box and return to the worksheet.
  • The conditional formatting rule containing the formula is applied to the worksheet.
  • Alternate rows in the selected range are shaded with the chosen background fill color.

Interpreting the MOD Formula

The pattern designed depends on the MOD function in the formula. MOD divides the row number (determined by the ROW function) by the second number inside the brackets (2) and returns the remainder of modulus.

Open an Excel worksheet. To follow along with this tutorial, use a blank worksheet.

Highlight a range of cells in the worksheet.

Select Home.

Select Conditional Formatting.

Choose New Rule to open ​the New Formatting Rule dialog box.

Select Use a formula to determine which cells to format.

In the Format values where this formula is a true text box, enter the formula =MOD(ROW(),2)=0.

Select Format to open the Format Cells dialog box. Except on a Mac, where you select Format with.

Select the Fill tab and choose a color for the alternate rows. Select OK when you’re finished to return to the New Formatting Rule dialog box.

Select OK to close the New Formatting Rule dialog box and return to the worksheet.

The conditional formatting rule containing the formula is applied to the worksheet.

Alternate rows in the selected range are shaded with the chosen background fill color.

At this point, conditional formatting takes over and compares the modulus with the number after the equal sign. If there is a match (when 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. This is done because this row often contains headings that have their own formatting.

Shade Columns Instead of Rows

When you want to shade alternate columns, modify the formula used to shade alternate rows. Use the COLUMN function instead of the ROW function in the formula. The formula to shade columns is:

And the result looks like this:

Change the Shading Pattern

To change the shading pattern, change either of the two numbers in the formula.

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

Additionally, to change 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 are shaded. Change =0 to <3, and the shading is done in groups of three rows.

The number inside the brackets is called the divisor since it is the number that does the dividing in the MOD function. Dividing by zero isn’t allowed in Excel either. If you enter a 0 inside the brackets in place of the 2, no shading appears in the range.

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.

Get the Latest Tech News Delivered Every Day