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

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

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?




Best Answer


  • Andrée StaråAndrée Starå ✭✭✭✭✭

    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!


    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!


    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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.

  • Apply conditional formatting to text

    Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to.

    On the Home tab, click Conditional Formatting.

    Point to Highlight Cells Rules, and then click Text that Contains.

    Type the text that you want to highlight, and then click OK.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

  • edited 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...

  • Just saw your explanation and link to an example, @Paul Newcome -- that's super helpful! Thanks!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

Sign In or Register to comment.