Using calculated columns to add color coding to your SharePoint lists


There are a variety of ways to add color coding to your SharePoint lists and document libraries, from embedding custom code on your page to creating data view web parts with conditional formatting in SharePoint Designer. The trick is determining which method works best in your situation. Not sure how to do that? Start with the basics:

  1. Understand your business requirements.
  2. Build your user adoption strategy.

You have to start with your basic business requirements. You need to determine what data you will be storing, how you will be storing it and when you will be surfacing it to key audiences. Since many of these questions may impact your use of color coding and conditional formatting, they’re critical to understand.

You also need to know how dynamic your users want their solution to be. Do they want to be able to tweak the specific shades of green you’re using? What if they decide that they no longer want to demarcate completed items in grey–instead opting for a nice shade of puce? If you build your color coding solution using custom code or a SharePoint Designer add-on, your average business users will (most likely) not be able to make color modifications on their own.

Once you have the basic business requirements down, you need to figure out what add-ons and additional features your users are dying to get their hands on. Do they get excited about having high-priority items display in red text? Do they want all the completed items in their weekly project tracker to show up in light grey font? Or are they color blind and see no value in having any color differentiation of their SharePoint data? If your users have a wish list of desired functionality that includes color coding a document library, list or calendar, you should be running (not walking!) to find any and every solution that will make their dreams come true. Delivering on your users’ wish list items will ensure smooth user adoption and easier change management.

I’ve used a variety of methods for color coding my SharePoint lists and document libraries. I’m using this blog post to share one of my color-coding favorites–the Path to SharePoint solution that uses embedded HTML strings in SharePoint calculated columns to apply color coding. Here’s an example of the types of color coding you can do with this solution:

Setting up this type of color coding is fast–literally 5 minutes of work per list view you want to customize. But navigating the steps for the first time can be daunting. This blog pulls all the steps together in one easy-to-follow process. We’ll start with a business scenario, then move on to setup in both MOSS 2007 and SharePoint 2010. We’ll finish things off by discussing ways you can customize your new color coding.

Business scenario:

My company uses a SharePoint issue tracking list to store help desk support requests. All SharePoint users have the ability to go to this list and create new support requests as needed. When they go to the list and click on the Add new item link, they get a New Item form with the following basic input fields:

  • Title
  • Description
  • Priority
  • Due Date

When a user fills out the New Item form and clicks OK, the help desk ticket information is automatically routed to our help desk team for resolution. To ensure that high priority help requests are easily visible, our help desk manager wants the Priority field in our SharePoint list view to have automated color coding. He’d like all support requests that are High priority to display with a red icon. All Normal priority requests should display with a yellow icon and all Low priority requests should display with a green icon.

And while the team is currently using MOSS 2007, they have plans to upgrade to SharePoint 2010 in the next 3 months. So any color coding solution(s) that we build must work in both platforms.

Solution:

There are a few ways to get this done–including building custom data view web parts in SharePoint Designer. But since our help desk manager wants to modify the colors of his priority icons on the fly (without using SharePoint Designer), we’re going to build out these custom color indicators using HTML encoding embedded in calculated columns. We’ll be using the Priority column that comes with the default Issue Tracking list as the trigger for our color coding. (In other words, the value set in the Priority field will determine whether green, yellow or red color coding is applied to our new calculated field.)

While this solution works in both MOSS 2007 and SharePoint 2010, the setup is a bit different in each platform. Here are the required elements for both platforms:

MOSS 2007 required elements SharePoint 2010 required elements
  • HTML script file (provided by Path to SharePoint) that will render your new HTML tags
  • Document library to house the HTML script file
  • SharePoint list with a Priority column
  • Content Editor Web Part (CEWP)
  • SharePoint list with a Priority column
  • SharePoint Designer 2010

The remainder of this blog post outlines MOSS 2007 setup steps, SharePoint 2010 setup steps and cool ways to customize your new color coding.

MOSS 2007 setup

  1. Go to http://pathtosharepoint.com/Downloads/Forms/AllItems.aspx, expand the HTML Calculated Column group header and download a copy of the TextToHTML-v2.1.1 text file.
  2. Upload the HTML script file to a document library on your site. As you can see in the screen shot below, I uploaded my file to a document library named Site config files. It doesn’t matter what document library you use to store your file, but make sure all site users have at least read-only access to the library you use. This will ensure the HTML script file can be accessed to render the color coding you’re going to set up.
  3. Validate that the SharePoint list or document library that you want to color code is set up with a Priority field with the following basic settings. (Note that these settings come with the out-of-the-box Priority site column, which you can add to your list or document library.)
  4. Browse the color formatting options at http://blog.pathtosharepoint.com/2008/09/01/apply-color-coding-to-your-sharepoint-lists/ and determine which option (e.g. traffic light indicator, font color shading, background shading, KPI icon indicator) you’d like to implement.
  5. Copy the formula provided for your desired formatting. In my case, I’m looking to implement a traffic light indicator so I copied the following formula:
    ="<DIV style=’font-weight:bold; font-size:24px; color:"&CHOOSE(RIGHT(LEFT(Priority,2),1),"red","orange","green")&";’>&bull;</DIV>"
  6. Add a new calculated column to your SharePoint list. Paste the HTML formula string you copied into the new column’s Formula field. Click OK to save your changes.
  7. When you return to your list view, you’ll see your new calculated column displaying with <DIV> tags. Not very attractive.
  8. To correct this issue, we’re going to add our HTML script to the page. Click on Site Actions > Edit Page.
  9. Click Add a Web Part and add a new Content Editor Web Part to your page.
  10. Move your new Content Editor Web Part (CEWP) to the bottom of the page. You need it to display below your list view web part.
  11. Click on the CEWP’s open the tool pane hyperlink.
  12. When the web part configuration panel appears, place your cursor in the Content Link field and enter the location where you stored your HTML script file. Click OK to save your changes.

