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

Jamie Bedford

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

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.