How to Alternate Row Color in Excel & Google Sheets

This tutorial demonstrates how to alternate row color in Excel and Google Sheets.

 

altrowcolor intro

 

Alternate Row Color

Table Formatting

To format each row in a table to have alternate row colors, you can use the Format as Table feature in Excel.

  1. Select the cells you wish to apply the alternate row colors or click in the middle of the range of cells you wish to apply the alternate row colors to.

 

altrowcolor no format

 

  1. Then, in the Ribbon, select Home > Styles > Format as Table and then select the type of formatting required.

 

altrowcolor format table

 

  1. If applicable, make sure the My table has headers box is checked and click OK.

 

altrowcolor format as table

 

The data is formatted as a table, with alternative row colors.

 

altrowcolor converttorange

 

  1. Notice there is (1) a new Table tab on the Ribbon.
    Click (2) Convert to Range to convert the table back into a simple range of cells.

 

altrowcolor normalrange

 

  1. Click OK to convert the table to a normal range.

OR

Another way to format data as a table (Steps 1–3 above) is through the Ribbon’s Insert tab.

  1. In the Ribbon, select Insert > Table.

 

altrowcolor create table

 

  1. Once again, check the My table has Headers check box if relevant.

 

altrowcolor createtable

 

Conditional Formatting

You can also create alternative colors for each row using Conditional Formatting.

  1. Select the cells you wish to apply the alternate row colors to, and then, in the Ribbon, select Home > Styles > Conditional Formatting, and then select New Rule.

 

altrowcolor new rule

 

  1. In the New Formatting Rule dialog box, select (1) Use a formula to determine which cells for format.

 

Then, (2) type in this formula:

=MOD(ROW(),2)=0

Next, click (3) on Format.

 

altrowcolor create rule

 

  1. In the Format Cells dialog box, select (1) the Fill tab and (2) select the Background Color. Finally (3) click on OK and then click OK again to return to Excel.

 

altrowcolor format rule

 

As you can see below, the data has alternating colors.

 

altrowcolor conditional formatted

 

Alternate Row Color in Google Sheets

Alternate Colors

  1. Select the cells you wish to apply the alternate row colors or click in the middle of the range of cells you wish to apply the alternate row colors to.
  1. In the Menu, select Format, Alternate Colors.

 

altrowcolor gs alternating-colors menu

 

  1. The entire table will automatically be selected in the (1) Apply to range box. In the Styles (2) a Header is automatically selected – you can remove the tick if you do not wish to have a header row. You can (3) select the style you require, or (4) you can create a Custom style and select your own colors for the Header and alternate rows.

 

altrowcolor gs alternating-colors

 

  1. Click Done when complete to apply the format to the Google Sheet.

Conditional Formatting

You can also apply alternate colors in Google Sheets using Conditional Formatting.

  1. Select the cells you wish to format, and then, in the Menu, select Format > Conditional Formatting.

 

altrowcolor gs conditional formatting

 

Make sure the (1) range is correctly selected and then select Custom formula is in the Format cells dropdown list.

 

  1. You can use the same formula from the Excel example above.
=MOD(ROW(),2)=0
  1. Select the background color of the cells.
  2. Then click Done.

 

altrowcolor gs conditional formatting rule

Again, you get alternating colors.
altrowcolor gs done