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.comApr 18 '13 at 17:11

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

Use the conditional format option in the menu format:

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

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.

-
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
@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
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

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

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.

-

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

-
Can you share the doc with us? What is X? – Jacob Jan Tuinstra Apr 20 '13 at 6:11
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 '14 at 19:24
Sorry I haven't checked back in so long. I'll make some edits – Cayetano Gonçalves Oct 15 '14 at 15:04

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

-