Color coding: more examples

I already published an article about color coding for SharePoint lists three months ago, and its sibling for calendar views last month. Both rely on the same client side method, the “HTML calculated column“.

Color coding involves 3 steps:

1/ Select the color

2/ Apply the formatting: traffic light, font color, background color, etc.

3/ Include the magic script that renders the formatting (SharePoint OOTB would just display text)

People are usually comfortable with step 3, which is just a copy/paste of a generic script. But some users are struggling with the first two steps, because they are not so familiar with SharePoint calculated columns.

So I have decided to publish a list of examples based on the default SharePoint lists. They can be used as is and should cover a wide range of use cases. For each example, you can grab the formula by clicking on “copy to clipboard”.

If you are a seasoned list designer, skip this post…but forward the link to your friends!

1/ Color selection

In HTML, you can identify a color by its name or its code. Here we’ll stick to the friendly color names. A good reference is W3schools.

Let’s call [Color] the column that will store the value. You can choose among the following column types:
– Text
– Choice
– Calculated column

The latter will be the most useful, as the color usually depends on another factor. For example, we’ll pick red for issues that are tagged as “Critical”.

If you are not familiar with calculated columns, you can start by reading this introduction.

Example 1: Priority (Issues list, Tasks list)
(1) High red
(2) Normal gold
(3) Low limegreen

Use this formula in your [Color] calculated column:

=CHOOSE(RIGHT(LEFT(Priority,2),1),"red","gold","limegreen")

How it works: if the user selects “(2) Normal”, RIGHT(LEFT(Priority,2),1) will extract the second character. It is the number “2”, so CHOOSE will select the second item – gold.

Example 2: Category (Issues list)
(1) Category1 red
(2) Category2 gold
(3) Category3 limegreen

The formula in this case:

=CHOOSE(RIGHT(LEFT(Category,2),1),"red","gold","limegreen")

Example 3: numbered list
Let’s say you have a [Choice] column with following values:
1. First choice red
2. Second choice gold
3. Third choice limegreen

In this case, the number is in first position, and we get a shorter formula:

=CHOOSE(LEFT(Choice,1),"red","gold","limegreen")

Example 4: Issue Status (Issues list)
Active red
Resolved gold
Closed limegreen

No number to help us identify the choice, so we need to find a trick. My choice: identify the position of the letter “e”. If for example “e” is in fifth position, it means that the status is “closed”.

The formula:

=CHOOSE(FIND("e", [Issue Status],1),,"gold",,,"limegreen","red")

How it works: the CHOOSE function will pick a color according to the position of the letter “e”. So if the status is “Closed”:
FIND(“e”, [Issue Status],1) = 5 (the letter “e” is in fifth position)
So the CHOOSE function will pick the color that is in fifth position, “limegreen” here. Note that in our example the letter “e” is never in position 1, 3 or 4, this is why I didn’t enter any color name for these positions.

We can also use nested IFs for a similar result, but the formula is heavier (especially if we have a longer choice list):

=IF( [Issue Status]="Active", "red", IF([Issue Status]="Resolved", "gold", "limegreen"))

Example 5: Status (Tasks list)
Not Started red
In Progress gold
Completed limegreen
Deferred black
Waiting on someone else darkorange

Here again we’ll use the “e” letter trick:

=CHOOSE(FIND("e",[Status],1),,"black",,,,"limegreen",,,"gold","red",,,,,"darkorange")

Here, FIND(“e”,[Status],1) can only take five values:
2 for Deferred –> black
6 for Completed –> limegreen
9 for In Progress –> gold
10 for Not Started –> red
15 for Waiting on someone else –> darkorange

2/ Formatting

After setting the value of the [Color] column, we can now format it. For this we need another calulated column. The formulas below are the same as the ones I used in the initial post on color coding.

Example A: traffic lights

="<DIV style='font-weight:bold; font-size:24px; color:"&Color&";'>&bull;</DIV>"

I am using a bullet (&bull;) to create the shape.

Example B: font color

My formula is for the [Issue Status] column, adapt it if you work with another column:

="<DIV style='font-weight:bold; font-size:12px; color:"&Color&";'>"&[Issue Status]&"</DIV>"

Example C: background color

Here too, replace [Issue Status] with your own column:

="<DIV style='font-size:12px; background-color:"&Color&";'>"&[Issue Status]&"</DIV>"

3/ The script

To apply the script, simply follow the instructions for list views or calendar views. There’s also a troubleshooting page to help you out.

Please let me know if you find that detailed posts like this are helpful.

About these ads

