change language:

In Excel, conditional formats can be used to set rows to be colored alternately. For example, for the range B3:K13 to set rows to be colored alternately, you can do this by selecting this range and entering the following formula under conditional formatting:


Where B3 is the cell from the top left corner of the selected range. A suitable background color can then be selected for the layout.

rows alternate color 1

By adjusting the formula to:


the columns are colored alternately instead of the rows.

It is also possible to make a checkerboard pattern. This can be done by using the following formula with conditional formatting:


This will look like this:

rows alternate color 2

Although the latter formula looks quite complex, its operation is relatively simple. The background turns blue if one of the statements below is true:

  • The row number AND column number are EVEN
  • The row number AND column number are ODD

Otherwise, the background will remain white.

Questions / suggestions

Hopefully this article helped you to alternate color rows. If you have any questions about this topic or suggestions for improvement, please post a comment below.