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.

A few friends and myself have created a Google Spreadsheet where we are keeping track of virtual item sales. One column has costs, one column has income one has profit, and a fourth has a running total of funds.

I'd like to be able to make the column with profit to be very green for very large numbers, so they are visible while glancing over the document.

I know how to do this in Excel, but I can't find a way to do it in Google Spreadsheet.

share|improve this question

migrated from superuser.com Apr 18 '13 at 17:11

This question came from our site for computer enthusiasts and power users.

3 Answers 3

up vote 1 down vote accepted

Use the conditional format option in the menu format:
enter image description here

Make sure to select the complete column, when applying the conditional format. See screenshot from the example file I prepared:
enter image description here

Example file: Conditional Formatting. Try to add some numbers yourself add see the result. Be sure to check the settings under Format> Conditional formatting... in the menu.

share|improve this answer
2  
Thanks for the answer, my only concern is that there's no gradients. I am dreading typing in 50 shades of green by hand. –  Cayetano Gonçalves Apr 18 '13 at 19:32
1  
@CayetanoGonçalves LOL, it doesn't need to be a work of art, does it? Perhaps you can use the FREQUENCY function to determine the distribution of the values to the range you set it to be. Thanks for responding.......50 shades .....haha –  Jacob Jan Tuinstra Apr 18 '13 at 19:47
1  
It doesn't need to be perfect, but I just ran up against another problem. There's only 11 conditions that can be applied to the column >.< –  Cayetano Gonçalves Apr 18 '13 at 19:57

I've written a Google Apps Script that partially implements the color scales feature from Excel. You can find more information on my blog. Hope this helps.

share|improve this answer

I finally decided to write a macro, it took me about a day of learning how to use JavaScript.

function colorize(x) {


 if (x==0) {
    var hexcolor = "#ffffff"; //white for zero
  }

else if (x>0) { 
  var hex = (255*(1-x)).toString(16).match(/^\w{0,2}/); 
  // convert into reverse hex percentage

  var hexcolor = "#"+hex+"ff"+hex; //green for over 0
  }

  else if (x<0) {
    var y = -x;

    var hex = (255*(1-y)).toString(16).match(/^\w{0,2}/);
    var hexcolor = "#ff"+hex+hex; //red for less than 0
  }

  return hexcolor;
}

Here x is the profit, so for very large x, the hex-code will be very green, for very large negative numbers, the hex-code will be very red.

That's because green hex codes have variable numbers in the first two and last two positions, and the middle two numbers are "ff".

And, red hex codes have variable numbers in the last four positions, and the first two are "ff".

share|improve this answer
2  
Can you share the doc with us? What is X? –  Jacob Jan Tuinstra Apr 20 '13 at 6:11
2  
So, this returns the colors as strings. Could you explain how to use them to color the spreadsheet? –  Frank Oct 1 '13 at 18:49
    
+1 for sharing an example doc ! –  Max L. Oct 10 at 19:24
    
Sorry I haven't checked back in so long. I'll make some edits –  Cayetano Gonçalves Oct 15 at 15:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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