How to alternate colors in Google Sheets

Looking at numbers and texts in a plain spreadsheet table can be an unpleasant experience.

Coloring alternate rows of your spreadsheet not only makes it look easy on the eyes but, most especially, more professional.

Shading alternate rows also make navigating through dozens of them a breeze.

Now, there are two ways to color alternate rows in Google Sheets.

2 Ways to Color Alternate Rows in Google Sheets

• Using the built-in function of Google Sheets
• Using a formula in the conditional formatting

The first method is the simplest way to color alternate rows. However, it is limited only to coloring every other row. Thus, you may find the second one more flexible, especially if you want to control the number of rows to be shaded.

To help you even further, we included links to the same exact spreadsheet we used in this guide down below. You may choose to use our sample spreadsheet so you could start following the steps right away.

Alternatively, you can also apply what you learned in this article to your own personal spreadsheet.

Why Should You Color Alternate Rows in Google Sheets?

Reading through the rows and columns is a tedious task that could lead to mistaking one row for another. Also, keeping your audience focused on your work is something you have to think about.

Considering that most of whats on your spreadsheet is going to be numbers, retaining the attention of your audience is crucial.

Coloring alternate rows of your spreadsheet is a great way to make it look easy on the eyes, or give an otherwise boring document a professional look. You want to color alternate rows of your spreadsheet when:

• You want to make your data easy to scan.
• You want to avoid making a simple mistake of confusing rows.
• You want to add a professional look to your spreadsheet.
• You need to look through a row where the starting column is already out of view.

This is why you should color alternate rows in your spreadsheet. Google Sheets has a handy tool to easily color alternate rows with a few clicks. Also, the styles and templates in Google Sheets will make coloring alternate rows a piece of cake.

Lets dive right into it.

Method 1: Color Alternate Rows Using Google Sheets Built-in Function

Google Sheets has built-in functions for most common tasks. In this case, well be going over the Color Alternate Rows function. As mentioned above, this method is the fastest way to get the job done. Its so simple you can actually complete it in 4 clicks.

Heres how you can do that.

Step 1: Open up your Google Sheets file.

First off, we need to open up a spreadsheet file and make sure we have a table in our spreadsheet. If you dont have one yet, you may create a copy of the document used in this guide. Alternatively, feel free to use your own personal spreadsheet if you have one that needs to be formatted. If youre unhappy with the color you chose or the format you used, dont worry as reverting these changes is fairly simple. Any damages should be reversible by simply pressing Ctrl + Z.

Step 2. Click on the table you want alternate rows shaded.

Click on or highlight the table where you want the coloring of alternate rows done. If you have multiple tables in one sheet, it is best to highlight the table you are formatting to avoid confusion. You may choose to not include the table heading if you have already formatting styles applied. Do not worry though because you will be given the option to include or exclude your headers in succeeding steps. In fact, you will also be given an option to include your table footer.

Tip: Selecting the whole table can also be done by holding down the CTRL + A keys. This automatically selects the table on focus.

Step 3. Open the Alternating Colors menu.

After selecting those cells, click on the Format tab near the top-left corner of your screen. From there, move your mouse all the way down and select Alternating colors. This will open a window where you can configure the color and format of the alternate rows.

The Alternating colors menu should appear on the right side of your screen.

On the menu, you can further customize the alternating colors of your table. You can change the range of the table in the field Apply to range. Under Styles, select Header or Footer if you want a darker shade on your header and footer, respectively. You may select both or neither. Google Sheets applies your changes as you select the style you want.

As you may have noticed, you can customize the colors and create your own style if you dont want to use the presets that are already there.

Once you are happy with your selection, click Done. Otherwise, click Remove alternating colors.

Thats it! Youre all set. We can now move on to the next method.

Method 2: Color Alternate Rows Using Formulas

This next method will guide you on how to specify the shading pattern for your set of rows. If you think coloring every other row isnt enough, this method gives you that flexibility. Although this is more confusing, as it involves formulas, well provide you with a detailed description of each parameter. This way, you can understand how to modify this to your own needs yourself.

Heres how you can do that.

Step 1: Open up your Google Sheets file.

Before anything else, we need to open up a spreadsheet file with a data table in it. You may choose to create a copy of the document used in this guide. Now, if you already have a spreadsheet ready, you can use that as well. The changes made in this step can be easily reverted, so you shouldnt have to worry about destroying your spreadsheet.

Step 2. Select the table you want alternate rows shaded.

Highlight the whole table where you want the shading applied. You can also select the whole table by pressing on the CTRL + A keys. This way, you let Google Sheets automatically select your table.

Step 3. Open the Conditional Formatting menu.

Once youve selected the necessary cells, go ahead and click on the Format tab near the top-left corner of your window. With the drop-down menu open, look for and select Conditional Formatting.

You should see the Conditional Formatting menu on the right.

Step 4. Insert the formula as a format rule.

Under Format rules, click on the drop-down list and select Custom formula is. Youll find this option at the bottom of the drop-down list. Insert the formula =MOD(ROW(), 2) in the Value or formula textbox.

Where:

= MOD() returns the remainder after the division operation

= ROW() returns the row number

2 is the divisor, diving the row number returned by the = ROW() function

Google Sheets should preview the table with shaded rows accordingly.

You can change the style by using the options under Formatting style. Here, you can change the color of the cell background and the format of your texts.

Click Done when finished.

Congratulations! You just used a formula to color alternate rows of your table. A few formula tweaks are provided down below to shade rows in a more elaborate manner.

Specifying Number Of Rows To Be Shaded

Since these are formulas, we can actually change the parameters to our liking. Try the following examples:

Example 1. =MOD(row(), 5) will shade every after four rows starting from the first row.

Example 2. =MOD(ROW()-2,4) >= 2 shades every 2 rows starting from the first row.

The MOD() and ROW() functions

Briefly, the MOD function returns the remainder of a division. For example, =MOD(6,2) returns 0 because 6 divided by 2 is exactly 3. This results in the remainder being 0. Likewise, =MOD(5,2) returns 1, since 5 divided 2 is 4, with the remainder being 1.

On the other hand, the ROW function returns the row number of a cell. Together, the formula simply tells Google Sheets to shade the ones with the remainder.

Alternative to the MOD() Function

Alternatively, you may use either the =ISEVEN(ROW()) or =ISODD(ROW()) formulas. As theyre names imply, =ISEVEN(ROW()) checks whether the row is even while =ISODD(ROW()) checks if the row is odd. Google Sheets then colors the row accordingly.

You will notice that these functions cannot be customized any further unlike the MOD() function.

Editing and Deleting Conditional Formatting Formulas

If you want to edit the formulas in the Conditional Formatting menu, open up the Conditional Format rules window. The steps are the same as when we opened it in the previous method.

Note, however, that the Conditional format rules will only be visible if theres a formula that has been previously placed.

Now, select the formula you want to edit. You will be directed to the Conditional Format rule window we had in the previous method.

Otherwise, click the Trash Bin button across the formula if you want to delete.

Conclusion

Youve finally reached the end of this article. Hopefully, weve helped you figure out how to color alternate rows in Google Sheets. Heres a quick recap of the things we covered today.

There are more than a hundred formatting tools to make your spreadsheet more professional looking.

Coloring alternate rows should always be on your priority list. Legibility is key when dealing with cells of numbers and texts.

Fortunately, Google Sheets has a quick fix for this with the Alternating colors built-in function. Another solution is to use conditional formatting, which utilizes customizable formulas.

We hope you found this article helpful!