That’s it! Your formatting is applied and your page should now display your new color coded field. Here’s a screen shot of my finished view, complete with a Priority Indicator column that displays red, yellow and green traffic lights that correlate with the priority level of each help desk request:

Now that you’ve set up color coding on one list view, you can continue adding more color coding to this view or add color coding to other views. Note that you will need to add one CEWP to each list view page that you want to color code. There is no limit, however, to the number of calculated columns with HTML encoding you can add to your list.

SharePoint 2010 setup

  1. Validate that the SharePoint list or document library that you want to color code is set up with a Priority field with the following basic settings. (Note that these settings come with the out-of-the-box Priority site column, which you can add to your list or document library.)
  2. Browse the color formatting options at http://blog.pathtosharepoint.com/2008/09/01/apply-color-coding-to-your-sharepoint-lists/ and determine which option (e.g. traffic light indicator, font color shading, background shading, KPI icon indicator) you’d like to implement.
  3. Copy the formula provided for your desired formatting. In my case, I’m looking to implement a traffic light indicator so I copied the following formula:
    ="<DIV style=’font-weight:bold; font-size:24px; color:"&CHOOSE(RIGHT(LEFT(Priority,2),1),"red","orange","green")&";’>&bull;</DIV>"
  4. Add a new calculated column to your SharePoint list. Paste the HTML formula string you copied into the new column’s Formula field. Click OK to save your changes.
  5. When you return to your list view, you’ll see your new calculated column displaying with <DIV> tags. Not very attractive.
  6. To correct this issue, we’re going to need to modify this new calculated column. Open your site in SharePoint Designer 2010.
  7. Click on Lists and Libraries in your SharePoint Designer 2010 navigation bar. When the list of your site’s document libraries and lists display, click on the list or document library we’re working on.
  8. When the list or document library details page displays, look for and click on the name of the view you’re applying color coding to. In my case, I am updating the All Issues view.
  9. Once your view opens, click on of the fields that are displaying those ugly

    tags. Notice that the <xsl:value-of> tag in the bottom right-hand corner of your screen lights up in yellow once your field is selected.

  10. Hover over the <xsl:value-of> tag until a black dropdown arrow appears. Click on the dropdown arrow and select Edit Tag…
  11. A dialog box will pop up with the following text:
    Place your cursor between the last quotation mark and the closing > bracket. Now type the text disable-output-escaping=“yes”
  12. Your Quick Tag Editor box should now read like this:
  13. Click on the Quick Tag Editor box’s green checkmark icon to save your changes. Your SharePoint Designer page will refresh and your color coding will appear!
  14. Click File > Save to save your changes.

That’s it! Your formatting is applied and your page should now display your new color coded field. Here’s a screen shot of my finished view, complete with a Priority Indicator column that displays red, yellow and green traffic lights that correlate with the priority level of each help desk request:

Now that you’ve set up color coding on one list view, you can continue adding more color coding to this view or add color coding to other views. Note that you will need to modify the <xsl:value-of> tag for each column you want to display in HTML format. There is no limit, however, to the number of calculated columns with HTML encoding you can add to your list.

Cool ways to customize your color coding

Now that you have your fields set up, you may want to start customizing the look and feel of your new color coding. Changing out the colors that display is quick and easy:

  1. Find the new color(s) you want to use. A wide variety of HTML color choices are available. Here is a site that features a variety of HTML colors that are supported in all browsers: http://www.w3schools.com/tags/ref_colornames.asp
  2. Once you find a new color you’d like to use, note the color’s name.
  3. Go to your SharePoint list’s List Settings page.
  4. Find and click on your new color-coded calculated column.
  5. Look at your field’s formula and find the words redorange and green. These are HTML color names that are currently being displayed on your SharePoint site. To modify these colors, all you need to do is replace these color names with the new color names you want to use. If, for example, you wanted to change the color orange to the HTML color Violet, all you need to do is highlight the word orange and replace it with the word violet. Then click OK to save your changes.

Here’s a picture of my list view now that I’ve changed orange to violet. Notice that my “normal” priority items are displaying a violet icon:

But what if you want to make other changes–like using a different trigger field? It is quick and easy to change the name of the field you want to trigger your color coding from. Just go to your calculated column, find the word Priority and change it to the name of the field/column you want to use.

The more difficult bit is changing the valid values that you want to trigger off of. The color coding formulas provided assume that you have a choice field with valid values of:

  • (1) High
  • (2) Normal
  • (3) Low

