How to Color Every Other Row in Google Sheets

Key Points

To color alternate rows in Google Sheets:

  1. Select the rows, including the header, you want to color.
  2. Select Format > Alternating colors.
  3. Choose one of the default color styles or create your custom style.
  4. Click on 'Done'.

 

If you want to color alternate every third or fourth row, or color alternate columns, you need to do it with the Conditional formatting functionality explained in this article.

Suppose you are looking to add a visual element to your Google Sheets spreadsheet or make your data easier to read. In that case, you can use the awesome feature that allows you to color alternate rows in Google Sheets, or simply color every other row in Google Sheets.

The guide below will walk you through the steps for highlighting with alternate colors your Google Sheets files.

How to color every other row in Google Sheets

Google recently added the alternating colors functionality that makes it easy to highlight every other row in Google Sheets, i.e. color alternate rows in Google Sheets.

Before that, you had to use workarounds, such as conditional formatting, if you wanted to color every other row. But now you can do this in only 2 clicks. However, below we will show you both methods: the built-in method to color alternate rows in Google Sheets and the conditional formatting method if you want to color every third or fourth row and color alternate columns.

How to color every other row in Google Sheets with the built-in method

The first method for highlighting every other row in Google Sheets is really straightforward.

  1. First, open the spreadsheet in which you want to color alternate.
  2. Next, select all the rows in the dataset, including the header.
  3. Click on the ‘Format’ tab.
  4. Select ‘Alternating colors’ near the bottom.

Select ‘Format’ > ‘Alternating colors’

  1. In the pane that opens on the right, make the following changes:
    1. Make sure the ‘Header’ option is selected under the Styles section. If your spreadsheet doesn’t have headers, uncheck this option.

Make sure the 'Header' option is checked

    1. Select from any of the default styles or create your custom style by specifying colors for Header, Color 1 and Color 2.

Select from any of the default styles or create your custom style

  1. Click on ‘Done’.

Click on the 'Done' button

This will instantly color every other row in your Google Sheets dataset with the chosen colors.

Rows highlighted in alternating colors in Google Sheets

To remove the alternating colors,

  1. Click on any cell in the dataset.
  2. Click on the ‘Format’ tab and then select the ‘Alternating colors’ option.
  3. This will open the pane where you’ll see the option to ‘Remove alternating colors’.

Check out also how to add superscript and subscript in Google Sheets.

Things to have in mind when applying the ‘Alternating colors’ functionality

These are several things you need to know when you are using the ‘Alternating colors’ functionality and how it interacts with other functionalities in Google Sheets.

  • If you have highlighted the cells in another color, they will be replaced by those in the ‘Alternating colors’ functionality. If you remove the alternate colors, it will bring back the original colors of the cells.
  • If you want to change the header color of a specific cell or range, you can do that manually in the ‘Alternating colors’ menu.
  • If you add more records to the datasheet, Google Sheet will automatically color the row in the alternate colors style you chose. But, if you delete any records, the colors will remain and you have to remove them manually.
  • If you add more rows to the dataset, Google Sheets will automatically highlight them in alternate colors.

How to color every third or fourth row in Google Sheets

Let’s explore how you can color alternate every third or fourth row in Google Sheets with the Conditional formatting functionality.

  1. Select the rows you want to highlight with alternate colors by dragging the cursor on the row numbers on the left.
  2. Click on the ‘Format’ tab.
  3. Select ‘Conditional formatting’ at the bottom of the pop-up menu.

Select ‘Format’ > ‘Conditional formatting’

  1. In the Conditional Format rules panel on the left, click on the ‘Format rules’ drop-down menu.

Click on the ‘Format rules’ drop-down menu

  1. Scroll at the bottom and select ‘Custom Formula is’.

Select ‘Custom Formula is’

  1. In the ‘Value or formula’ field enter this formula: =MOD(ROW(),3)=0 if you want to color every third row or =MOD(ROW(),4)=0 if you want to color every fourth row.

'Custom formula is' =MOD(ROW(),3)=0

  1. Next, specify the formatting style - you can make rows bold, italicize them, apply strike-through format, etc.
  2. Click on ‘Done’ to save the changes.

Note: You can alternatively use the formula =ISEVEN(ROW()) to color all even rows in your dataset, or the formula =ISODD(ROW()) to color all odd rows.

How to color columns in Google Sheets

The ‘Alternate coloring’ functionality doesn’t work for columns. So if you want to color alternate columns in Google Sheets, you also need to use ‘Conditional formatting’.

  1. Select the columns you want to highlight with alternate colors by dragging the cursor over the column header.
  2. Next, select ‘Format’ > ‘Conditional formatting’.
  3. In the Conditional Format menu, click on the Format Rules’ drop-down menu, and select ‘Custom Formula Is’.
  4. In the ‘Value or formula’ field, type in the formula: =ISEVEN(COLUMN()).

Formula to color alternate even-numbered columns in Google Sheets

  1. Click on ‘Done’.

This formula will highlight each column with an even number, meaning column B, column D, etc.

even-numbered columns with alternate colors

If you want to alternate color formatting to odd-numbered columns (column A, column C, etc.):

  1. Select the columns you want to highlight.
  2. Go again to Format > Conditional formatting or simply click ‘Add Another Rule’ in the Conditional Format rules panel.
  3. From the ‘Format Rules’ drop-down menu, select ‘Custom Formula Is’.
  4. In the formula box, enter the formula: =ISODD(COLUMN()).

Formula to color alternate odd-numbered columns

  1. Select formatting options in the ‘Formatting Style’ box and then click on ‘Done’.

Your columns will be highlighted at every odd column.

odd-numbered columns with alternate colors

If you want to remove any conditional formatting rules you’ve applied, go to the Conditional Format Rules” panel, hover over a rule, and click on the ‘Delete’ icon.

You might also be interested in learning how to indent text in Google Sheets.