How to Highlight Every Other Row in Excel

In this lesson you will learn how to highlight every second row. Excel will highlight cells automatically instead doing it manually.

Setting conditional formatting rules

Select the range of cells that you want to apply the formatting to.

Excel Select Column

Go to Ribbon. Home > Conditional formatting > New Rule.

Excel Conditional Formatting New Rule

Dialog box appears. Click Use a formula to determine which cells to format.

Excel Use a formula to determine which cells to format

In the Format values where this formula is true box, enter the formula =MOD(ROW(),2)=1

Excel New Formatting Rule

Click on the Format button to choose the formatting that you want to apply.

In the Format Cells dialog box, select the Fill tab and choose the color that you want to use for the highlighted rows.

Excel Format Cells

You should now see every other row in the selected range highlighted with the color that you chose.

Highlighting Every Other Row

Cells are coloured. Conditional formatting started from first row.

Excel Every Second Row Highlighted

Repeat. Go to Ribbon > Home > Conditional formatting > New Rule. Click Use a formula to determine which cells to format.

This time write formula: =MOD(ROW(),2)=0 and choose your second favourite colour.

Excel Conditional Formatting Rule Formula

Here is what you will see:

Excel Conditional Formatting CElls Highlight

Every cells has got its own colour green or blue. Isn’t it easy?

Tip1 – defining own conditional formatting

Of course, you can use your own conditional formatting. It can be also other kind of borders, colours, font style or pattern of cells. It’s your choice. Let’s prepare your custom conditional formatting like the ones I prepared:

See also  How to standardize data in Excel

Excel Conditional Formatting Other Ways

Your new conditional formatting rule should now be applied to the selected range of cells in Excel. You can create multiple rules for different conditions by repeating these steps for each new rule that you want to define.

Tip2 – highlight every Nth row

You can also highlight every third of fourth row. Just change the number in formula.

Syntax for highlight every third row is =MOD(ROW(),3)=0 or =MOD(ROW(),3)=1 or =MOD(ROW(),3)=2 or just =MOD(ROW(),3)

Syntax for highlight every fourth row is =MOD(ROW(),4)=0 or =MOD(ROW(),4)=1 or =MOD(ROW(),4)=2 or =MOD(ROW(),4)=3 or just =MOD(ROW(),4)

Click on the Format button to choose the formatting that you want to apply. For example, you can select a background color for the cells that you want to highlight.

Excel Conditional Formatting Third Fourth

Your conditional formatting rule should now be applied to every Nth row in the selected range of cells in Excel. Note that you can replace N with any number you want to highlight rows at a different interval.

Highlighting every other row in Excel can make it easier to read and organize large amounts of data. By using conditional formatting, you can quickly apply this formatting to a selected range of cells.

The steps to highlight every other row are simple and involve selecting the range of cells, choosing the Conditional Formatting option, creating a new rule using a formula, selecting a formatting option, and applying the rule to the range.