If you want to trigger color coding off of different values, you will need to tweak your calculated column formula. Fortunately, the fantastic folks at Path to SharePoint have already anticipated this need and have written a follow-up blog post with more formula examples. Take a look – http://blog.pathtosharepoint.com/2008/12/09/color-coding-more-examples/

About these ads

About Sarah Haase

Librarian living in a SharePoint world
This entry was posted in General SharePoint solutions, Tips & Tricks. Bookmark the permalink.

135 Responses to Using calculated columns to add color coding to your SharePoint lists

  1. Stuart Pegg says:

    If you want to skip the XSL step, you could use our free HTML Calculated Column:
    http://www.pentalogic.net/sharepoint-products/free-stuff/html-calculated-column

    • The Red Baron says:

      No need To use pentalogic neither go to designer to write extra code.
      Follow all the steps given by Sarah Haase
      i.e. steps 1 to 3,
      in step 4 all you need to do is select the type of calculated column as number instead of single line text and select the the checkbox as add to default view as selected.
      You r good to go.
      I was working on the same issue yesterday where client needs Calculated column and no third party installers. Also the client needed, no changes in sharepoint designer.

      • Rasmus says:

        All I get in my Status color column is #VALUE! and I follow the 4 steps by Sarah Haase, and did like u said to change the type to number.. need some help!

      • Sarah Haase says:

        What version of SharePoint are you using? And at which exact step are you seeing the #VALUE! string?

      • Yolandamac says:

        The Red Baron,

        I have never left a comment in a blog; however, I want to send you a huge THANK YOU!!! I am working in SharePoint 2013 and have been trying to figure out how to color code a list item for 3 days now and nothing. I have added a CEWP and html code and a million other suggestions made by various other blogs and simply changing the column type to number from single line of text did the trick!

        Thanks again!!

  2. Yes, but with Sarah’s you can do it yourself. With Pentalogic’s, it has to be installed on the server. Business users don’t have access to the servers.

  3. Brad says:

    This solution does not work if you apply Show Tool Bar in Edit WebPart.

    • Brad says:

      Actually, I take that back. I think my problems are more complicated then that. I got this to work on test list but not on the live version. After I add the Tool Bar, I’m not able to select the field which has the HTML tags. The entire view becomes selected when I try.

      • Sarah Haase says:

        I tested this out, but was unable to replicate your issue. Here are the steps I followed:

        1. Add a Priority column to my list.
        2. Add a Priority Indicator calculated column to store my traffic light indicator formula.
        3. Go to my list and populate my new Priority fields.
        4. Go to my site’s home page and add a new list view web part. Make sure that my new Priority Indicator field displays (albeit with the unattractive
          tags).
        5. Go into SharePoint Designer 2010 and to edit the tag.
        6. Go back to my SharePoint site and validate that my traffic light appears as desired.
        7. Go in and modify my web part, turning on the Show Tool Bar option.

        After taking these steps I was able to select items without issue.

      • Brad says:

        Thanks for the Reply Sarah. I have followed all the steps. I think the problem is related to my list being grouped on two fields and collapsed. For me to follow the steps I have to create the view expanded, edit the tag in SPD, then change the view to collapsed. The last step is to edit the Web Part to Show the tool bar. When I am able to get the colored bullets to display, the list becomes unstable. Sometimes I’m able to select a row, sometimes I can place a checkmark on a row but once I move the mouse off the row, the checkmark goes away.

  4. Tiago says:

    Hi Sarah, although it’s a possible approach, I find it too heavy on the JS side. MOSS pages are known for having their huge and complex HTML markup composition (still must be coming from the old timers at MSFT…) and the JS script mentioned basically selects all the on the page. This is bad. And it is even worse on a MOSS page, which is already heavily fustigated by HTML (the famous nested tables), CSS, javascript. That is almost insane. In my laptop I had to wait at least 5 seconds for the JS to complete on a very small list (15 items). I’ve had a lot of cases where I though “hmmm JS can do it”, but in practice, it brings a lot of issues, and this is just one of them, so it must be used carefully. Even with jQuery and selecting just the specific elements I want, its still too troublesome, thanks to the HTML which goes as well against W3C standards…
    Not to even mention, if something changes in the HTML page, which could cause the code to crack, you’re done.
    For me, this is not worth the trouble, sorry for that… Either use the old boring OOTB options, or just use a custom programmed column…
    I sure hope MSFT changes a lot in MOSS 15. Because MOSS 2010 is in a lot of aspects inheriting still a lot of “evil” from MOSS 2007.

    Regards,
    Tiago

    • Tiago says:

      Just a correction, when I mentioned “(…) and the JS script mentioned basically selects all the on the page. This is bad.(…)” I forgot the part so I meant:
      “(…) and the JS script mentioned basically selects all the on the page. This is bad.(…)”

      • Tiago says:

        Now I figured out the messages are filtering the tags, so I meant ““(…) and the JS script mentioned basically selects all the “TD” on the page. This is bad.(…)”

  5. Larry says:

    I’m trying to get one column (Status) in a list to pull from another column (Utilization) in a list and display the appropriate traffic light color (Sharepoint).

    70 but 80 but 90 to display the saved red traffic light

    Example:
    Utilization data entry is 74, I want a yellow traffic light to appear in the Status Column.

    I’ve tried the examples already provided on the page but for some reason it didn’t work for me.

  6. Megha says:

    The solution worked great on the list, however when I add the list view webpart to another page in SP 2010, it is showing the DIV tags!

  7. Paul says:

    I really appreciate it ! Works fine for me in Standart View, but if I select DataSheet View or Export to excel it is showing the DIV tags. I couldn’t find where to change it. Any idea ? Thanks in advance,

  8. Sara says:

    This works great if I show “all items” in my list. However, when the list view web part is showing a list view with grouping, it shows the DIV tags. For example, I am grouping on owner. When you expand the owner group within the webpart, instead of showing the formatting, it shows the tags.

    • Sarah Haase says:

      I avoid displaying my taffic lights on Grouped views, as I also have trouble getting them to render appropriately. Would love to hear any solutions folks have found for this…

  9. John says:

    Hello Sarah,

    I just tried the above step on SPD 2010 and after I copy the disable-output-escaping=“yes” into the formula I get an error? – Failed setting processor stylesheet : 0×80004005 : the value of the ‘disable-output-escaping’attributmaonly be ‘yes’ or ‘no’.

    I have verified my spelling and tried it sevaral times. Please note I did not download the TextToHTML-v2.1.1 file (not authorized error was displayed). But since I have 2010, I did not think I needed it regardless.

    Any ideas what is happening?
    Thanks…

    • John says:

      I found my error….I cut and pasted disable-output-escaping=“yes” and when I re-typed “yes” I recognized the quotes were in not in the same format. Now it works!

  10. Elsie says:

    Re; The solution worked great on the list, however when I add the list view webpart to another page in SP 2010, it is showing the DIV tags!
    Sarah’s answer was: You’ll need to modify the tag on EACH page you want the Calculated column to display.
    Question: I went to sharepoint designer, the instructions above only cover modification for list, but not page. Can you please advise me how to make this change?

    • Sarah Haase says:

      The changes are the same–the changes just need to be made to your page instead of your list view. Use SharePoint Designer’s nav tree on the left to find and open your page.

  11. Lukas says:

    I followed your user guide step by step but when creating the indicator column it always tells me, that the syntac I’m using is wrong?

    • Sarah Haase says:

      If you’re copying and pasting the formula, I’d double check to make sure you haven’t dropped any characters. If that fails to resolve the issue, I’d try hand-keying the formula in. (This should help to ensure none of the characters are being changed during the copy/paste.)

      • Lukas says:

        Hi Sarah,
        thanks for the speedy reply. I did double check the text I copied and I also keyed in the formula manually but it still tells me it is an invalid formula. Are there any SharePoint Settings (security settings), which might prevent me from using HTML code in calculated columns?

      • Lukas says:

        Alright, I used SharePoint Designer to create column and then it worked :o)

  12. Aj says:

    Hi Sarah,

    Great solution, I have a requirement for a color coded SharePoint list such as a list of towns indicating the danger level i.e. red = high, amber = normal and green is low.

    Each town has a specific level of threat, how can this be implemented. The result would be to have the following columns town (back ground color coded) and danger level (color coded included the word i.e. high, low)

    I hope this makes sense,

    Thanks

    Aj

    • Sarah Haase says:

      When I need to background shade several cells within a list item, I usually opt to use conditional formatting in a SharePoint Designer data view web part. Have you considered going this route?

  13. matthew blackburn says:

    When I click to download a copy it wants me to sign in with an MSID?

  14. raj says:

    Hey Sarah,
    i am just trying to get same functionality like you. But when i am adding formula to calculated columns then in list view its not showing my formula, showing its self “#Value!” as you said its supposed to show formula before i apply to html script. please let me know what is the cause of showing “#Value!” in calculated field.

    • Sarah Haase says:

      That’s the standard symbol that something in your formula doesn’t compute. I’d try re-copying the formula or keying it in by hand to ensure you have everything in its place.

  15. raj says:

    Hi Sarah,
    very nice article.in my recent project client want color coding with datasheet view. Do u have any exerience color coding with datasheet view, if you have please share. please suggest if there is any way to achieve that.
    Thanks

  16. Artem says:

    Another solution:
    http://www.sparqube.com/sharepoint-status-indicator/
    easy to use, No XSL code required, condition editor, but it is not free.

  17. S. Anthony says:

    Would love to find a workaround for those of us without SPD access to hid the div tags. Free preferred :)

  18. Heather says:

    Hi Sarah! This is awesome!! Thank you so much for figuring this out and sharing it with the rest of us. You are greatly appreciated! I was able to get this to work for my task list, however I cannot get it to replicate on the homepage, even though I have done this coding to the “all task” view I am working with and it is selected in the view option for the web part on the home page, but it will not show on the homepage. Any ideas what I am doing wrong here?

    Many, many thanks!!
    -Heather

    • Sarah Haase says:

      Are you seeing the

      tags on your home page? Keep in mind you need to modify XSL tags (if you’re on SP2010) or add in the CEWP (if you’re on MOSS) for each page you want to display custom color coding on.
  19. Srinivas says:

    Hi Sarah,
    Thanks for the article. I got the status indicator symbols correctly, but when i filter by the status indicator column, it is displaying the formulas. I need to get the values instead of formula. Please suggest me .

    • Sarah Haase says:

      Are you trying to filter using column headers or a view? And are you filtering against the Priority column in your list or the status indicator column? I’d recommend filtering on the Priority column.

      • Ram Priya says:

        Hi Sarah,

        Like Srinivas Said, even I have a problem when trying to filter the calculated formula Columns. If you click the filter on Priority Indicator column the Ugly ‘Div’ formulas are displayed, Even though you are gogin to do a explicit filter , how to hide the formulas being dispalyed there?

      • Sarah Haase says:

        You can filter against the calculated column that is displaying your traffic lights–using either the column header filter OR the view filter. But remember, when you filter a column you are filtering against the contents of that column. And in this case, your calculated column’s contents are the HTML DIV tags. Your nicely colored traffic lights only render on the display side–they are not the actual content stored in your SharePoint field.

        One way around this issue is to avoid filtering on the calculated column you have set up. Rather than filtering on my calculated column that contains the DIV tags, I filter against the field my calculated column is reading from. In this blog post, I created a Priority Indicator calculated column that displays traffic light icons based on the values populated in the Priority column. If I were going to do any filtering in this scenario, I’d filter against my Priority column–not my calculated Priority Indicator column.

      • Ram Priya says:

        Hi Sarah,

        Fine let them filter it using ‘Priroty’ Columns only. But in case when accidently they click on ‘Priority indicator’ column Filter it thows a Ugly ‘Div’ commands. How to get rid of it.. is there any way to Hide the filter or filter values from user?

      • Sarah Haase says:

        No. This solution is driven by the DIV tags; if there is a desire to eliminate them, I believe a new solution is needed.

      • Ram Priya says:

        Hi Sarah,

        I have found the option myself from another blog to remove the filter.. and here are the steps. Hope that helps future sharepoint experts.

        Open the desired sharepoint site in sharepoint designer
        • Select Lists and Libraries from the Site Objects
        • Select desired list / library
        • Select the desired view from the views list where you want to remove to remove filter
        • Click on advance mode
        • Search for the inside
        • Now add following desired attribute to all desired columns. (All column are )
        as

  20. Claire says:

    This is excellant, works a treat. Thanks Sarah :)

  21. Pingback: SharePoint Designer 2013 #SPD2013 « SharePoint (and Project Server) Shenanigans

  22. Pingback: A look at SharePoint Designer 2013 - The Microsoft SharePoint Blog

  23. Jack De Blasi says:

    Hi Sarah,
    This works great and your explanation of how to do this is the best on the web…searched for a while before I found this. It’s working for me…but I also get an “Error on page.” message in the bottom left of the SharePoint page. Here’s a copy of the text and I’d appreciate any guidance. If I remove the edit from the tag the “Error on page” goes away. I’m using 2010.

    Webpage error details

    User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; InfoPath.3; MS-RTC LM 8; .NET4.0C; .NET4.0E)

    Timestamp: Thu, 8 Nov 2012 07:31:12 UTC

    Message: Sys.InvalidOperationException: Type SP.EnumerableArray has already been registered. The type may be defined multiple times or the script file that defines it may have already been loaded. A possible cause is a change of settings during a partial update.

    Line: 1717

    Char: 42

    Code: 0

    Thanks, Jack

    • Sarah Haase says:

      Hi Jack –
      Unfortunately, I’m not able to re-create the error on page. Are you using IE8?

    • Jack De Blasi says:

      Yes, IE8. I’m going to work with our SP Admin as I believe there may be a ‘larger’ issue not related to your fine work. I’ll ping you again if we eliminate other possibilities. Thanks Sarah.

  24. Sistemas KWD says:

    Hi Sarah,
    I follow the steps of the solution and only at the end we have found a problem in teh view. He have done the changes in the SPD 2010 and I look the red cicle, save and when I go to the view in Sharepoint I look the text
    =”<DIV style='font-weight:bold; font-size:24px; color:"&IF(TiempoCaducidad•”

    Please, can you help me?

  25. Hi Sarah,

    This works great until I save in SharePoint Designer, when I click the preview icon or refresh the relevant webpage i get “Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.”

    I doubt this is down to permissions as the account I used to do the customisations has full control /site coillection admin access and is in the Farm Admin group too. I checked the logs and no joy there either

    Hope you’ve come across this before?

    Chet

    • Sarah Haase says:

      Since you are able to make (and save) your changes in SharePoint Designer, this is clearly not a permissions issue. More likely, you have made a mistake entering your code in SharePoint Designer or have entered the code in the wrong location. I recommend backing out your changes and trying it again from the beginning.

      • Thanks Sarah,

        i left it for 5 mins and it now loads correctly after a few refreshes :)

        however when attempting to display the item menu I get the error “This item is no longer available. It may have been deleted by another user. Click OK to refresh the page.”

        If I click the ckeckbox for any individual item, only the “New Item” option in the ribbon is available and the rest are greyed out. The only way in is to click on the title to open the item then click “Edit Item” – very strange and should keep me busy for a few more hours today.

  26. Natalie Birindelli says:

    Hi Sarah,
    This is very helpful. My users want to be able to select the priority indicator themselves rather than utilize a calculated column to render. How do I go about doing that so it is more manual rather than calculation based? Your assistance would be appreciated!

  27. Martin Braun says:

    Here’s an example using the same technique to display “Traffic light” images from /_Layouts. Change the Column name (Project Status) and status values (Red, Orange, Yellow) to suit your needs:

    =””

  28. malene vestergaard says:

    I have tried to follow the guide by I get a “The formula contains syntax error or is not supported” when I try to save my calculated field. I use MOSS 2007

    • Sarah Haase says:

      That means something in your formula is not correct. I would suggest copying and pasting it again or typing it in manually. If you’re still having trouble after that, post the formula you’re using here as a comment and I’ll take a look at it.

      • malene vestergaard says:

        Dear Sarah,
        This is the formula I use. I have now tried to type it in manualle but I get the same error message when I hit save.

        The formula contains a syntax error or is not supported.
        Troubleshoot issues with Windows SharePoint Services.

        =”=’font-weight:bold; font-size:24px; color:”&CHOOSE(RIGHT(LEFT(Priority,2),1),”red”,”orange”,”green”)&”;>•”

        I hope you can point to where the problem migth be.
        Thanks in advance

      • Sarah Haase says:

        Check your email for an updated formula…

      • malene vestergaard says:

        Missed a ‘ but unfortunately it does not change anything.

        =”=’font-weight:bold; font-size:24px; color:”&CHOOSE(RIGHT(LEFT(Priority,2),1),”red”,”orange”,”green”)&”;’>•”

      • malene vestergaard says:

        Hi Saral – finally got it to work together with one of our SP experts. We used a slightly modified calculated field, but it works :-)
        =”•”
        My status field is a drop-down list with three value: red, yellow and green.

        By the way do you have any experiance with changing the layout of the Our Of the Box KPI List e.g. adding custom fields to the view?

      • Sarah Haase says:

        I know you can’t add/modify columns on KPI lists for MOSS 2007 without using custom code. I believe the same holds true for SharePoint 2010, but I haven’t tested to confirm.

  29. Heather Clark says:

    I’d love to give this a try – but everytime I try to access the Path to SharePoint site to get the text file, I’m prompted to log in. In all likelyhood, I’m doing something wrong…. but it seems pretty simple. Go to library, download file.

  30. Heather Clark says:

    Has anyone been able to get this to work with views that group items? Part of the requirements of the list I’m working with is to be able to view these traffic lights when grouped by a particular field (in this case, we’re rating the performance of particular individuals). When I use this solution in the all items view, everything works perfectly. When I edit the views in SPD for views with groupings, I get the following error even though everything in SPD for that view looks great:

    Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

    Correlation ID:9c433f38-0027-4310-a84c-929e191fda14:

  31. Jithin Chandran.B.G says:

    I want to change the color of the ‘Title’ to red when its priority is High.
    suggest me its formula

    • Sarah Haase says:

      The HTML formatting discussed here alters the look and feel of calculated columns–not core columns like the Title field. If you want to shade the background color of your Title field or change its font color, I’d recommend looking at conditional formatting via a data form web part.

  32. CM says:

    Hi Sarah,

    This post was SUPER helpful. I have a couple of questions though as I’ve exhausted the trial and error route. In the indicator column I no longer want to base it on the priority column. Instead I would like to base it on another calculated column. That column is an output of the 1, 2, and 3 values for high, normal and low. I get errors every time I make the slightest edit to the formula. The formula that I’ve been using is below. Thank you in advance!!

    =”•”

    • Sarah Haase says:

      Candice – You can make this kind of change. All you need to do is modify the “source” field that your formula is pointing to. In the example formula provided below, note the word FieldName. This is the name of the “source” field you want to render color formatting based on. Just change the words FieldName to match the name of your calculated column and the formula should work.

  33. Hi Sarah,

    I implemented this on a basic project status updates list however I cannot edit the GANTT view in SPD to edit the the tag?

    Any help please?

  34. Richard Firchau says:

    Sarah,
    I have setup a calculated column in a list to display a color-coded “traffic-light” based on a percentage in another column. The display works fine like you discussed above. However, the column filter still shows the text containing the “” tags. Is there a way to modify how the filter works so that it would display the color also? I know how to disable the filter for a column, but it would be nice to filter using the colors instead of the text. Any ideas?

    • Sarah Haase says:

      Unfortunately, I don’t have a resolution for this scenario. I’d recommend you use the percentage column to do your filtering OR build a data form web part to display conditional traffic lights based on values in your percentage column.

  35. Paul says:

    Sarah this is exactly what i need (red, amber, green) but i don’t want to use a Priority column like in the example. I’m getting #value. :-) I didn’t know the difference between the lite and the full version of the script, or whether to download the .htm or .txt version. Which is it? Even when i say “save target as”, it tries to save the .txt file as .htm. I overrode that once but didn’t fix my #value. In the fomula examples you gave, i edited and changed Priority to [RAG text]. I assume i need to change what position it’s looking for a particular character to help the formula discern what it finds? Am i going about this too difficultly? :-)

    • Sarah Haase says:

      Hi Paul –
      Several problems here by the looks of it :) You want to download the HTM file, not the TXT file. And yes–you want the full version not the lite version. When you click Save As to save the file, just key in the file extension HTM manually if it is looking to overwrite. For best results, I recommend doing a complete walkthrough of the setup steps in SharePoint as written (and using the Priority field). Once you get that working successfully you can work on tweaks–including changing your columns. Changing the values you’re looking for in the columns gets dicey very quickly–especially if you’re not very comfortable working with the calculated column code. Hopefully you have a dev you can ask if you get stuck on formula modifications…

  36. John says:

    Hi Sarah,

    Great information. If I don’t have access to SharePoint designer 2010 and can’t install it due to admin restrictions, is there any other way to fix the fields showing the ugly tags in the form view?

    Thank you,

    John

    • Sarah Haase says:

      If you want to incorporate color-coding for SharePoint 2010 using the method I proposed, SharePoint Designer 2010 is a necessity. If that’s off the table, you’ll have to research other options. Possibly an add-on tool or the HTML calculated column offered on Pentalogic.

  37. Pete says:

    Thanks for the write up – one question – I am using a custom field that has not had data populated in older records (Sharepoint 2010). Is there a good way to catch the #VALUE exception output and just make it a black bullet?

    • Sarah Haase says:

      I’m afraid I don’t have a formula fix for that yet. Anyone else?
      In the meantime, can you populate a standard value in all the older columns to ensure that the #VALUE exceptions don’t display?

      • pete.ikusz@thomsonreuters.com says:

        Hello Sarah,

        I managed to find a way:
        =”0,CHOOSE(RIGHT(LEFT(RAG,2),1),”red”,”orange”,”green”),”black”)&”
        ;’>•”

        Now the problem is sorting – the drop down for filtering leaves something to be desired and doesn’t always work correctly.

        Any thoughts there?

        -P

      • Sarah Haase says:

        Are you referring to the

        tags that appear in the filtered view? That is an unfortuante side effect of this solution.
  38. DC7 says:

    Hi Sarah,
    I tried your approach to display the color coding in List and it worked successfully. However, when I click anywhere in the list item and opent it or update the .. . shows in the DispForm/EditForm.aspx Please help on this one.
    A big thank you for your contribution!

  39. DannyE says:

    You do not need Text2HTML or SPD perse to get HTML code in Calculated Columns to display.
    Check out http://e2.nl/icc

  40. Kent Dixon says:

    Hi Sarah,
    I did as you said and made the “Priority” column and the example works great. Can I take values from a calculated column and do the same thing? I have a formula that calculates from a date to a system date, figures out the workdays and if the workdays exceed a threshold, in this case, 15 days, the formula returns “Red”. I tried to modify your code so I could get the indicator to light red based on seeing the value coming from the calculated column.

    Kent

  41. Clayton says:

    Hello
    I would like to nest 8 of these images (columns R1,R2,R3,R4,R5,R6,R7,R8) together in one calculated column (=R1&R2&R3&R4&R5&R6&R7&R8), but when i try the images are alligned vertically. Is there a workaround to get them horizontal.
    Regards
    Clayton

  42. Jackie says:

    Hi, I used the KPI option on my site and it all worked brilliantly, only problem is when I now try to login to the page this is on I get HRESULT: 0×80020009 (DISP_E_EXCEPTION)) error. Anyone else had this issue, all the pages that do not use the solution here work perfectly

  43. Karl says:

    Thanks for detailing all the steps. I use pathtosharepoint regularly. One thing that continues to annoy me is the alignment. Is there a way to fix the vertical alignment of the traffic symbols so that they are in line with the text and not appearing lower?

  44. Sarah – this is amazing and quick and it works – thank you.
    However, I have a related issue that is driving me nuts and I would very much welcome your advice please:
    When I opened my list in SharePoint Designer 201o to modify it. I recieved the message “File is under source control do you want to check it out?”
    So, I selected ‘Yes’, made the changes described in your post but now I cannot see how to check the file in, and I think that it is because of this that the only person who can see the pretty traffic lights is the site collection administrator who made the changes. Everyone else sees the HTML tags.

  45. OK – scrub that – I’ve worked it out.

    1. Open the site in SharePoint Designer
    2. Click ‘All files’ in the left-hand, site objects panel
    3. Select ‘Lists’ in the ‘All files’ panel
    4. Richt-click and Check-in the list in question.

    Thanks again for your blog here Sarah. Thanks to you, my list looks great now.

    John.

  46. Tej says:

    Sarah – Thank you for sharing this. I am having a challenge with nested “” tags show up. I follow these steps for Traffic light and I see the color code. I am trying to use a progress bar example and am not able to display the color. I copy the HTML tag into a file and it displays fine, so I know the syntax is correct. Here is the nested tag that I derive in the calculated column =””. Any assistance would be greatly appreciated. I am using Sharepoint 2010

  47. Tej says:

    Sarah – Thank you for sharing this. I was trying to implement a Progress bar and it is not displaying the color. I went through this example and was able to display the traffic light color. Followed the same steps for Progress bar and the color does not appear. Here is the final value I get from the calculated column “”. I copy this code into a html file and it displays fine, so I think the syntax is correct. Any help will be greatly appreciated. I am using SP 2010.

    • Tej says:

      oops, I guess the code did not get through, let me try again by removing the brackets around the DIV tag …… DIV style=’background-color:Beige;’>DIV style=’background-color:yellow; width:20%;’>/DIV>/DIV>”. It is a nested DIV tags

  48. Gilbert says:

    Hi. i noticed that after pasting the code in step 4…..

    [Add a new calculated column to your SharePoint list. Paste the HTML formula string you copied into the new column’s Formula field. Click OK to save your changes.]

    if you set data type to number before clicking OK, everything works well without having to go through the SPDesigner stuff….tested on sharepoint 2013
    thanks otherwise

    • DannyE says:

      I have coined the phrase Inline Calculcated Columns
      See http://e2.nl/icc for detailed (technical) explanation on why and how this works on 2013 and also older 2010 environments.
      It elleviates all the hassles of adding extra javascript script or XSLT code. Does not require SharePoint SPDesigner and, best of all, works on every View where you include the Calculcated Column

  49. The Red Baron says:

    oK if I Click on a field, the Edit item View pops up, and the color codes are again seen as HTML codes. Any help on that?

    • Sarah Haase says:

      The HTML codes should be stored in a Calculated column. By design Calculated columns do not show up as editable fields. I recommend checking your field type to confirm you have the codes set up correctly.

  50. Antonio says:

    Hi Sarah and thank you very much for your tips. I had a problem in downloading this file
    http://pathtosharepoint.com/Downloads/TextToHTML-v2.1.1.txt
    Which I believe is the most important to make the list to work.
    It require username and password.
    Do I need to be registered somewhere? How can I get that file and try if also in my list all this works?
    Thank you for the help.
    Antonio

  51. StarfishCI says:

    Wow, thanks to your simple instructions this works beautifully in a regular view. How can I get the RAG indicators to show up in a view displayed in a web part on a page, though? To complicate things, our MS-hosted SharePoint 2 is part-way through migration from SP2010 to SP2013 so I have to use SP Designer 2013 and it is a total pain to have to hack the code as described at http://www.sharepointblog.co.uk/2012/11/a-look-at-sharepoint-designer-2013/

  52. Jack says:

    Hi Sarah,
    I used your disable-output-escaping=”yes” successfully on my list. Thanks so much for the solution. Can you tell me how to make this work for the preview pane view that is available for a list ? On the preview pane view, there isn’t a tag available.

  53. Jack says:

    edit for the above: there isn’t a xsl:value-of tag

  54. Pingback: Using calculated columns to add color coding to your SharePoint lists | Sarah Haase « Collins CreativeCollins Creative

  55. Hi Sarah, I am trying to create a traffic light system for a date column. This is related to if a user is in date for training courses. Example, green if in date, amber if coming to the end of the time period and red if out of date. How do i add say 365 days to 15/10/2013 and have the traffic light as green. but to change to amber once 350 days has passed?

  56. Tom says:

    It is a great article. Thanks! I noticed that I started getting errors after I applied it.
    List does not exist.

    The page you selected contains a list that does not exist. It may have been deleted by another user.

    Even I deleted that list, I still get the error in that specific team site! Have you got this error before?

    Thank You

  57. Tammy says:

    Thank you for this solution…it was exactly what I needed and it worked flawlessly.

  58. Nimal says:

    Excellent Article!

  59. Douglas says:

    Hello Sarah,
    Could you please let me know what should I do if I need a fourth colour?

    Many thanks!

  60. Vinay says:

    Very Nice article.. Thank you!!!!

  61. Scott says:

    Great article Sarah and a great use of utilizing calculated fields as well as HTML, brilliant. I have seen another spot where a SharePoint user discussed adding Images instead of colors that you could reference. The references would be using a conditional statement instead of applying a DIV.
    Here is my example.
    =IF([Coding] = “(3) Bad”, “red.gif”, IF([Coding] = “(2) Ok”, “yellow.gif”, IF([Coding] = “(1) Awesome”, “Green.gif”)))

  62. FELIPE QUINTAS DO AMARAL says:

    Hello Sarah Haase!

    This is a very good example of how to use calculated columns to use traffic lights!
    It worked!

    I have just a quickie question:
    There is any way in the formula from calculated column to align the traffic light in the center?
    I have tried but it didn’t worked!
    =”•”

    Could you please help me with this?

    Thanks in advance,
    Felipe.

  63. Laura says:

    I can’t thank you enough for this tutorial! I have been trying to do this for MONTHS, but have absolutely no experience with HTML, or anything beyond basic Excel formulas. There are a lot of blogs that give you the code you need, but I never knew where to use it.. I am a total beginner. After stumbling across this blog, I finished my project in 2 hours, and it looks exactly how I wanted it to!

  64. Ira says:

    would this work without choice option – because i have table being imported from access – but just need color coding for the specific cell- and this seems only for the choice menu – is there an option to add this color traffic symbol if the column is not a choice?

  65. todd says:

    Hello – do you have a similar solution for SP 2013?

  66. ArunDarly says:

    Hi Sarah Haase Your color coading post helped me to slove my problem
    but iam getting one problem by going through it that is, while filtering coloumn wise it showing that “…” code which was writen in the calculated coloumn .I want to show colors in coloumn wise filter please help me to slove that problem thankig you

  67. Melinda says:

    HI Sarah,
    There was a previous question without an answer….Thanks for detailing all the steps. I use pathtosharepoint regularly. One thing that continues to annoy me is the alignment. Is there a way to fix the vertical alignment of the traffic symbols so that they are in line with the text and not appearing lower?

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