Conditional formatting to apply a color gradient based on cell contents?

Jamie Bedford · 07/31/20

Hi there!

Google Sheets have a very useful feature that lets you set a conditional color gradient (i.e., white to red, or red to green shadings, etc.) that adjusts based on either min/mid/max values or based on absolute values.

Is this kind of thing possible with the existing conditional formatting feature? Or would I need to add each color manually?

Thanks!

Jamie

Paul Newcome · 08/04/20

It actually doesn't take too long to set up once you establish a few details. The biggest detail being how many different colors/shades of colors do you want incorporated. I actually have a sample sheet set up already that has 11 points ranging from dark red through orange and yellow into green.

The basic concept is that we use three columns.

Rank

[Table Rank]

[Table Range]

In the [Table Rank] column I simply list the numbers 0 through 10 down the column.

In [Table Range]1 (rank 0) I pull the MIN, and in [Table Range]11 (rank 10) I pull the MAX. Both of these come from the number column you want to base your conditional formatting on (in my example the Score column).

Then in [Table Range]2:[Table Range]10 I use:

=(VALUE("." + [Table Rank]@row) * (MAX(Score:Score) - MIN(Score:Score))) + MIN(Score:Score)

Next we go to the Rank column which will pull from the table the appropriate rank based on the range and use this formula:

=INDEX([Table Rank]$1:[Table Rank]$11, MATCH(MIN(COLLECT([Table Range]$1:[Table Range]$11, [Table Range]$1:[Table Range]$11, >=Score@row)), [Table Range]$1:[Table Range]$11, 0))

Last but certainly not least we set up the Conditional formatting for each of the ranks.

HERE is a link to the published sheet you can use as an example that includes the formulas in cells that you can simply copy/paste from (aside from the few manual entry bits).

So really if you match your column names to mine, you can copy paste formulas straight into your sheet then change your column names back to what you want. Then just manually fill in the [Table Rank] column and set up the Conditional Formatting.

NOTE: My example is set up to break it down into percentages of a whole where the lowest score is 0% and the highest score is 100%. The remaining scores are given a rank based on where between those two numbers the score falls.

If you only wanted 3 points of data such as low range, mid range, and max range, you would use 0 and 10 but then use different numbers in between or even less numbers if you wanted.

Hopefully that all makes sense with the link and whatnot. Feel free to let me know if you have any questions.

Andrée Starå · 07/31/20

Hi @Jamie Bedford

Unfortunately, it's not possible at the moment, so you'd have to add each color manually, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome · 07/31/20

There is a way to build out a table that pulls the min and max with however many different points in between. You can then set up a "score" column that would score the row based on where it falls on this table then set up your conditional formatting manually. Once this is set up though the process is automated and the colors you selected manually will automatically adjust based on the overall data in the sheet.

Paul Newcome · 07/31/20

@lewis hamilton You are referring to Excel. This is not how Conditional Formatting in Smartsheet works.

Jamie Bedford · 08/04/20

@Paul Newcome -- thanks for the insightful idea!

I don't think that my use cases would be impactful enough to justify spending the time setting that up (and keeping it updated if additional values outside of the initial range are added), though.

Are you aware of any templates of this kind of thing that could reduce the time investment necessary to set this up?

The nice thing about Google's implementation is that it is dead simple to apply -- very high ROI. It can turn any data column into a visual report in only a minute or two. If it would take hours to set up then I may as well use a real reporting tool.

I'll submit an Enhancement Request, regardless...

Paul Newcome · 08/04/20