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

    W: | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul 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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • Jamie Bedford
    Jamie Bedford ✭✭
    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!

  • Is it possible to republish your example or recreate it? I'm thinking this may work for me. (Percentage of Balance Ranking). Thank you. -Chris