Highlight Every Other Row in Excel

Highlighting every other row in a spreadsheet makes data easier to read. It does that by adding visual contrast. Let’s learn how to highlight every other row in Microsoft Excel.

How to Highlight Every Other Row in Excel

Consider a long list of data like this one. As you can tell, it can be a little tough to read. That’s especially true if you’re sharing it virtually, for example. It will appear smaller to your audience.

This is a great time to highlight every other row of your data. One row will remain with the default white background, while the next will be shaded in a different color. You might think you have to do this with the Fill feature, line by line. But thanks to Excel, the spreadsheet will do the work for you.

Begin by highlighting your dataset. On the Home tab, in the Styles group, click on Conditional Formatting. Then, from the dropdown, choose New Rule.

You’ll see a window appear, called New Formatting Rule. In this case, you’ll want to click Classic, and then on the bottom option: Use a formula to determine which cells to format.

Next, under Format values where this formula is true, you’ll need to input a quick formula. Begin by typing =MOD. Since you want every other row to be highlighted, continue with (ROW(),2). Your complete formula should read:

=MOD(ROW(),2)

When finished, click on CustomFormat, then Fill. You’ll see a series of theme color thumbnails. Click one to apply it as a highlight, or choose More Colors to customize your own hues. Be sure to choose a light shade that won’t make cell contents hard to read.

Finally, click OK. As you can see, Excel will instantly highlight every other row of your data.

This quick edit makes your data look cleaner and easier to read.