How to Color Every Other Row in Google Sheets

Imagine you are managing a sales report in Google Sheets and want to make it easier to read by adding color to every other row. This will help you quickly differentiate between rows and avoid mistakes when looking at the data.

In the above example, you can see that rows with different color shades help you read the data easily.

When every other row has an alternating color, it’s much easier to follow a single row of information, especially when you have many columns and rows.

This can help reduce errors, as your eyes can quickly distinguish between rows.

You have four ways to apply alternate row colors to the data in Google Sheets. We also have a new Convert to Table option introduced in June 2024 in these four methods.

Before you do that, make sure to have a few things in your data, like having a clear heading row, remove blank rows, or columns from the data and also fix the merged cells. And now, let’s learn these methods in detail.

Alternating Colors Option in Google Sheets

This is one of the best ways to create a row of different colors. This is the specific option for applying two different colors to the rows. To use this, follow these simple steps:

1. Select the range of cells you want to apply alternating colors to. This could be a specific range of a single column, table, or entire worksheet.
   
2. From the menu bar, go to the Format menu and click on “Alternating colors”. When you click on it, it will open a new pane on the right side of the screen and apply default alternate colors to the rows of the selected rows.
   
3. After that, you can select the color style from the default style already in the pane or create a new one from there. You can also customize a few more options, like adding a color for the footer and changing the color of the alternating rows.
   
4. In the end, once you are done with all the changes you want to make, click to save the settings. But you can come back any time and make changes you want to make to the color shades or other settings.
   

If you use the dark mode in your workbook, you might need to use light shades of color to create alternating rows for the data.

Removing Alternating Rows

If you want to remove the alternating row colors, you must follow the same steps you used while applying them. Click on the “Format” option from the menu bar and open the “Alternating colors” option.

This will open the sidebar on the right side of your screen where you initially set the alternating colors.

At the bottom, you will see an option to “Remove alternating colors.” Click this button to remove the formatting from your selected range.

The other way is to remove formatting from the entire range using the clear formatting option. But this will also remove the other formatting like font color, bold, italic, underline, etc.

Read Also – Make All Cells of the Same Size in Google Sheets / Sort by Color in Google Sheets

Alternate Colors with Conditional Formatting

You can also use conditional formatting to add alternate colors to the rows in the data. You need to apply conditional formatting using formulas to the data range.

1. Select the range of cells you want to format with alternating colors. Again, this could be a single column, a range of cells, or an entire worksheet.
2. Then, go to the “Format” option and click on the Conditional Formatting option. This will open a sidebar on the right side of your screen.
   
3. Now, from the sidebar, you need to do three things: from the “Format cells if,” select “Custom formula is”. Enter the following formula to color even rows: =ISEVEN(ROW()), and then choose the color you want to apply to the even rows. In the end, click “Done”.
   
4. Next, you need to apply color for the odd rows, and for this you need to create one more rule with the same steps and then use the formula =ISODD(ROW()), and click “Done” to apply. You can also leave it the way it is by showing the default white color on the odd rows.
   

After that, apply color and put the cells for the header row in bold. This will create a data table with alternate shaded rows.

You can download a PDF file with shaded colors once you apply alternate colors to the rows.

Using New Convert to Table Option for Alternate Colored Rows

I am referring to Google Sheets’ new “Convert to Table” feature, which was introduced in June 2024. This feature simplifies applying alternating row colors and other table formatting.

In the menu at the top of Google Sheets, click on the “Format” option. From the menu, select “Convert to table.” This will apply default table formatting to your selected range, including alternating row colors.

Applying “Convert to table” gives the data a light shade of alternate rows, allowing you to differentiate each row from another.

Use Google App Script to Create a Custom Option to Creating Alternate Colored Row

Once you run the script below, it creates a custom menu on the menu bar with the option to apply alternate coloring rows to the selected data.

To open the script editor, click “Extensions” in the menu, select “Apps Script”, paste the code there, and then save it. After saving the script, reload your Google Sheets workbook.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Add a custom menu to the spreadsheet
  ui.createMenu('Custom Menu')
      .addItem('Apply Alternating Colors', 'applyAlternatingColors')
      .addToUi();
}

function applyAlternatingColors() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveRange();
  var numRows = range.getNumRows();
  var numColumns = range.getNumColumns();
  
  // Check if no rows or only one row is selected
  if (numRows <= 1) {
    SpreadsheetApp.getUi().alert('Please select at least two rows.');
    return;
  }

  // Ask if the first row is a header
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert('Is the first row a header?', ui.ButtonSet.YES_NO);

  // Colors
  var headerColor = '#cccccc'; // Light grey color for header
  var color1 = '#ffffff'; // White color
  var color2 = '#f2f2f2'; // Light grey color

  // Apply colors
  for (var row = 0; row < numRows; row++) {
    var color;
    if (row == 0 && response == ui.Button.YES) {
      // Apply header color
      color = headerColor;
    } else {
      // Apply alternating colors
      color = ((row + (response == ui.Button.YES ? -1 : 0)) % 2 == 0) ? color1 : color2;
    }
    for (var col = 0; col < numColumns; col++) {
      range.getCell(row + 1, col + 1).setBackground(color);
    }
  }
}

Points to Take Care while using Alternate Rows Colors

You need to consider a few points while applying alternate colors to the rows.

• Make sure to format the header row differently to differentiate it from the rest of the data. This helps quickly identify the column headers.
• Be aware that adding or removing rows may disrupt the alternating color pattern. Check and adjust the formatting as needed to maintain the desired pattern.
• Before you apply colors to the rows, make sure to unhide all the hidden rows from the data so that it disturbs the data formatting when you unhide them.
• If cells in your worksheet are locked, then you need permission from the admin to apply the alternate rows to the range.