Tips: Highlight Alternate Rows In Excel…By Formula

Tips: Highlight Alternate Rows In Excel…By Formula
This post was previously published. It has been updated.

I work in IT. As a function of my job, I know a lot of little tips and tricks for many programs. Excel is one of my go-to programs, but I rarely use it for numbers – I generate code and do a lot of data work with it instead.

I recently had a long list of information to print out as reference material. It contained several columns, and I wanted a quick way to follow the information across the printed row without having to use an external guide.

I could have used “print gridlines” to put the grid lines on, but this only marginally improve readability.

I decided to color every other row in the selection a light grey. I could have done this by hand-formatting the rows, but I had thousands of lines to do. Here is what I did to highlight the cells using conditional formatting:

Highlighting Rows With Conditional Formatting

  1. Select all the cells you wish to change.

  1. Choose Conditional Formatting from the Home ribbon and choose New Rule.
  2. Select “use a formula” from the Rule Type.
  3. Enter “=MOD(ROW(),2)” in the formula box.
  4. Select the formatting by pressing on the format button.
  5. Click OK.

And you now have highlighted rows.


  1. LJ

    One of my excellent readers, Filipe from Brazil, pointed out the formula is “=mod(lin(),2)” for those people using the Portuguese version of Excel.

  2. Robert Parker

    Very cool. Works well and saves a lot of time. Thanks for this tip.

  3. Ronan Allen

    Very Useful

Comments are closed