A Complete 2024 Guide to Google Sheets Alternate Row Color

By

How to Color Alternate Rows in Google Sheets

There are times when you might need to highlight every other row in Google Sheets to make your data easy to read and analyze. Luckily there are plenty of Google Sheets alternate row color options in the format menu.

The first option uses the alternating colors menu in Google Sheets to color every other row. You can also use Conditional formatting to do this.

Coloring the alternate rows increases the readability of the data. These are also called the zebra lines. Below, we will show you how to highlight every other row in Google Sheets with examples.

How to Alternate Row Color in Google Sheets

The easiest way for Google Sheets to highlight every other row is to use the alternating colors option. Here’s how to make every other row shaded in Google Sheets:

  1. Select the data range.
  2. Go to Format > Alternating color.
  3. Choose a Default Style.
  4. Click Done.

That’s all you need to do in order to create alternating colors in Google Sheets. It’s a pretty simple process. Now let’s look at some examples of how to shade every other row in Google Sheets.

How to Color Alternate Rows in Google Sheets

Suppose I have a dataset as shown below:

Screenshot of example data sheet

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

  1. Select the cells in which you want to color the alternate rows. In this example, I want to highlight cells A1:D12.
Screenshot of our example sheet with the data range selected
  1. Go to Format –> Alternating colors.
Screenshot of Format --> Alternating colors menu
  1. A window will open to the right. Choose a default color scheme or your own color scheme in custom styles.
Screenshot of Alternating colors window. Choose a default color scheme or your own color scheme in custom styles.
  1. Click Done.
Screenshot of highlighted every other row in google sheets.

This should change the background color for every other row from the first row. With these steps, you will be able to highlight every other row in Google Sheets.

Consider a Course

While the first method was easy, some of the other ways to alternate colors in Google Sheets are a little more difficult. If you’re having trouble, you may want to try a comprehensive Google Sheets course to polish your skills first.

How to Color Alternate Using Conditional Formatting

Conditional formatting is a very versatile tool in Google Sheets that can be used to make heat maps. You can also use conditional formatting in Google Sheets to color every other row. In this case, you will need to use some custom formulas depending on how you want to color the rows. If you want to color from the first row, you should use the ISODD formula and if you want your color to start from the second row, use the ISEVEN formula.

Here’s how to alternate colors in Google Sheets using our example sheet:

  1. Select your data range.
  2. Go to Format > Conditional formatting.
Screenshot of Format > Conditional formatting menu
  1. Under Single color, go to the format rules drop-down menu
  2. Click on the ‘Format cells if’ drop down and select ‘Custom Formula is’.

  1. In the field below it, enter the formula =ISEVEN(ROW())
In the field below it, enter the formula =ISEVEN(ROW())
  1. Select the color for the alternate rows. You can either select the default option or access more Fill colors option from the paint bucket icon.
  2. Click Done.

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

Screenshot of he even rows (in the selected range) filled with the specified color.

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.

Google Sheets Alternate Row Color for Every Third Row

Conditional formatting can also be used in Google Sheets alternating colors every 3 rows. 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:

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

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

Screenshot of the example sheet with every third row (in the selected range) filled with the specified color.

Everything remains the same except the formula. Earlier, when highlighting 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..

Dynamic Alternating Rows

You can also create dynamic alternating rows that add or remove alternate colors every time you update your data.

To do this, you will have to use conditional formatting and a custom formula to create a dynamic color alternating row.

  1. Select the entire columns for the data range
Screenshot of our example sheet with the data range selected
  1. Go to Format > Conditional format.
Screenshot of Format > Conditional formatting menu
  1. Click Add rule.
  2. Go to Format rule and choose custom formula is
  3. Type the formula =AND(NOT(ISBLANK($A2)),ISODD(ROW()))
Go to Format rule and choose custom formula isType the formula =AND(NOT(ISBLANK($A2)),ISODD(ROW()))
  1. Choose a color for the alternative rows
  2. Click Done.

How To Alternate Colors In Google Sheets Columns

On special occasions, you may also want to alternate colors for every other column in Google Sheets. To do so, you will use the conditional format menu with the custom formula ISEVEN or ISODD with the COLUMN() formula.

  1. Select your data range
  2. Go to Format > Conditional formatting
Screenshot of Format > Conditional formatting menu
  1. Under Single color, go to the format rules drop-down menu
  2. Click on the ‘Format cells if’ drop down and select ‘Custom Formula is’.

  1. In the field below it, enter the formula =ISODD(COLUMN())
In the field below it, enter the formula =ISODD(COLUMN())
  1. Choose a color scheme
  2. Click Done.
With this formula, you will get all the odd numbered columns in your selected range highlighted.

With this formula, you will get all the odd-numbered columns in your selected range highlighted.

Frequently Asked Questions

How do I change the color of my alternate rows?

Changing the color of the alternate rows is possible, but it depends on the method you used to

To change the color of your alternate rows using alternating colors:

  1. Go to Format > Alternating colors.
  2. Go to Default styles.
  3. Choose a color from the default scheme.
Screenshot of Alternating colors window pane
  1. You can also create your own color scheme. You can choose a specific color for the header,  color 1, and color 2.
  2. Once you are finished, click Done.

Here’s how to change the color alternating using conditional formatting:

  1. Go to the Conditional formatting menu
  2. Click on the rule for the alternating color
Click on the rule for the alternating color
  1. Go to the Default style.
Go to the Default style.
  1. Click on the paint bucket option.
  2. Choose a color.
  3. Click Done.

How do I get rid of alternating row colors in Google Sheets?

There are different ways to remove Google Sheets every other row color, depending on the method you used.

If you used Alternating colors:

  1. Go Format > Alternating colors.
  2. At the bottom of the alternating colors menu, click ‘Remove alternating colors’.
At the bottom of the alternating colors menu click ‘Remove alternating colors’

If you used conditional formatting:

  1. Go to Format > Conditional format.
  2. Click the trash icon to delete the rule.

How do I color every third row in Google Sheets?

In this guide, we have shown you how to color every third row in Google Sheets. Simply follow the following steps:

  1. Select the cells in which you want to color the alternate rows.
  2. Go to Format –> Conditional formatting.
  3. Click on the ‘Format cells if’ drop down and select ‘Custom Formula is
  4. In the field below it, enter the formula =MOD(ROW(),3)=0
  5. Select the Color scheme..
  6. Click Done.

Conclusion

In this guide to Google Sheets alternate row color, we have shown you two ways to alternate row colors, The first method is using the Alternating color menu, and the second is using the Conditional formatting option.

If you find this guide useful, you can also check out how to sort by color in Google Sheets.

You May Also like the Following Google Sheets Tutorials:

Popular Posts