How To Alternate Row Color in Google Sheets (Highlight Every Other Row With Ease!)

This article provides a comprehensive guide on how to add alternating colors in Google Sheets.

We will cover several different scenarios that may be useful for your projects. We will also cover every possible edge case you may be facing.

This tutorial should take you about 5 minutes to complete. If you’d like to feel free to copy this accompanying spreadsheet. If you do so you will be able to follow along with the examples below.

How To Apply Alternating Colors in Google Sheets

The are a few different ways to apply alternating colors. Let’s start with the simplest one that will highlight every other row and will also cover most of your use cases.

Example 1: How To Make Every Other Row Gray in Google Sheets (Or In Any Other Color)

In this section, I will show you how to shade every other row in Google Sheets. It is a very efficient way to make your data more readable and easier to digest.

It’s also a great feature if you want to format your data as a table in Google Sheets

Here are the steps you need to follow:

Step 1 (Optional): Open the “Exercise 1 – start” worksheet

The arrow on the screenshot points to the 'exercise 1 - start' worksheet where user can find data to follow the steps in the tutorials

PS. If you open the exercise sheet you will notice that the first row is pinned to the top. If you are wondering how to do that feel free to check this article where I explained how to freeze cells in Google Sheets

Step 2: In the Options Menu go to “Format” and select “Alternating colors”

There are two arrows on the screenshot. The first arrow is pointing to the 'Format' menu item, the second option is pointing to the 'alternating colors' option

Step 3: You will see a popup on the right-hand side of your screen.

First, select the range to be A1:D

Then you can customize the options. The default one is just going to make every other row gray.

If you have a header (like we do in the example) just make sure that you check the “header” option too.

The screenshot highlights the side pane that appears after clicking the 'alternate colors' menu item

Step 4 (Optional): You can customize the colors of your layout to be whatever you’d like. You can either pick a color from the default styles or use custom ones too.

That’s how the final output should look like:

The final effect shows every second line shaded in grey

As you can see it’s super easy to do and you should be able to format your data in less than 1 minute. Plus, if you add more entries to the sheet, later on, they will automatically be formatted as well.

The only downside is that any colors that were applied before formatting will be overridden.

PS. The data is now much easier to read and follow. If you’d like to make it even better check this article where I explained how to remove gridlines in Google Sheets

Example 2: How to alternate colors every 3 or 4 rows in Google Sheets

The example mentioned above will be enough in most cases but what if you want to color every second or even the third row? That’s also possible but requires a little bit more effort.

In this exercise, we will change the background color for every third row. Here’s what you need to do:

Step 1 (Optional): Open the “Exercise 2 – start” worksheet

The arrow on the screenshot points to the 'exercise 2 - start' worksheet where user can find data to follow the steps in the tutorials

Step 2: In the Options Menu go to “Format” and select “Conditional formatting”

There are two arrows on the screenshot. The first arrow is pointing to the 'Format' menu item, the second option is pointing to the 'conditional formatting' option

Step 3: In the pane that you will see on the right-hand side set the range to A1:D to include all rows in the sheet

Step 4: Now, find the “Format rules” section and set the “Format cells if…” to “Custom formula is”

Arrow pointing on 'custom formula is' option that will allow us to provide a custom formula for highlighting every 3rd row

Step 5: Paste the formula into the input that will appear below: =MOD(ROW(),3)=0 and apply some formatting styles

An arrow pointing to the formula that will return TRUE for every third row

Here’s a breakdown of the formula:

  • ROW() – gets the row number of each cell
  • MOD() – Returns the result of the modulo operator (simply speaking it is what reminds us what’s left after a division operation).
  • The background color will only be changed when the result of the above formula returns TRUE
  • So for the second row =MOD(2,3) will return 2 which is not equal to 0 therefore the result of the function will be FALSE
  • And for the third row =MOD(3,3) will return 0 which is equal to 0. That means the result of the function will be TRUE and these cells will be formatted

Step 6: Let’s optimize the formula!

As you can see the formula works perfectly well. Rows 3, 6, 9, 12 (and so on) are grey now. However, we didn’t consider the fact that we have a header in our table which we shouldn’t be considered in the calculations.

How can we fix it? It’s simple, we just need to subtract 1 (as we are offsetting the formatting by 1 row) from the general output.

Here’s our updated formula: =MOD(ROW(),3) - 1 =0

Note: This will also apply styles to the header now, so you may also wish to change the range to A2:D

There are two arrows visible on the screenshot. First one is pointing to the custom range which is 'A2:D' and the second row shows the alternated formula where we substract 1 to avoid selecting header

And this is what you should see as the final output you should be seeing:

The final effect that shows every 3rd row shaded out

Example 3: How to add multiple alternating colors in Google Sheets

Adding many alternating colors will be repeating the very same steps we covered in the previous section.

All you need to do is to apply another conditional formatting rule that will just target different cells.

For example, if you want to target every fourth row you need to alter your formula to =MOD(ROW(),4) - 1 =0 and set different formatting options

