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 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 favourite colour. Click OK.
Highlighting Every Other Row
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 favourite colour.
Here is what you will see:
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:
Tip2 - 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)