6

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.

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

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

3

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.

  • 3
    Thanks for the answer, my only concern is that there's no gradients. I am dreading typing in 50 shades of green by hand. – Ryan Ward 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 >.< – Ryan Ward Apr 18 '13 at 19:57
12

Google Sheets now supports conditional color scales under Menu "Format > Conditional formatting..." then select the tab "Colour scale".

Google Sheets color scale

5

An old thread, but it's worth noting that Google just launched this functionality into Google Sheets. This likely negates the need for external solutions for most users. More info: Google Apps Updates - Conditional formatting improvements in Google Sheets

  • Yup, they've got exactly what the OP is asking for. It's called Color Scale. – jmagnusson May 24 '15 at 10:25
2

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.

2

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".

  • 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
  • Sorry I haven't checked back in so long. I'll make some edits – Ryan Ward Oct 15 '14 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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