Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question

6 Answers 6

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

Alternating Color Rows

  • You'd have to edit the script to get the color you want
  • It only affects the sheet you are currently looking at
share|improve this answer
2  
There's currently no way for a formula to do this and no default Google feature. - there wasn't when you wrote that but there is now (see @Stampede X's answer). –  pnuts Apr 23 at 6:07
2  
Script gallery is not available anymore. –  Olli May 3 at 8:36

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.

share|improve this answer
    
Maybe see @StampedeXV's answer. –  pnuts Apr 23 at 6:09
    
Script gallery is not available anymore. –  Olli May 3 at 8:35

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.

share|improve this answer
    
hopefully Google will add formulas to conditional formatting soon. - within about six months I believe. –  pnuts Apr 23 at 6:10

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

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:

Conditional Formatting


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

Custom Formula


See the result:

Result

share|improve this answer
1  
Because currently I use it only in internal company documents. If I find time I will create a dummy. –  StampedeXV Feb 14 at 8:48
1  
a screenshot about what? A sheet with alternating colors? How to choose new sheets or where to define the formula? –  StampedeXV Feb 14 at 12:23
1  
This is the best answer, thanks. –  Mr. Polywhirl Feb 22 at 19:52
    
✔ Best answer, thanks. FWIW, "sheets" is now fully rolled out, and script gallery is now "add ons" –  Rowe Morehouse Jun 23 at 1:25

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.

Screenshot

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

share|improve this answer
    
Why don't you add a screenshot? –  Jacob Jan Tuinstra Feb 27 at 20:57
1  
Didn't think it would help. It's pretty obvious how alternating colors look. –  Hjulle Feb 27 at 22:36
  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.

share|improve this answer
    
Good answer, but it could be even better if you added a screenshot demonstrating the process. –  Vidar S. Ramdal Apr 22 at 10:11
    
Excellent answer. It's really easy thing to do without a screenshot. –  Olli May 3 at 8:35

protected by Community Apr 27 at 16:52

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.