Visualization – calculated color gradients

Posted on by

CalcGradient

If you need dynamically generated visualizations for your SharePoint data, have you considered leveraging the power of the Calculated Column? This Tuesday at 1:00pm EST, Mark Miller and I will give you all the keys to master this simple yet powerful technique. At the end of the two hour entry level workshop, you’ll be able to add color coding, KPIs and other effects – like the one described in this post – to your SharePoint lists.

Green/Yellow/Red is a standard color palette for dashboards. You can just use 3 colors to visualize discrete states, for example the status of a project (on track – drifting – late). But if your purpose is to communicate progress, or a measure on a scale, you need a larger color palette. This is for example the case in my screenshot, where the color reflects the level of completion (in %), in a tasks list.

So, how can I do this in SharePoint? Of course, my plan is to use a calculated column that will determine the color, based on the value in the [% Completed] column.

Method 1: nested IFs

This is the most basic approach:
if [% Completed]>90, select green
else if [% Completed]>80, etc.
I am not going to detail it, as we can do much better.

Method 2: CHOOSE function

The CHOOSE function is more elegant than nested IFs, and is a natural choice when dealing with multiple options. You’ll find all the explanations to achieve a color gradient in this post.

Method 3: pure calculation

So, how can we go even further? Well, colors can be identified by their name, but also by their rgb code, as each color can be generated from a combination of red, green and blue. For example:
red: rgb(255,0,0)
green: rgb(0,255,0)
blue: rgb(0,0,255)
yellow: rgb(255,255,0)
white: rgb(255,255,255)

Using these values, we can “easily” create our red/yellow/green gradient:
rgb(255,0,0) –> rgb(255,255,0) –> rgb(0,255,0)

The following formula, entered in a calculated column, will give you the rgb value for each value of the [% Completed] column:
=”rgb(“&INT(MIN(510-[% Complete]*255*2,255))&”,”&INT(MIN([% Complete]*255*2,255))&”,0)”

To obtain the visual effect as in the screenshot, use the HTML Calculated Column method, with the following formula:

="<span style='display:inline-block;position:relative; width:60px; height:14px;border:1px solid;'><span style='display:inline-block;position:relative;background-color:rgb("&INT(MIN(510-[% Complete]*255*2,255))&","&INT(MIN([% Complete]*255*2,255))&",0); width:"&([% Complete]*100)&"%;height:14px;'><span style='position:absolute; top:0px;'> "&TEXT([% Complete],"0%")&"</span></span></span>"

Note that the method works in both SharePoint 2003 and 2007.

18 thoughts on “Visualization – calculated color gradients”

  1. Pingback: uberVU - social comments

  2. Pingback: Visualizing Information in SharePoint: Demo 04 – Three Resources | End User SharePoint

  3. Hi – Loved the class and was able to use TWO of the ideas within hours after the class was over.

    I used the Gradient on one of our sites to show % complete. My PMO asked if we could adapt this for tracking % of budget.. Well, that’s good, except budgets tend go over 100%.

    The PMO manager said she would be satisified if the box just shows 100%+ and a differnt (bright) color. I’d like to show the actual value (eg: 125%) in the box.

    Any ideas for how to best address this? Thanks.

    • Thanks Wanda. good to see you can leverage what we did in the workshop.
      First, you need to set up your business rules: what exactly do you expect to happen when you go over 100%? What would be the extreme value? When do you turn yellow or red?

  4. Pingback: HTML Calculated Column and Unicode Graphics « Path to SharePoint

  5. Wow, great stuff. I was able to implement a color background on one list but had no luck with the gradient on a task list.

    The calculated column formula returns a syntax error: “The formula contains a syntax error or is not supported.”

    I am using this in a SP 2003 Task List and it does have a “% Complete” column.

    =”rgb(“&INT(MIN(510-[% Complete]*255*2,255))&”,”&INT(MIN([% Complete]*255*2,255))&”,0)”

    Has this been an issue for others?

  6. Hi Robert,

    I had the same problem and found a way to make it work.

    Try these following options :
    – If your SP2003 is not in English, try to translate it into the correct language (I had to translate into French)
    – Within the formula, use a ; instead of a , it worked for me.

  7. I have a column called “Open Days” for which I need to calculate a risk indicator which need to return a background color to indicate risk. Dending on the impact level it needs to return different values and therefore colors.
    Impact High 9=red otherwise orange
    Impact Medium 14=red otherwise orange
    Impact Low 19=red otherwise orange
    So depending on the impact it needs to return color gradient as per each impact. I tried to do this with IF and INT but no success.
    thanks

  8. Great tip, works beautifully except when I print the screen it does not print the progress bar in colour. Any ideas what is causing this?

    Keep up the great work

  9. Pingback: Security concerns with the HTML Calculated Column « Path to SharePoint

  10. Pingback: Trick or Treat? Text to html, the wicked no-code way « Path to SharePoint

  11. Pingback: Trick or Treat? Text to html, the wicked no-code way (Part II) « Path to SharePoint

Comments are closed.