Color Alternate Rows In Excel

Let’s see how we can color each alternate rows in excel.

We could use the MOD and ROW function in excel to color every alternate rows or every nth row in excel.

We will see a simple example to understand that how we could do it with the data below.


Step 1

MOD function gives the remainder after a number is divided by a divisor and ROW function gives the row no of the cell.

Enter the formula =MOD(ROW(A2),2) in the cell B2 ,the first argument to MOD is the row number and second argument is 2 because we want to color every alternate rows, if we wanted to color every third row, we would have provided 3 as second argument to MOD function i.e. =MOD(ROW(A2),3).


Step 2

Drag the formula till where the data is available in column A.


Step 3

Now filter for 0 in column B as it signifies the alternate rows (every second row) and press Shift+Space bar to select the entire row and color them.



Similar to this, you could change the second argument to n to color any nth row, the row which has 0 is your target rows to color.

Hope this helped.

Share The Knowledge

Random Posts

Leave a Reply