How to Alternate Colors in Google Sheets Quickly

colors have been alternated in google sheets

Sometimes it’s important to color the background of odd or even rows all at once or for specific rows only, and you can do those things in Google Sheets easily. Here’s how to alternate colors in Google Sheets quickly.

Alternating colors is a feature to quickly fill the entire rows of selected cells with particular background colors for the odd and even rows. This makes it easy to read and edit the content of the spreadsheet, especially when you have a lot of rows.

Another good thing about this that you can specify which rows you want to alternate colors for. For example, you can alternate colors for one row every five rows.

Alternating colors can also be used to make a table in Google Sheets to easily sort and manage your spreadsheet.

How to alternate colors in Google Sheets

Google Sheets directly provide its alternating colors feature in the menu, which you can access through the Format menu.

Simply select all cells you want to alternate colors for, including the row header.

how to alternate colors in google sheets

Click the Format, and select Alternating colors.

selecting format menu in google sheets to alternate colors

Here, you will see the right sidebar that shows you options to alternate colors, from which range of cells you want to select, whether you want to stylize the header or the footer, choosing the color and styles and more.

alternating colors for rows or columns

Select Header if you want to stylize the header, and Footer if you wish to. Below them, choose one of the default styles that are available for quick alternating colors. If the desired colors don’t exist, you can edit by clicking the three options of Header, Color 1, and Color 2.

changing the alternate colors style easily on google sheets

Choose the new color that will be selected for the option you choose. Google Sheets basically provide different alternating colors for the odd and even rows. The availability of the Header and Footer option here depends on whether you select them on the previous Styles section.

selecting colors

You can also add a custom style to create a new style from scratch. Simply click the plus button at Custom styles, and then it would double the current style.

creating a custom color for alternating colors

Once you’ve done alternating colors in Google Sheets, simply click Done at the bottom.

saving alternate colors in google sheets

You then will immediately see the resulting style based on the colors you’ve selected.

alternating colors in google sheets

How to alternate colors specific rows in Google Sheets

Sometimes you don’t want to color the odd and even rows. Instead, you want to color some specific rows in Google Sheets. Don’t worry, Google Sheets provide the feature to do that as well.

Select the range of cells you want to color. Click Format, and select Conditional Formatting.

conditional formatting to alternate colors for specific rows

The conditional formatting menu means literally what it is. It colors based on some conditions you can choose, and you can also use a formula to color specific rows.

Here, you can select the condition in Format cells if…. In this example, we’ll color every five rows on this spreadsheet, so I selected “Custom formula is”. To color every five rows only, you can use this formula:

=MOD(ROW(),5)=0

If you want to color a different number other than five, simply change it. Once you’ve put the formula, you can select the custom format or style you would like.

alternate colors for specific rows in google sheets

On another tab called Color scale, you can customize the formatting or stylize based on the value that is present on the cell. For example, you can color cells based on the minimum or maximum value of the entire range of cells.

color scale in alternating colors

You can also choose already-existing conditions here. This is a lot easier option for most people to color rows based on the value of the cell. For example, if the cell contains some numbers greater than you specified here, it will be colored differently.

choosing format

Let’s take a look at the conditional formatting in this example. It now colors only every five rows.

colors have been alternated in google sheets


Coloring rows in Google Sheets can be done manually, using conditional formatting to color rows based on the value that is present on the cell, or using the alternating colors feature if you want to color the odd and even rows automatically.

RELATED: How to Calculate the Standard Deviation in Google Sheets

Leave a Comment

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