How To Highlight Every Second Row In Excel

How To Highlight Every Second Row In Excel

Select column which you want to format.

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

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

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

Click Format. Choose your favorite colour. Click OK.

Cells are coloured. Conditional formatting started from first row.

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.

Here is what you will see:

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:

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)

Template

Further reading: 
Basic concepts 
Getting started with Excel
Cell References