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 the range of cells that you want to apply the formatting to.
Go to Ribbon. Home > Conditional formatting > New Rule.
Dialog box appears. Click Use a formula to determine which cells to format.
In the Format values where this formula is true box, enter the formula =MOD(ROW(),2)=1
Click on the Format button to choose the formatting that you want to apply.
In the Format Cells dialog box, select the Fill tab and choose the color that you want to use for the highlighted rows.
You should now see every other row in the selected range highlighted with the color that you chose.
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:
Your new conditional formatting rule should now be applied to the selected range of cells in Excel. You can create multiple rules for different conditions by repeating these steps for each new rule that you want to define.
Tip2 – highlight every Nth 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, and the 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.
Click on the Format button to choose the formatting that you want to apply. For example, you can select a background color for the cells that you want to highlight.
Your conditional formatting rule should now be applied to every Nth row in the selected range of cells in Excel. Note that you can replace N with any number you want to highlight rows at a different interval.
Highlighting every other row in Excel can make it easier to read and organize large amounts of data. By using conditional formatting, you can quickly apply this formatting to a selected range of cells.
The steps to highlight every other row are simple and involve selecting the range of cells, choosing the Conditional Formatting option, creating a new rule using a formula, selecting a formatting option, and applying the rule to the range.
