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
