How To Highlight Every Second Row In Excel

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

Select column which you want to format.

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

Write formula: =MOD(ROW(),2)=1

Excel New Formatting Rule

Click Format. Choose your favorite colour. Click OK.

Excel Format Cells

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 favorite 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?

Tip-1 – Defining Own conditional Formatting

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

Excel Conditional Formatting Other Ways

Tip-2 – Highlight Every n 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)

Excel Conditional Formatting Third Fourth

Template

You can download the Template here – Download