The screenshot shows two custom formulas applied to the conditional format rules. First one highlights every 3rd row in grey, second one highlights every 4rd row in light violet

And this is how the final effect should look like:

The final effect of the exercise with rows highlighted in different colors

Example 4: How to add alternating colors based on a value in Google Sheets?

If you want to highlight an entire row where a specific cell contains a specific value, there is a way to do so. We will rely on conditional formatting again and we will also learn a bit about absolute references.

Here’s what you need to do:

Step 1 (Optional): Open the “Exercise 4 – start” worksheet

Step 2: In the Options Menu go to “Format” and select “Conditional formatting”

There are two arrows on the screenshot. The first arrow is pointing to the 'Format' menu item, the second option is pointing to the 'conditional formatting' option

Step 3: In the pane that you will see on the right-hand side set the range to A1:D to include all rows in the sheet

Step 4: Now, find the “Format rules” section and set the “Format cells if…” to “Custom formula is”

Arrow pointing on 'custom formula is' option that will allow us to provide a custom formula for highlighting every 3rd row

Step 5: Paste this formula into the input that will appear below: = $D2 = "United States" and apply some formatting styles

Arrow pointing to the custom formula that will highlight all rows where D cell value equals to 'United States'

Chances are that you are wondering what the $ sign does. And that’s a brilliant question!

The $ sign before the column name tells Google Sheets to use that specific cell value for all cells in our data set.

For example, when the parser checks the A2 cell $D2 makes it possible to check the D cell value (which is “United States” and meets our criteria).

Without the $ sign, it would:

  • check the cell value (Linda Terry) against “United States” (that we provided in the custom formula field)
  • return FALSE.

PS I’ve covered a more complex example of using absolute references in the following article: how to insert and use checkbox in Google Sheets

And here’s the final output of our exercise:

The final output of our exercise where only 2nd and 10th rows are highlighted in green because these are the only entries containing 'United States' as a country

How to Remove Alternating Colors in Google Sheets

As you now know, there are two ways to alternate row colors in Google Sheets:

  • using the alternate color option
  • or (for more complex use cases) by applying conditional formatting with a custom formula.

The method you choose will affect how you remove colors that are alternate. Yet, the good news is that it will be a quick process either way:

Remove alternating colors created by the “Alternate color” option:

Step 1: In Options Menu select “Format” and then click on “Alternate colors”

Step 2: Remove the existing setting from your worksheet by simply clicking on “Remove alternating colors”

The screenshot shows how to remove alternating colors by clicking on the button at the bottom of the side pane

Remove alternating colors created by the “Conditional Formatting” option:

Step 1: Focus on a cell that has the conditional formatting applied

Step 2: From the Options Menu select “Format” and then click on “Conditional formatting”

Step 3: Remove the existing formatting from your worksheet by simply clicking on the trash icon next to your formula

The arrow on the screenshot is pointing to the remove rule trash icon which allows to remove conditional formatting rules

How To Apply Alternating Colors For Columns in Google Sheets

If you want to improve your data’s readability, you’ll usually need to alter row colors.

However, there are some exceptions where you may have to alter column colors instead.

Although that’s not a standard option, there is a way to achieve this effect. Here are the steps you need to follow:

Step 1 (Optional): Open the “Exercise 5 – start” worksheet

The arrow on the screenshot points to the 'exercise 5 - start' worksheet where user can find data to follow the steps in the tutorials

Step 2: in the Options Menu navigate to Format > Conditional formatting

There are two arrows on the screenshot. The first arrow is pointing to the 'Format' menu item, the second option is pointing to the 'conditional formatting' option

Step 3: In the format rules set the range to A1:D cells and set the “Format cells if…” to “Custom formula is”

Step 4: Add a custom formula to the input field

Use:

  • =ISODD(COLUMN()) if you want to start with the first column (column A)
  • =ISEVEN(COLUMN()) if you want to start with the second column (column B)
The arrow on the screenshot is pointing on the ISODD formula applied as a conditional format rule

Step 5: Click on done and check if your formatting is applied correctly

The final effect that shows every second column highlighted in light yellow

Some Questions You May Still Have

What To Do When Alternating Colors Option In Google Sheets Is Not Working?

If you’ve used the “conditional formatting” and your colors are alternating, it’s likely that the formula is not returning TRUE. If that is happening, double-check the formula to make sure it is returning the correct values

Summary

In this tutorial, we have covered 4 different ways of applying alternate colors to your worksheet.

We went through:

  1. a way to highlight every other row,
  2. the idea of how to add banded colors every nth row,
  3. a way to add multiple alternating colors,
  4. and how to highlight rows based on a specific cell value.

As you have seen, your options for creating the above are narrowed to two choices. In the simplest scenario the “Alternate colors” will be enough. For some advanced examples, you can use conditional formatting for alternating row colors.

Hopefully, I’ve covered everything you should know, however, if you have any questions feel free to let me know in the comments!

Leave a Reply

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