Alternating colors for lines in Google docs spreadsheet

Question

I have a shared Google spreadsheet where several users have already filled out parts. It would be useful to have alternately colored lines to facilitate correct placement of additional data.

Is there a painless method option to alternately color (say, the cells given that already many of them contain text?

In an empty document, there would always be the "color 2 lines, copy-paste 2 lines, copy-paste 4 lines, etc." option, but in the current document, there is already text and some lines are still liable to be deleted which necessitates a recoloring later.

Answer 1

Finally my original answer is completely obsolete.

Google updated features with Alternating Colors features in "Format" menu item. There you can easily select a default scheme and customize as you like:

Be sure to remove your old manual formatting, as this doesn't automatically replace/override.

=== OBSOLETE BELOW THIS LINE ===

Google added this feature now in the "new spreadsheet format".

This can be enabled for new documents like follows: On "MyDrive" go to settings choose Editing and then "Try the new Google Sheets". Please be aware that as of now not all features are complete, so treat it as a beta version.

One of the changes in this new spreadsheet is the possibility to define custom formulas in the conditional formatting option.

This formula would then allow the alternating coloring in the sheet (if applied to whole sheet):

=MOD(ROW(A2),2) < 1

Update. The following will also work (thx @Qwertman):

=MOD(ROW(),2) < 1

Just for completeness: I'm using the new sheets now for quite some time and did not yet find a feature I was missing. While the custom formulas are very helpful - not only for alternating colors.

Select Conditional Formatting:

Create a custom Formula with Background Color and Range of Cells:

See the result:

Answer 2

There's currently no way for a formula to do this and no default Google feature.

However, there are a few third party scripts that will do this for you:

Installing the script

1. Open your spreadsheet
2. Click on Tools menu
3. Click on Script Gallery
4. Search for Zebra Stripe
5. Install
6. Read the warning (and if you agree go on)
7. Authorize

Executing the script

1. Click on Tools menu
2. Go to Script Manager...
3. Click on ZebraStripe
4. Click Run
5. Profit

• You'd have to edit the script to get the color you want
• It only affects the sheet you are currently looking at

Answer 3

1. Select row 1
2. Color it with Fill color
3. Select row 2
4. Color it with another color with fill color
5. Select row 1 and 2 row and copy it
6. Select the full sheet
7. Go to Edit → Paste special → Paste format only

Job done.

Answer 4

I have posted a script to the Script Gallery called "Zebra Stripe Menu". If you add that to your spreadsheet, and make sure to open and close the script editor (there is a bug with recognizing the script). You will have a menu called "Zebra Stripes" and there you will find a couple of options for striping sheets and groups of cells. Some of the other options don't use Google best practices and can be very difficult to undo or stripe selections. If you find better tools, I'd love to hear about them.

Answer 5

You can also use the paint format tool. Highlight the row after you've changed the color, click the icon that looks like a paint roller, then click the next row you want to change color too (as long as it's okay if the rest of the format of both those rows are the same.) It's annoying to have to do it repetitively, but it's slightly faster than selecting the color for each row.

• If you do this before adding data then the other format changes will go on top.

• The main problem with this method if you insert rows later it will offset everything unless you insert a pair of rows and format it the same.

hopefully Google will add formulas to conditional formatting soon.

Answer 6

An alternative solution could be not to change the document at all and just change how it is presented, using custom CSS (eg. with the Stylish Firefox/Chrome plugin):

@-moz-document url-prefix(https://docs.google.com/spreadsheet) {
    .waffle tr:nth-child(even) > td {
        background-color: #A0FFA0 !important;
    }
}

It is easy to turn on and of, using the stylish menu, and should work on all spreadsheets, even those you do not have write access to. It's not exactly what was asked for, but could be useful regardless.

I have uploaded this style-sheet to the userstyles page as well.

Answer 7

The answer of @StampedeXV is correct, but I suggest using a simpler function.
You don't need a parameter for the ROW() function and ISEVEN() is much simpler than MOD:

=ISEVEN(ROW())

Targeting the inverse set of rows would be:

=ISODD(ROW())

Column based zebra tables can be done with:

=ISEVEN(COLUMN())

...and...

=ISODD(COLUMN())