175 thoughts on “Color coding: more examples

  1. Christophe:
    This helped me again to create a wonderful simple In/ Out Board for a team in our company! Thank you.

    On another issue, and somewhat a mix of your red/green progress bar AND your data that feeds a sparkline,

    – I have 7 date fields
    – when a date is current (or in the future), I want to trigger more green in a progress bar
    – if a date has past, I want to trigger red
    – I need the progress bar to visually reveal how “current” an item is according to the 7 dates

    Thanks

    Charlie

    • Hi,

      another great thing would then be, to hide the calculated column(s) in the DispForm.aspx…. Is there any possibility??

      At the moment there’s the HTML-code, I could set the CEWP there to have the color coded, but better would be not to have it there…

  2. Pingback: Christmas indicators « Path to SharePoint

  3. Love the site! However, I’m stumped on a very basic custom list, just to test. I’m using nested IF’s (I just can’t wrap my mind around the CHOOSE function…yet) to indicate a color (red, green). The column my IF’s reference is “Priority.” Unfortunately, when I try to enter the second calculated column with the DIV tags, SP keeps telling I’m referring to a column that does not exist, when in fact it does. I’ve looked over the syntax and can’t find any problems. Maybe you can help me.

    Here is the IF statement in a calculated column called “CalcColor”

    =IF([Priority]=”High”,”Red”,”Green”)

    Here’s the DIV statement I want in a calculated column called “CalcColorDiv”

    =””&[Priority]&””

    I don’t understand it. The first calculated column “CalcColor” works just fine and indicates the color properly. But for some reason, the second, “CalcColorDiv” won’t recognize [Priority] as a valid column. I’m sure it’s something small I’m missing. Any help is appreciated. Many thanks.

  4. Nevermind! Sorry everyone with all the comments. Even though both of my earlier posts had the second code sample cut off, I figured it out. I truly was referring to a non-existant column, since I copied Christophe’s sample. My color is contained in a column called “CalcColor” and not “Color,” which is what is used in the example. Sorry about my lack of attention to detail.

  5. Christophe,

    My next requirement puts me between the “Choose” and nested “IF” methods. I have a status list with nine options. “E” is in the fifth position for two of the choices. What should I try next, a different letter in a different position? Some of the choices also have spaces in between, E.G. “In Transition”.

    We’d rather not use any of the “numbering” lists.

    Thanks,
    Brian

  6. Brian: a first approach would be to use two criteria, for example word length and position of the letter “e”.
    As an example of combined criteria, see this post and my comments:
    http://www.endusersharepoint.com/?p=1047

    However, in your case numbering may be the right approach. So the first step is to understand why you don’t like numbering. Is it just for display reasons? In this case, you could use a calculated column – just for display – that removes the numbers.

  7. I may go that route, I had the coloring working based on numbers in a choice column. Then, would I create another calculated column to “shift” everything three spaces to the right? (drop the “1._” – space is the underscore)?

    Brian

  8. So, playing around with the formatting and I was looking at changing the “icon” of the traffic light. It appears you could use any of the special HTML characters and symbols. For my experiments I used the following “traffic light” formatting :

    ="<DIV style='font-weight:bold; font-size:24px; color:"&Color&";'>&lowast;</DIV>"
    Or
    ="<DIV style='font-weight:bold; font-size:24px; color:"&Color&";'>&mdash;</DIV>"
    

    A good list of characters and symbols are at these links:
    http://www.w3schools.com/tags/ref_symbols.asp

    http://www.w3schools.com/tags/ref_entities.asp

  9. Pingback: Apply color coding to your SharePoint lists « Path to SharePoint

  10. Christophe,

    What if I add more than ten entries to my “status” column (using example #3, a numbered list)? The tenth option no longer displays the correct color.

    Thanks,
    Brian

  11. Brian: that’s right, the above formula only takes the first digit.

    In your case you need to identify the position of the dot first. Use the SEARCH function for this:
    =CHOOSE(LEFT(Choice,SEARCH(“.”)-1),…)

  12. Hi Christophe,

    I have been using your calculated colums for colour coding quite a bit lately – thanks!

    I have a strange problem when trying to replicate Excel conditional formatting in a list.
    The column is called DecFormat and contains a range of numbers between 0 and 1.29 OR it may contain an asterisk ‘*’ or a hyphen ‘-‘.

    The formatting rules are:

    If cell value begins with ‘*’ then green bg black text
    If cell value begins with ‘-‘ then white bg black text
    If cell value between 1 and 1.29 then green bg
    If cell value between 0.9 and 0.999 then amber bg
    If cell value < 0.9 then red bg

    I thought this would be easy enough to do with the following:

    =”=0.9,[DecFormat]=1,[DecFormat]<=1.29),”green”,
    IF([DecFormat]”

    Of course the >= does not work if the column is a Single Line of Text and I lose all my ‘*’ and ‘-‘ if I convert it to a Number (but then the colour coding for the >= and < for text columns?

    Cheers
    Ben


  13. ="<DIV style='width:100%;background-color:"
    &IF([DecFormat]="*","green",
    IF(AND([DecFormat]>=0.9,[DecFormat]<=0.9999),"orange",
    IF(AND([DecFormat]>=1,[DecFormat]<=1.29),"green",
    IF([DecFormat]<0.9,"red","blue"
    ))))&";'></DIV>"

    :-S

  14. Christophe,
    Yes it works if the column is of type Number but I have issues that the imported data mixes types – sometimes it’s a number sometimes it’s text i.e. * or – …. so to accomodate both types of data the SharePoint column has to be Text.

    So the question is, is there a way that we can think of that a Text column (even if it has a number in it) be evaluated to meet the number range criteria?

  15. Great – that should do what I want: it works on a simple greater than, but when I try to use with an AND I don’t get the results I expect…

    I am sure it’s just my lack of syntax knowledge … i.e how do apply a VALUE to the second part of the AND ?



    IF(AND(VALUE(NovFormat)>=0.9,(NovFormat)<=0.9999),"orange",

    SharePoint saves that formula OK but the results don’t evaluate.

    Thanks for your help!
    Ben

  16. Pingback: Highlight rows in SharePoint lists « Path to SharePoint

  17. Christophe,

    I’ve got all this working and it’s an awesome mod. I’m not much of a coder so I could use some help here to go further.

    Now that we have Sharepoint columns displaying as HTML, is there some way that we can make the bullet a hyperlink so when it is clicked, it can take the user to some specified document library or other place?

    Thanks,
    Michael

  18. Ha! i actually got it working from many of your examples on your blog. Many thanks! I have a colored bullet that is also a hyperlink.

    My only problem now, is that it underlines the bullet and colores it dark blue. I’ll work with it to see what I can do but if you have an example that will speed my process, I’ll take it. Below is what is working for me.

    =CONCATENATE("<DIV style='font-weight:bold; font-size:24px; color:"&CHOOSE(RIGHT(LEFT(CostStatus,2),1),"red","orange","green")&";'><a href='","https://mysite.com/sites/mycustomers/web%20files/Forms/AllItems.aspx",,"' rel="nofollow">•</a></DIV>")
    

    Michael

  19. I think simply adding:
    text-decoration: none;
    in your style should work.

    Instead of the bullet, which is considered text, you could also choose to use pictures, like in my KPI example.

  20. Christophe, thanks again for your help. I will eventually try to figure out how to set a different URL for different items in the column.

    Michael

  21. I’m using images instead of the text because I could not get any style to work to eliminate the underline of a hyperlink.

    Now, I can get rid of the stupid purple box that it puts around the images I’m using. I’ve tried all sorts of combinations of style.

    Here is what is working but adds the box around the hyperlinked image:

    =CONCATENATE("<DIV><a href='","https://mysite.com/sites/mysite/web%20files/Forms/AllItems.aspx",,"' rel="nofollow"><IMG src='/sites/mysite/web%20files/"&(3-RIGHT(LEFT(CostStatus,2),1))&".png' /></a></DIV>")
    

    Maybe you can help on getting rid of that box!

    Michael

  22. Hello All,

    I mast be doing something wrong. I am simply trying to turn a row color red for announcements that are “pending” in the approval status. Any help on the calculated field would be great.

  23. Worked like a charm for me – thanks for your help!

    The added details in this post did the trick, it was very helpful.

  24. Works Great! Trying to find a way to use the filter on this column, currently when selecting the filter the end user sees the “” statement, I would prefer they see something along the lines of Low, Medium, High. Thanks

  25. Pingback: Daily Blog Post 04/01/2009 « Murratore’s Weblog

  26. Hello,

    Thanks for the learning. Unfortunately the following code in the calculated column actually displays the code in the list and not the intended result.

    What is wrong in:
    =”30,”red”,IF([Date Difference]>5,”orange”,”green”))&”;’>•”

    That shows:

    And not the bullet. Note that the conditions seems to work as the correct color is inserted in the formula.

    Thanks for the help.

  27. Welcome to my blog…Christophe!

    Did you follow all the steps described in the post, including the script in step 3? And have you checked the troubleshooting page (cf. end of the post)?

  28. Hello,

    Can you please help me to figure where i am going wrong with this syntax?

    I have three images that i am trying to display based on GO/NOGO condition…. But the following code is throwing error…. Apologies i am not a programmer…

  29. Hello Christophe,

    The step 3, that was the missing part! Mea culpa, mea maxima culpa.

    Thanks a lot for the help, it’s greatly appreciated. Everything works fine now :-)

  30. Sam, I think a couple quotes and slashes are missing in your formula. Try this:

    ="<DIV><img src='https://shared.spaces.intaner/Images/"&IF([Approval Type]="GO","go.gif'/>",IF([Approval Type]= "NO GO","nogo.gif'/>","return.gif'/>")&"</DIV>"
    
  31. Hi Christophe, i did try the code but the image is only shown for the ‘FALSE’ condition in IF statement. For all the TRUE values the column shows just the hyperlink; no images.

    =””,IF([Approval Type]= “NO GO”,”nogo.gif’/>”,”return.gif’/>”)&””

    Anything missing from my side? Thanks again for your advise.

    Cheerz,
    Sam

  32. Hi Christophe, Got it. I got rid of mutiple IF’s and modified the code as below and now the indicators are working as required. Many thanks for your continued support.

  33. Here is the code. I forgot to mention the syntax to make it visible in the blog.

    
    ="<DIV><IMG src='https://mysite/Images/App"&3-LEFT([Approval Type],1,2)&".gif' /></DIV>"
    
    
  34. Pingback: Color Coding - Formula Generator « Path to SharePoint

  35. Thanks for the posts, however I still can’t get this working, even with basic HTML. I have a calculated column with the following formula

    =”•”

    ..yes, I’ve stuck with one colour as I can’t get anything to work, and it’s still showing up as the HTML and not what you’d expect.

    I have the CEWP below the list with the relevant script put in using the source editor and we are using WSS 3.0.

    Can anyone spot anything that might be amiss!!

    • Apologies, I’ve missed the sourcecode on the html. However, I’ve used the formula generator to generate a simple formula for my list which states,

      "<DIV style='background-color:"&IF(Status="","Limegreen",IF(Status="1 Complete","Blue",""))&";'> "&Status&"</DIV>"
      

      however all I’m seeing is the html and not the text highlighted as expected. Is there anyway to simply test that the script is being called, as I’m getting stuck here. I’ve checked upper and lower case DIV in the script, but can’t see any other common problems I haven’t checked against.

      Thanks!

  36. Christophe, when I add a Group By to this list view, the tags show up instead of the color coding. Once I remove the Group By, the color coding returns.

  37. Hi Christophe,

    I have taken your examples and they work perfectly. I love this site…Kudos to you!
    I have a question because I am hitting a roadblock on a couple issues.

    Question
    When I use the Content Query Web Part to roll up and display Content types that contain your calculated column snippets, it does not render the same as it does in the list view even though I have the Content Editor Web part on the page with the required javascript to parse the CSS/HTML correctly.

    Any suggestions on what needs to be done to make the calculated columns display correctly in the Content Query Web Part?

    Any suggestions would be greatly appreciated.
    Again thanks for your posts!

    Eric Proshuto

    • The fix was really quite simple. The CQWP displays the HTML output escaped (just text)by default.
      First I added the HTML calculated column to be retrieved by the CQWP as shown by heather Solomon’s blog, then I edited the ItemStyle.xsl file in the Syle Library on the root site to render the data.

      To change the output from being escaped all you need to do is add this little snippet to the xsl select statement
      xsl:value-of select=”@KPIIcon” disable-output-escaping=”yes”

      Save and check in the ItemStyle.xsl file and refresh the page with the CQWP. No need to add the CEWP to the page, the html will render properly showing the desired format.

  38. Hi Christophe,

    Firstly, great website!

    Secondly, I’m stumped – what is wrong with this statement:

    =”<DIV style='font-size:12px; background-color:"&IF([OOF Type]="(1) Exam Leave", "Pink", IF([OOF Type]="(2) Vacation", "Yellow", IF([OOF Type]="(3) Business Trip", "Blue", IF([OOF Type]="(4) Study Leave", "Orange", IF([OOF Type]="(5) Maternity Leave", "Green", IF([OOF Type]="(6) Bank Holiday", "Grey", IF([OOF Type]="(7) Training", "Aqua", IF([OOF Type]="(8) Working From Home", "Red", IF([OOF Type]="(9) Sick Leave", "Purple")))))))))

    I have 9 OOF Types I want to apply different background color to but it causes an error.

    Any ideas??

    Thanks.

  39. Christophe,

    I’m trying to use the code in Example 5 but I don’t quite understand the use of the commas – can you explain?

    =CHOOSE(FIND(“e”,[Status],1),,”black”,,,,”limegreen”,,,”gold”,”red”,,,,,”darkorange”)

    Thanks

  40. Pingback: Case study: KPI roll-up in MOSS (Part I) « Path to SharePoint

  41. Great post, this is just what I was looking for. I’m having a slight issue tweaking this, I was wondering if you could help me out. I’m not really a coder, so I think I’m just not quite wrapping my head around this. Here’s what I’m trying to do:

    I’d like to change the color of a list column titled Event Name based off of another column, VIP Attendees. The Event Name is a single line of test, and VIP Attendees is a choice column (three VIPs, and None), that is a required field. I created a third column, named Color, and used your HTML generator (another great tool by the way) and modified the page using the script in a CEWP. The code I got of your generator is:

    =” “&[Event Name]&””

    What I’m getting when looking at a list view is: Test in the Color column when looking at a List View of the Calendar, and the Event is in blue when looking at a calendar view. Any ideas or suggestions would be greatly appreciated.

    Steve

    • Scratch that, I sort of figured it out. What I’m getting now, under the Color column, is the word red, instead of the Event Name being colored red. This is my code for the second calculated column:
      =””&[Event Name]&””

      Any ideas?

  42. Do I need to be running MOSS for this to work or is WSS ok? Being a newbie here I’m trying to determine if this will screw up my “view” list that I have prepared? Can I edit the view to add this code and make the color html work? Thanks ahead of time.

  43. Ok, I am in need of some basic help please.

    I have a list, it has a defined view. I want to add the Traffic Indicators as my Severity level as shown. I created a column Titled “Severity” which has 3 choices, High – Normal – Low. I created a column to house the icons Titled “Indicator”. I used the formula generator to create my code

    =”• “&[Indicator]&””

    I created a third new column Titled “Code” (as a placeholder) and stated this is a calculated column and entered the code above.

    In return my datasheet view shows the HTML as text as expected. Where do I add the additional code needed to display the icons? Sorry but I do appreciate the help here if possible. Thanks

  44. Hi Christophe,

    I tried that and it worked fine, although I am not at all familiar with HTML scripts… Your expalanations and examples are really comprehensive. I have 1 question though… Can you export to a Spreadsheet the SP list and how will the column containing the formula for applying the formatting look like?

    Thanks for your comments!

  45. Hi,

    Your post was referred to me and as I read and view, it seems like this will helped me to some pressure I had in our corporate portal. I’m not a developer but I was assigned to maintain our portal using MOSS2007. However, maintaining our portal is not the only task I have right now because there are enhancement given to me.
    One task I am pressured with is to have a color distinction for the Approval Status Column of our leave application system list in a calendar view. I filtered it by Organization column then by Approval Status column since they want to see a calendar view in each organization. Currently, I have calendar view in each organization which displays all application filed either Approved, Pending and Rejected. What we need is to distinguish through color coding its Approval Status like: Approved = Green, Pending = Yellow, Rejected = Red.
    Please help how can I do this. I find it hard to do it in Sharepoint Designer.

    Thanks.

    Jack

  46. Hi Christophe,
    I have applied the traffic signal formatting on the column but only the formula appears. Where I did I go wrong?

    Maraming Salamat! (Thank you very much in Filipino)

    Bing

  47. HEllo there, I’m trying to figure out the syntax to have the following column be background colored when chosen. I have 5 choices and want two colors:
    (5) High [to be red]
    (4) Moderate High [to be red]
    (3) Moderate [To be yellow]
    (2) Moderate Low [To be yellow]
    (1) Low [To be yellow]

    ALso, what tyoe of column should I be choosing?

    Thanks in advance for your help.

  48. This guide was brilliant, thank you. I have it working perfectly to display a bullet showing a RAG status of a project.

    However, I have applied exactly the same method to display a coloured number to indicate a risk level. The only difference is that my risk number is actually a calculated value (by multiplying two other columns). The number displays as a bold 25 (so I can see html is working as intended), but is always black.

    My colour column simply says =IF([Calculated Risk Level]=”25″,”red”)

    I am guessing it can’t capture the number for a calculated column. Any way around this?

  49. Hi Christophe, thanks for all the great tips, I implemented colour coding for Lists, Calendars and a Forms List, I found the Lists part relatively easy, I really struggled with the Forms List as you don’t seem to have any instructions for this, but I eventually figured it out, and I really struggled with the Calendar List until I realised I need ed to change the field it was using for the Text, but once I figured it out it worked great.

    I also implemented the tabs too, that was too easy ;-)

    Thanks again.

    Chris

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

  51. Is there any way to center justify the indicators in the column? I have the formula working properly (even figured out how to tweak it a bit – again, thanks Chris and all for the info on this site that helped me do it), and now I have been scouring the web trying to figure out how to display the data center justified so it looks a little cleaner.

    Byron.

    • Byron, you might try:

      text-align: center;

      Example:

      =IF(Action=”Time Saved (credit/deposit)”,””&[Hours (Annualized)]&””,””)

      • sorry. The text-align: center would go after/before the font color, like this:

        …=’font-weight:bold; font-size:12px; color:green; text-align: center;’…

  52. Hi Cristophe,

    I re-created the issues list from this example http://blog.pathtosharepoint.com/2008/12/09/color-coding-more-examples/. The columns traffic light, indicator, font color, background color are calculated columns and I copy pasted your formulas.
    Then I added an empty content editor web part under the list and pasted on the source editor the exact script from here http://blog.pathtosharepoint.com/2008/09/01/using-calculated-columns-to-write-html/.
    It wouldn’t allow me to save at all. It says “cannot retrieve properties at this time”.

    What am I doing wrong?

    • I think this is an issue with your SharerPoint server – it has nothing to do with the method itself. Maybe try again later, and contact your IT support if you still get the message.

  53. Christophe – I am trying to use a calculated column to show a red, green or yellow traffic light based on the created date of a document. If the Document was created 3 months + the bullet should be red; if the doc was created 2+ months, the bullet should be yellow; if the doc was created less than 2 months from today, the bullet should be green. I can’t get the formula to be correct. Any thoughts???

    =”<SPAN style='font-size:24px;font-weight:bold;color:"&IF([Created]<=”[Today]-90”,"Red",IF([Created]=”[Today]-20”,“Green”,””)) &”;’>• “

  54. Hi Cristophe,
    I finally foud out that the Dutch version of MOSS requires Dutch versions of the code. Got that working now, but the color coding itself returns ‘False’, whether I try Hex-codes or color names in English or Dutch. Could you help?

    This is the code I tried:

    =ALS(Fase="1_Genomineerd";"#008000";ALS(Fase="2_Interface_uit";"#FFFF00";ALS(Fase="Stroom_uit";"#FF0000";ALS(Fase="Uit_het_rack";"#000000"))))
    

    And this:

    =ALS(Fase="1_Genomineerd"; "groen";ALS(Fase="2_Interface_uit"; "geel";ALS(Fase="Stroom_uit"; "rood";ALS(Fase="Uit_het_rack"; "zwart"))))
    

    Or this, the English color names:

    =ALS(Fase="1_Genomineerd"; "green";ALS(Fase="2_Interface_uit"; "yellow";ALS(Fase="Stroom_uit"; "red";ALS(Fase="Uit_het_rack"; "black"))))
    

    The column Color returns ‘No” and the Format column :

    with all 3 variants.

    Your help will be appreciated.
    Regards, Pieter.

  55. Pingback: Maximum number of IF statements in a Calculated Column? « Path to SharePoint

  56. (Again, Kudo’s to Chris for this amazing site!)

    What I am trying to do now is basically the above indicator column, where the numbers and the icons change – I know how to make a list (thanks to Chris!) that has different colours, or a differnent icon, but I am not sure how to make a formula that, for example has the first option to be a circle, and the second to be a square, third to be a trianlge, etc.

    I found the link to the HTML codes, it is just getting a better understanding of the formula.

    Help?
    Byron

  57. I have a calculated column in a Sharepoint list that determines a color coding in text based on another calculated column that equals a score (number). Then that number determines the risk color. Right now the color is just text ex. “Amber” i would like the background color of this column to be amber but i cannot figure how to make this happen.
    Any help appreciated!!

  58. Great article and extremely helpful! I’ve done calculations but you and others who have contributed to your articles have taken it a step further. I’ve had no problems using these valuable tips from any of your articles.
    I will be posting links to your articles on my website with your permission.

    Big Thanks!

  59. To Dana:

    Instead of going into details I will point you to these articles I think your missing. Take your time and don’t give up. It takes some getting use too, but you’ll get it.

    Go to Dessie Lunsford’s article and create the columns accordingly. If this is not done correctly it will not work.

    http://www.endusersharepoint.com/2009/10/21/taming-the-elusive-calculated-column-referencing-a-multiple-lines-of-text-column-with-append-text-part-1/

    Then for the “Text to HTML” drop a Content Editor WebPart and click the source button and paste the code from this article. If this is not done you will only see the text (markup).

    http://blog.pathtosharepoint.com/2008/09/01/using-calculated-columns-to-write-html/

    Last but not least, create another column and call it background and use this function. Be sure and follow the Dessie’s article for your calculated column.

    =””&Status&””

    But change it accordingly to fit your needs. I think this is correct but try it out and I hope this helps….

    Joet

  60. Hello Christophe,

    I’m really a beginner in sharepoint but I have a big interest in your articles.

    I copy and paste your formulas in a calculated colomn but Sharepoint ask me to choose what the result of this calculation will be (text, numbers, etc…). I select text by default cause I guess number, date or other are not hte right format to choose. But SharePoint sned me back an error code. I can,t pass trough the first step of your procedure.

    What’s wrong with me ?
    Thanks for your help.

    Chris

    • Chris: text is the correct format. Some possible causes for the error message:
      – an error in a column name
      – a typo
      – the use of the formula in a non-English version of SharePoint
      – the use of styled quotes instead of regular ones

      If you need step by step assistance, note that Mark Miller and I are hosting a workshop on this topic this Wednesday.

  61. Hi,

    I have successfully implemented the traffic light system for my suppliers but if you change the view to the “preview panel” (much easier to use for large content) the html code doesn’t display the traffic lights. Is there a quick fix for this ?

    Regards,

    c

    • You can group by color using the “Color” column. In the current version, if you use the calculated column for grouping it won’t be converted to HTML.

  62. Hi Christophe, thanks so much for your blog, it’s been very helpful.
    In order to determine the RAG status we have been using a 5×5 matrix in a spreadsheet and the OFFSET function to return a value (all return values in the matrix are unique numbers). Do you know if it’s possible to do something like this in a SharePoint list using a formula
    For example…
    IF(Impact Score = 5) AND (Prob Score = 4) THEN (Gross Score = 11)
    IF(Impact Score = 4) AND (Prob Score = 5) THEN (Gross Score = 30) etc…..

    Thanks

  63. Thank you so much! I am new to SharePoint, and html coding was able to implement your instructions with little trouble! Though, I could not get it to display in Datasheet view for some reason. Thanks again!

  64. Pingback: A2life » Showing a list from another SharePoint Site

  65. Hey, Christophe – I’ve been trying to use the formula generator code inside the ‘traffic light’ code, and am messing up the syntax somehow.

    I’ve got this:

    =”•”

    but sharepoint keeps coming back with ‘error in syntax’. Can I not do it this way?

  66. Pingback: A2life » SharePointクロスサイトのリスト表示をJQueryで行う

  67. What am I doing wrong? I copied the html onto the clipboard and have tried pasting into the content editor web part, but all I can see thereafter is this

    =”

    I must be missing something. Can anyone point me in the right direction please?

    Thanks,
    Colin

  68. Hello,

    I am currently having a problem pasting your formula in the calculated column. Every time I change the column to the column I’m working with, it keeps saying it cannot publish the column as it cannot reference the column. No matter what column I use though it will not publish because it says that it does not exist. This is with 2010, though. So if there’s an issue, I understand.

    • If SharePoint says the column doesn’t exist, most likely… it doesn’t exist! Make sure your column is among the avilable ones, listed on the right next to the formula field.

  69. Hi,
    My colors are woking, however, I canot figure out how to center the traffic lights. Is there a way? Here is the exampke the code I use for my calculated column:

    =”• “&””

    Thanks so much for your help!

  70. i am new to sharepoint so i created a percentact complete column with =(val1+val2)/13 where val1 is one formula and val2 is another formula both the same but just couldn’t fit all columns to one formula. i’d like to add the progress bar that would use the %complete column as the information it will use. any help would be great

  71. Hi Christophe:
    Can your HTML Calc Column be used to simply show a negative number as red?

    Thanks-

  72. Thank you!!!
    This has worked all very well!
    Please explain how in the ‘Issues’ example above, the symbols and bullets are aligned with the text row. I am using the ‘traffic light’ version and my bullets are not horizontally aligned with the row of text. Any idea would be appreciated.
    Great wrok!
    Rick

      • Aligning the bullets is more than a pain for me … I just can’t work it out! I went to the link you suggested but didn’t see anything there that would help. Can you point me *please*? This is great stuff and I’d really like to get it good enough to present to my boss. Grateful for whatever you can tell me !

        MF

        • I’m also interested in understanding how to do the absolute positioning. I looked at the unicode link you provided, but the other options I tried were just as bad or worse. Is there a way to line everything up?

  73. Hello,

    Currently I’m looking at your script and got it to run awesomely. Great script. Definitely.

    The only thing that I would like to change is for the background color, I can’t seem to figure out how to only have a background color and no text. If I do

    =””””

    A ” still gets outputted. Granted, it’s extremely difficult to see, but visible none-the-less.

    Is there a way to change this?

    Thank you very much.

        • Sorry it took so long to get back to you on this. I did just try this, actually, and it didn’t work. It just prints out & n b s p; What my code looks like (I’ll put the nbsp with spaces so it doesn’t act like your comment) is this:

          =”$n b s p;”

          It correctly colors everything, which is great and a lot further than I’ve gotten before, but nothing.

          Thank you much for your help sir.

        • Hi Christophe,

          Am a newbie to MOSS, had tried your above eg and works prefectly, however same as Steve, I need the background column to show colour and not the text.

          However looking at your reply to Steve, could you provide the full statement? How should i use non breaking space () ?

          =””&[Issue Status]&””

          Thanks.

  74. Cool Trick, I was able to make it work, except for one problem.. in Sharepoint 2010, when the script was working and converting the text to HTML, the library tools buttons disappeared from the top of the page. Perhaps because those links are wrapped in divs and maybe they weren’t in older versions of sharepoint?

    • Surprising. Even if the buttons are wrapped in divs they shouldn’t be affected.
      I’d recommend that you check out my posts from 2010 for a version of the script more suitable for SP 2010.

  75. Searching around on the web suggests this is a common issue and adding any content editor web part knocks the library tools off the ribbon.

  76. Chris,

    The script does not seem to work against lists with collapsed columns, but it works just fine when the grouped column is expanded. When I expand a grouped column (that was set to collapsed as the default), I only see the underlying html and not the KPI colors.

    Any suggestsions?

      • Hi Christophe, I couldn’t find a blog entry with a working code for collapsed views for SharePoint 2010. Has this issue been addressed? Can you provide a specific place/date in your blog to look?

        Your site has been a life saver… Many thanks!

  77. I am running this script on a SharePoint 2010 server. What I have found is if the view has group by column then the color column no longer works. I guess it maybe SharePoint 2010 related issue. I did not see people complain about it in SharePoint 2007.

    Anyone know hot to fix it in SPS 2010 environment?

  78. Hi Christophe,

    Thanks for sharing this information; from this I have implemented a ‘traffic light’ system on a company wide sharepoint list which is producing some great results.

    We would like to take this a step further on a different project; I have a list with a calulated column which is multiplying two number columns together – I would like to display the output of this calulated column in different colors depending on the number value. Numbers <5 would be green; a number between 5-12 yellow and 15+ red. Do you have any suggestions? I'm thinking a combination of the traffic light method with an IF statement?

    Thanks in advance
    MOSS Newbie..

  79. Hi all, I must be missing something real obvious, but when I paste the code into a cewp I can just see the =”circle” (circle being a large black bullet point). I managed to get the calculated column to take the =CHOOSE(RIGHT(LEFT(Priority,2),1),”red”,”gold”,”limegreen”), but seem to be falling over at the CEWP

    Any help appreciated, Ben

  80. Hi, I’m using the stoplights for some status indicators. Some of the indicators will not apply so I’d like some other kind of icon.

    How can I do that? I have this calculated column now:
    =””

    So I’d like to have a value of “4” for not applicable?

    • Can someone help me with a formula to display 4 icons?
      traffice lights and then a black circle or something to display if not applicable?

  81. Great post! Thank you so much for the solution. I was able to apply the solution and display colored bullets depending on the “status” field in my SharePoint list. I am also able to display the status in colored text (as you explained in your solution). Question: is there a way to display the text status starting from a specific column (i.e. I would like to display (1) Not Started as simply Not Started (without the number selection))? Thank you, again.

  82. Hi
    Please help me with the cewp part,where to place the script part, I have placed the script part in html editor,my column is only showing the nam
    e of color based upon status colomn value bt not the traffic light

  83. Thank you so much for the script. I have a very simple problem I think. I am having the color show up in the column that has the DIV Formating code and not the column I want to show it in. I created a background color column with the formatting code and that is what its coloring. I need to color a choice column that is called MAS Notes.
    Any help pls?

  84. Hello,

    I need to produce a summary of the project health, the issues are categorized by impact i.e Schedule, Budget and Scope.

    Within this summary I need to be able to define the Impact status of Issues.

    When there are multiple issues for Budget for example, what is the worst color (Green, Amber or Red) Red being worst.

    Sample Output .. (if no issue exists for Budget, Scope or Schedule then Default = Green)

    Status Current Last Period
    Overall A R
    Budget G A
    Scope A A
    Schedule A R

    Any help creating this greatly appreciated.

  85. I am completley lost! I am very new to coding but I need to do something that is probably quite complex. I have one column, in a list, that asks the user to choose from a range of %’s indicating the level of completion on an action e.g 10%, 20%..100%. I want to create a calculated column that displays a traffic light bullet in Red, Amber or Green to show the RAG. The calculations would be that if the % is less than 50% then the colour is Red, if more than 70% then the colour of the bullet is Green, resulting in 50%, 60% and 70% being Amber..
    I get the IF statement but I don’t understand the html or where to put it. i have tried to put the html and formular in the calculated field but it comes back with an error saying wrong syntax. I need someone to tell me exactly what I need to write and where – Thank you in hope.

  86. Pingback: Using calculated columns to add color coding to your SharePoint lists | sarahlhaase

  87. Presentation wise this is a great and simple apporach. Kudos…..
    I however had a client trying to use the filter on the page and it showed the html(calculated column formula) on the page. Is it possible to colour the text in the actual dropdown rather than calculated column. In your example teh Priority column rather than the background column

    • I have done it for some customers (I even have a live demo with countdowns somewhere), but this is really tricky and upgrades might be an issue. An easier workaround was to simply disable the filter.

  88. Christophe, Kudos on an awesome site. My issue relates to displaying the calculated columns in the list. I created the HTML calculated column called Calc Prioirity, but I want the heading of the column to read Priority when it’s displayed on the site. Is there an easy way to do this?

  89. i have been able to get the first two calculated colums working but i need help writing the html script. Can anyone give me an example?

  90. Excellent tips although I am wanting to see if i can write a script that does the decision making (IF statements etc) on the fly as the script runs. The reason is that I have a large dataset that needs lots of columns colour coded differently and would rather write a single script then create dozens of calculated columns. I think i need to workout if i can tell which column I am in and work that into my IF statements. I have so far been successful in finding a function that allows me to check the column I am in.

    Can you help?

    • Well, I can understand your decision, and it’s always a trade off between maintainability and performance. This is the kind of stuff I have done for my customers, so you’re welcome to contact me for a quote :-). My SPELL framework has utilities to facilitate such customizations. This is for example how the matrix view color coding works.

      I have also created a mini-calculator, I’ll blog about this next week. The demo is already online (check out the Value field).

  91. Chris, I’ve been digging through your posts for a couple days now and can’t see to find anything that helps me. I’m just trying to do a simple calculated column that looks at a check box column and if it’s checked it turns the calculated field bold that’s all. However, no matter how I adjust the code I keep getting a syntax error in sharepoint. Can you please help me find what I’m missing?

  92. Thanks for sharing your knowledge on color coding in Sharepoint. It has been very helpful. I am using it to track project status in a similar way that you are using it for issue status. Thanks again!

  93. Hi!

    Thanks for an interesting post!

    Is it possible to change background color for each column in object edit mode based on the content type?

    Eg. I have a list based on 2 content types, C1 and C2. C1 is used for reporting an issue and C2 is for solving the issue. C1 includes X columns and C2 includes Y columns. The user who is responsible for issue solving is, when he/she is in edit object mode, presented X columns (based on C1) in color A and Y columns (based on C2) in color B.

    Possible?

    Thanks in advance!

    /Jonas

  94. Can anyone help me I am trying to color code a cell in a list in SharePoint 2013 Office 365. I have tried everything i could find on the Net and have not been able to get this working

  95. Christophe, I know it’s not your fault but is it just me or are people abusing your comments field just to drum up external links for their own sites? seeing a lot of nonsensical posts by dodgy posters advertising curling irons etc.

    • Sorry about that Chris. It’s true that I have seen a significant increase in spam recently, and if you have subscribed to my blog you see them before I have a chance to do the cleanup.

      I am getting tired of this too, and I am considering moving to a SharePoint blog where only authenticated users will be allowed to comment…

  96. I would like to color code my gantt chart not by task, but by team. I need to show a weekly/monthly view of projects going on in our group. We have three teams. I want the line on the chart to be color specific to a team. For instance Photo would be blue, Elec yellow and Instr green. Is there a way to do that?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s