In this article:

Google Sheets Color Every Other Row: The Step-By-Step Guide

In this article we will show you how to color every other row in Google Sheets to help organize and manage your data in just a few simple steps.

Google Sheets Color Every Other Row (Static Range)

1. Highlight the entire table

Select the data range either by click-dragging through it or by identifying the top-leftmost cell and bottom-rightmost cell.

google sheets color every other row, select table

2. Click Format 

Format is located along the top row of Google Sheets controls.

google sheets color every other row, click format

3. Select Alternating colors

google sheets color every other row, select alternating colors from format

The table becomes:

google sheets color every other row output

Screencap:

google sheets color every other row screencap

How to Change Color of Alternating Colors

After selecting Alternating colors in the Format option of the main menu, a sidebar labeled Alternating colors will appear on the right side of Google Sheets. It contains options for changing the colors of every other row. 

google sheets options to change color every other row

Default styles

You can select one of the default styles by clicking on one of the options inside the highlighted section:

google sheets color every other row, alternating colors, default color styles

How to Create Custom Color Style for Alternating Colors

You can also set your own custom color styles by clicking on the pallet for each element of the style:

google sheets color every other row, alternating colors, custom styles

1. Select the range of the table you want to format
google sheets color every other row, select table to customize style

2. Click the palette for each element and set the color
google sheets color every other row, custom styles, palette select color

3. Click the small box with plus sign above the elements to save the style

google sheets color every other row, custom styles, save custom style

4. Click Done

The table now looks as follows:

google sheets color every other row, custom styles output

Google Sheets Alternate Row Color (Dynamic Range)

If you want to apply an alternate row color style to a dynamic data range that is constantly being updated, you can use conditional formatting. This means that blank rows don’t get alternate row colors, keeping the sheet neat. Here are the steps:

1. Select the data range


Select the data range either by click-dragging through it or by identifying the top-leftmost cell and bottom-rightmost cell.

For bigger ranges, you can just select the entire columns that contain the data. 

google sheets alternate row color, select range with header row

If you have a header row and want to color it separately, do not include it in the range. For our example, we want to set a different color to the header. 

google sheets alternate row color, select range without header row

Screencap:

google sheets alternate row color, select range without header row screencap

2. Click Format then select Conditional formatting

Make sure the range is highlighted when you do this step.

google sheets alternate row color, open conditional formatting

Screencap:

google sheets alternate row color, open conditional formatting screencap

3. Add the formula in Conditional Formatting

The Conditional Formatting sidebar will appear. Select Custom formula is under Format rules:

google sheets conditional formatting alternating row color, select custom formula option

On the textbox that will appear, add the following formula:

=AND(NOT(ISBLANK($A2)),ISODD(ROW()))

The $A2 marks the top-leftmost cell in the range. Change it depending on the actual range of your data.

google sheets conditional formatting alternating row color, add custom formula 

Screencap:

google sheets conditional formatting alternating row color, add custom formula screencap

4. Change fill color

Click Fill color icon under Formatting style label in the sidebar, then select the color from the palette.

google sheets conditional formatting alternating row color, select custom color using palette

Screencap:

google sheets conditional formatting alternating row color, select custom color using palette screencap

5. Click Done

If you include the header row in Step 1, you will get the following result:

google sheets conditional formatting alternating row output with header

If you did not include the header row, you will get the following:

google sheets conditional formatting alternating row output without header

To shade the header row, do the next step.

6. Optional: Set header row color through Fill color 

Select the header row, then click Fill color icon in the main toolbar, then select the color from the palette.

google sheets conditional formatting alternating row, select header row and row color using palette

Screencap:

google sheets conditional formatting alternating row, select header row and row color using palette screencap

You can see how the conditional formatting changes when the number of rows with value changes.

google sheets conditional formatting alternating row output demonstration screencap

Use our capex expenditure approval software to easily automate capex approvals from your spreadsheet in just a few clicks. 

If you enjoyed this article, you might also like our article on how to use Google Sheets custom formula conditional formatting or our article on how to use Google Sheets conditional formatting if another cell contains text.  

If you want to learn how to use Google Sheets data validation email, we also suggest checking out our detailed guide.

Other Google Sheets Formatting Articles

-How to Strikethrough in Google Sheets

 

-How to Remove Gridlines in Google Sheets

 

-How to Underline in Google Sheets

 

-How to Hyperlink in Google Sheets

 

-How to Add Bullet Points in Google Sheets

 

-How to Indent in Google Sheets

 

-Google Sheets Dark Mode

 

-Add Line Break in Google Sheets

 

-Word Count in Google Sheets

Automate everything you track in spreadsheets with Lido
Learn more

Level up your Google Sheets skills with our free Google Sheets automation guide

Wasting too much time doing things manually in spreadsheets? Want to spend more time doing what you love? Our 100% free, 27-page Google Sheets automation guide is full of new tips and tricks that will save you time and money!