MS-Excel / Functions and Formula

Highlight Every Other Row or Column

You've surely seen Excel spreadsheets that have alternating row colors. For instance, odd-numbered rows might be white, while even-numbered rows might be gray. Conditional formatting makes this easy.

Alternating colors or shading looks professional and can make data easier to read. You can apply this formatting manually, but as you can imagine, or might have experienced, it's a rather time-consuming task that requires constant updating as you add and remove data from the table. It also requires infinite patience. Fortunately, conditional formatting can reduce the amount of patience required and enhance your professional image.

We'll assume your data occupies the range A1:H100. Select this range of cells, starting with A1, thus ensuring that A1 is the active cell in the selection. Nowselect Home → Conditional Formatting → NewRule → "Use a formula to determine which cells to format," and type the following formula in the "Format values where this formula is true" box (pre-2007, go to Format → Conditional Formatting... → change "Cell Value Is" to "Formula Is"):

=MOD(ROW( ),2)

Click the Format button and choose the format you want to apply to every second row. Click OK, and then click OK again. The format you specified should be applied to every second rowin the range A1:H100. You also should have some patience left for the rest of the day.

If you need to apply this to columns rather than rows, use this formula instead:


Although this method applies the formatting specified to every second row or column quickly and easily, it is not dynamic. Rows containing no data will still have the formatting applied. This looks slightly untidy and makes reading the spreadsheet a bit more difficult. Making the highlighting of every second row or column dynamic takes a little more formula tweaking.

[Contents] [Next]

In this tutorial:

  1. Highlight Every Other Row or Column
  2. Highlighting Dynamically