Alternate Row Shading In Google Sheets

This article shows you how to apply shading to every alternate row in a google sheet. You can apply the shading by using Alternating Colors Feature or simple conditional formatting formula.
Alternatively, you can use Google app script to apply colors to alternate rows.

Using Alternating Colors Feature

  • Open Google Sheet and select the range where you want to apply alternating colors
  • Go to Format->Alternating colors.
  • Selected range will get colored by default colors, you can change the styles by selecting different formatting styles on the right sidebar.
  • You can click on Remove Alternating colors option at the bottom on sidebar if you want to remove the alternating colors.

Using Conditional Formatting

  • Open Google Sheet and select the range where you want to apply alternating colors, goto Format->Conditional formatting.
  • Select Custom Formula is on the sidebar in left and enter formula in textbox =MOD(ROW(),2)=0
  • Select Formatting style and click button Done.


Note:

If you want to apply shading to alternate columns instead of alternate rows, enter =MOD(COLUMN(),2)=0 instead.

Using Google Apps Script

Given code snippet apply colors to alternate rows using google app script

This function get an array of the whole range, step through each row and apply different colors to even and odd rows. Then at the end call use setBackgroundColors() to set the colors on entire range selected.

function alternateColors() {
  var ss          = SpreadsheetApp.getActiveSpreadsheet();

  var sh          = ss.getActiveSheet();
  
  var backgrounds = sh.getRange('A1:H20').getBackgrounds()
  
  for(var i=0; i<backgrounds.length; i++){
    if(i%2==0){
      for(var k=0;k<backgrounds[0].length;k++){
        backgrounds[i][k]='#ffffff'
      }
    }    
    else{
      for(var k=0;k<backgrounds[0].length;k++){
        backgrounds[i][k]='#e0f7fa'
      }
    }
      
  }
  
  sh.getRange('A1:H20').setBackgrounds(backgrounds);
}

Add a Comment

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