Alternating Row Colors in Google Sheets

Ever found yourself bogged down with data in Google Sheets, wishing to jazz it up a bit? Coloring alternate rows can significantly improve readability and visual appeal. Here's a comprehensive guide on how to color every other row in Google Sheets, with a bonus tip for easy automatic application.

How to Alternate Row Colors in Google Sheets
How to Alternate Row Colors in Google Sheets

Google Sheets doesn't have a native feature for this, but with a little bit of knowledge about conditional formatting and Google Apps Script, you can achieve this in no time.

Facebook
Facebook

Using Conditional Formatting to Color Alternate Rows

Conditional formatting allows you to change the appearance of cells based on their values. In this case, we'll use it to change the background color of every other row.

How to Shade Every Other Row in Excel / Google Sheets
How to Shade Every Other Row in Excel / Google Sheets

First, let's understand the formula we'll use. The formula to get the row number is ROW(). By dividing the row number by 2 and checking if the remainder is 1 or not (with the MOD() function), we can alternate between two colors.

Apply conditional formatting to alternate rows

Highlight EVERY Other ROW in Excel (using Conditional Formatting)
Highlight EVERY Other ROW in Excel (using Conditional Formatting)

Here are the steps to apply this formatting:

1. Select the range you want to format (e.g., A1:Z100).

2. Click 'Format' in the menu, then hover over 'Conditional formatting'.

How to color cells and alternate row color in Google Sheets
How to color cells and alternate row color in Google Sheets

3. Under the 'Format cells if...' dropdown, choose 'Custom formula is'.

4. In the 'Value or formula' field, enter the following formula: `=MOD(ROW(),2)=1`.

5. Choose the formatting style you want (Background color, for example).

the color swatch is shown with hearts in black and white, as well as an outline
the color swatch is shown with hearts in black and white, as well as an outline

6. Click 'Done'.

Color the rows based on other criteria (Bonus)

How to Use Alternate Colors in Google Sheets
How to Use Alternate Colors in Google Sheets
Coloring Tutorial for Plaid Towels | Cici Slay Coloring
Coloring Tutorial for Plaid Towels | Cici Slay Coloring
Perfect Color Matching! Marker Swatch Card Tutorial + Freebie
Perfect Color Matching! Marker Swatch Card Tutorial + Freebie
How to Make a Color Swatch Sheet
How to Make a Color Swatch Sheet
Cre: tinydaisy.coloring
Cre: tinydaisy.coloring
the instructions for how to make an easy wood door
the instructions for how to make an easy wood door
How to Use Alternate Colors in Google Sheets
How to Use Alternate Colors in Google Sheets
the color scheme for an abc pattern
the color scheme for an abc pattern
How to Color Code in Google Sheets - ModernSchoolBus.com
How to Color Code in Google Sheets - ModernSchoolBus.com
FREE Printable 100 Color Swatch Templates
FREE Printable 100 Color Swatch Templates
Practice Blending with This Free Printable Sheet
Practice Blending with This Free Printable Sheet
Printable 80 Round Rects Color Swatch Chart
Printable 80 Round Rects Color Swatch Chart
Simple Art Background Ideas, Color Theory Reference Guide, Drawing Color Pallete, Color Pallete For Drawing, Artist Color Palette Reference, Tbhk Color Palette, Color Mixing Techniques, Color Pallets For Drawing, Color Pallete Drawings
Simple Art Background Ideas, Color Theory Reference Guide, Drawing Color Pallete, Color Pallete For Drawing, Artist Color Palette Reference, Tbhk Color Palette, Color Mixing Techniques, Color Pallets For Drawing, Color Pallete Drawings
How to draw nature | nature coloring | tree coloring | grass coloring | how to draw coco wyo | ohuhu
How to draw nature | nature coloring | tree coloring | grass coloring | how to draw coco wyo | ohuhu
Coloring Lab Practice Book
Coloring Lab Practice Book
how to color picnic blanket instructions
how to color picnic blanket instructions
the worksheet shows how to draw an arrow with four different arrows on it
the worksheet shows how to draw an arrow with four different arrows on it
coloring pattern inspo
coloring pattern inspo
the honolulu series is shown in black and white, as well as an image of several rows
the honolulu series is shown in black and white, as well as an image of several rows

You can modify the formula slightly to base the formatting on other criteria. For example, if you want rows containing certain text to be colored, you could replace ROW() with the address of the cell containing that text, like this: `=MOD(COUNTIF(A:A,"search"),2)=1`.

Or, you could color rows based on the values in a cell: `=MOD(A1,2)=1`.

Automatically Apply Formatting to New Rows

What if you frequently add new rows to your sheet? Here's how you can automate the formatting:

We'll create a Google Apps Script that runs on every change to the sheet. It will use the above conditional formatting formula to apply the style each time.

Create and Run a Google Apps Script

1. Click 'Extensions' in the menu, then 'Apps Script'.

2. Paste the following script into the code editor:

```javascript
function onEdit(e) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const s = ss.getActiveSheet();
const r = s.getActiveRange();
let rng = r.getCells(SpreadsheetApp.Range.useRowColumns());
let rules = s.getConditionalFormattingRules();
let rule = rules.find(r => r.getDescription() == 'Alternate Row');
if (!rule) {
let formulas = [{
whenCellEmpty: false,
backgroundColor: new RichTextValue().setBehind(false).setBackgroundColor('bcd8b0'),
condition: {
type: RichTextValue.Types.TEXT_CONTAINS,
value: '=MOD(ROW(),2)=1'
},
UI="/**/);
}
rule = s.setConditionalFormatting(rng, formulas);
}
}
```

3. Click 'Save' and give it a name, like 'AlternateRow'.

4. To test, insert a new row and watch the row above it change color.

And there you have it! With just a bit of creativity and a little bit of code, you've transformed your Google Sheets from plain to vibrant. Whether you're analyzing data, creating a report, or just wanting to make things pop, coloring alternate rows can make an enormous difference.

Related Articles

What Color Is Turkish Red How To Change Cell Color In Google Sheets Based On Checkbox How To Filter By Cell Color In Google Sheets How To Change Table Border Color In Google Sheets How To Color Code Drop Down List In Google Sheets Free Printable Turkey Coloring Pages For Toddlers How To Count Cells By Color In Google Sheets How To Change Table Header Color In Google Sheets How To Change Checkbox Color In Sheets How To Filter By Fill Color In Google Sheets