How to Color Alternate Rows in Google Sheets

How to Color Alternate Rows in Google Sheets

There is no straightforward way to color alternate rows in Google Sheets, however, you can use Conditional formatting to do this.

Coloring the alternate rows increases the readability of the data. These are also called the zebra lines.

Color Alternate Rows in Google Sheets

Suppose I have a dataset as shown below:

Color Alternate Rows in Google Sheets - Data

Here are the steps to color alternate rows in Google Sheets:

  • Select the cells in which you want to color the alternate rows. In this example, I want to highlight cells A2:C10. Color Alternate Rows in Google Sheets - Data Selected
  • Go to Format –> Conditional Formatting.Color Alternate Rows in Google Sheets - Data Conditional Formatting
  • In the Conditional Format Rules pane, click on the ‘Format cells if' drop down and select ‘Custom Formula is'.Color Alternate Rows in Google Sheets - custom formula is
  • In the field below it, enter the formula =ISEVEN(ROW())
  • Select the Formatting Style. You can either select the default option or access more Fill color options from the toolbar below it.Color Alternate Rows in Google Sheets - format
  • Click Done.

That's it. It would fill the even rows (in the selected range) with the specified color.

Color Alternate Rows in Google Sheets - colored rows

How does it work?

The entire magic in the formula =ISEVEN(ROW())

It returns true for all the cells where the row number is even. Conditional formatting checks each cell for this formula and any cell that gets a TRUE is filled with the specified color.

Color Every Third in Google Sheets

Let's use the above logic and extend it to highlight every third row in a data set. Something as shown below:

Here are the steps to do this:

  • Select the cells in which you want to color the alternate rows.
  • Go to Format –> Conditional Formatting.Color Alternate Rows in Google Sheets - Data Conditional Formatting
  • In the Conditional Format Rules pane, click on the ‘Format cells if' drop down and select ‘Custom Formula is'.Color Alternate Rows in Google Sheets - custom formula is
  • In the field below it, enter the formula =MOD(ROW(),3)=0
  • Select the Formatting Style. You can either select the default option or access more Fill color options from the toolbar below it.Color Alternate Rows in Google Sheets - mod fomrula
  • Click Done.

This would fill every third row (in the selected range) with the specified color.

Color Alternate Rows in Google Sheets - highlighted 3 rows

Everything remains the same except the formula. Earlier, when highlight alternate rows, we just had to check for every second row, hence the ISEVEN function worked fine. In this case, we have to check for every third row (which could be even or odd). Hence, we use the MOD function.

=MOD(ROW(),3) function returns the remainder when divided by 3. For rows that are multiples of 3, it would return 0. And this is the condition that we check in the conditional formatting custom formula.

For all the cells that return 0 as the remainder, the formula =MOD(ROW(),3)=0 returns TRUE, and that cell gets highlighted with the specified color.

You can use the same logic, to highlight every 4th, 5th, 6th, row and so on..

You May Also like the Following Google Sheets Tutorials:

>