How to highlight every second row?

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 favourite 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 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.

Excel Conditional Formatting Other Ways

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)

Excel Conditional Formatting Third Fourth