Guide to Alternating Row Colors in Google Sheets

You might not be aware: There isn’t a straightforward method to alternate row colors in Google Sheets. However, you can utilize conditional formatting to achieve this. This coloring technique will enhance data readability for you.

Color Alternating Rows in Google Sheets

Guide on How to Easily Color Alternating Rows in Google Sheets

How to color alternating rows in Google Sheets includes the following steps:

Step 1: Select the data range you want to color, then choose Format and click on Alternating Colors.

Step 2: Choose the color scheme you desire, then click Done.

Formatting Conditional Colors in Google Sheets Using Formulas

Let’s start with a dataset like the following:

Select the Cells You Want to Color; Assume You Want to Highlight Cells A2:C10.

Navigate to the Format Tab > Conditional Formatting

In the ‘Conditional Format Rules,’ click on the Format cells if drop-down menu and select ‘Custom formula is.’

In the field below, enter the formula =ISEVEN(ROW())

Choose the formatting style. You can opt for the default option or access additional color choices from the toolbar below.

Click on Done.

It will color the even rows (within the selected range) with the specified color. And here is the result:

The key lies in the formula =ISEVEN(ROW()). It will return TRUE for all cells with even row numbers; the conditional formatting will check each cell for this formula, and any cell returning TRUE will be colored according to the specification.

Coloring Alternate Rows in Google Sheets

Suppose you want to color the 3rd row in the data table, and then every subsequent set of 3 rows should be colored once. Here’s how you can do it:

  1. Select the cells you want to color.
  2. Click on the Format tab > Conditional formatting.
  3. In the ‘Conditional Format Rules,’ click on the Format cells if drop-down menu and select ‘Custom formula is.’

(Perform similar steps as in the previous instructions but modify the formula.)

  1. In the field below, enter the formula =MOD(ROW(),3)=0.
  2. Choose the formatting style. You can opt for the default option or access additional color choices from the toolbar below.
  3. Click on Done.

You will see green color applied to every third row (within the selected range) in the specified area.

Everything remains the same except for the formula. Previously, when marking rows, we only had to check every second row, so the ISEVEN function worked well. In this case, however, you need to check every third row (which could be even or odd). Therefore, you need to use the MOD function.

The formula MOD(ROW(),3)=0 will return the remainder when divided by 3, and for rows that are multiples of 3, it will return 0. This is the condition you need to check in this conditional formatting custom formula.

For all cells returning 0, the formula =MOD(ROW(),3)=0 will return TRUE, and that cell will be colored with the specified color. You can use the same approach to highlight every 4th, 5th, 6th row, and so on.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top