Alternating colors for lines in Google docs spreadsheet

Posted on

Alternating colors for lines in Google docs spreadsheet – Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula or script parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common script or formula parse errors in Google Sheets, like Alternating colors for lines in Google docs spreadsheet, and how to fix it.

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 :

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:

how to find it

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:

Conditional Formatting

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

Custom Formula

See the result:

Result

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

Job done.

Leave a Reply

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