Tips: Highlight Alternate Rows In Excel…By Formula

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.

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

And you now have highlighted rows.