Categories

SharePoint 2007 and 2010 - Color Coding Custom Lists and Tasks

SharePoint 2007 and 2010 - Color Coding Custom Lists and Tasks

Humans are visual by nature so creating a graphic icon for a list of items or tasks can be extremely beneficial when trying to sort through data. Without having to configure SharePoint Designer or writing custom code, you can create a simple color coded icon that will enhance your list tremendously in just a few minutes.

Tip - For a list of available colors, click here

  • Step 1 - Create a “Color” column in your list. This column can either be calculated using other data with in the list or selected manually on creation.

    Example:

    =IF(OR(Status="Not Started",Status="Deferred"),"Yellow",IF(OR(Status="In Progress",Status="Complete"),"Green",IF(Status="Waiting on Someone Else","Red")))

  • Step 2 - Create a “Status” column that is a calculated value. The output is a single line of text. Copy and paste this into the calculated field:

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

  • Step 3 - Add the list to your web part or wiki page
  • Step 4 - Edit the web part to show the Status column
  • Step 5 - Add a Content Editor Web Part directly underneath the List/Task Web Part
  • Step 6 - In 2010, you will need to load the below code to a location on your site in a text file and point the content editor to the location. In 2007, you can copy and paste the below code into the content editor settings box itself.

You have now completed you color coded list!

Want more???

Learn how to add a SharePoint Designer custom workflow to your business process: SharePoint Designer Sales Workflow

Learn about my team's SharePoint dashboard solution that makes SharePoint even more visually appealing for all SharePoint versions including WSS, MOSS, Foundation and 2010: SharePoint Dashboard Solutions

If you want to learn more about color coding lists and other ways SharePoint can benefit your business, please contact me at Anjali.Bharadwa@dmcinfo.com.


<script type="text/javascript">
  //
  // Text to HTML
  //
  var theTDs = document.getElementsByTagName("TD");
  var i = 0;
  var TDContent = " ";
  while (i < theTDs.length) {
    try {
      TDContent = theTDs[i].innerText || theTDs[i].textContent;
      if ((TDContent.indexOf("<DIV") == 0) && (TDContent.indexOf("</DIV>") >= 0)) {
        theTDs[i].innerHTML = TDContent;
      }
    } catch (err) {}
    i = i + 1;
  }
  //
  // ExpGroupRenderData overwrites the default SharePoint function
  // This part is needed for collapsed groupings
  //
  function ExpGroupRenderData(htmlToRender, groupName, isLoaded) {
    var tbody = document.getElementById("tbod" + groupName + "_");
    var wrapDiv = document.createElement("DIV");
    wrapDiv.innerHTML = "<TABLE><TBODY id=\"tbod" + groupName + "_\" isLoaded=\"" + isLoaded + "\">" + htmlToRender + "</TBODY></TABLE>";
    var theTBODYTDs = wrapDiv.getElementsByTagName("TD");
    var j = 0;
    var TDContent = " ";
    while (j < theTBODYTDs.length) {
      try {
        TDContent = theTBODYTDs[j].innerText || theTBODYTDs[j].textContent;
        if ((TDContent.indexOf("<DIV") == 0) && (TDContent.indexOf("</DIV>") >= 0)) {
          theTBODYTDs[j].innerHTML = TDContent;
        }
      } catch (err) {}
      j = j + 1;
    }
    tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild, tbody);
  }
</script>

Comments

Gibbs
# Gibbs
It's exactly what I was looking for ! But, I still have this #VALEUR! in the Status column not the point after loading the txt.

My system is also in french SharePoint 2010
Is this a language Problem?
Also as Olivier has mentionned. When I open my txt file in IE, ... empty and when I edit it from the file explorer, the text is here.

As a result in my webpart there's no color i have this instead
#VALEUR!
Dave
# Dave
Thank you VERY much!
Mariia Leathrum
# Mariia Leathrum
This is the best tutorial I've seen for SharePoint. The entire process worked like a charm. Thank you very much, Anjali for putting this together! You are at the top of my list when I search for help with other SP-related questions.
Pat
# Pat
Hi - love the tutorial...

One question - I want to do an AND statement on two different status fields but it keeps barfing on the context. Like below.

=IF(OR(Status="Checked In", Status="Build in Progress", Status="Build Completed", Status="Testing"),Yellow,
IF(AND(Status="Ready to ship",ReadMe Status="Reviewed"),Green,IF(Status="Checkin Pending","Red")))

Not a developer... any help greatly appreciated!!
Haley
# Haley
I realize this is an incredibly old article, but I'm working with SP 2007 so it was useful.

Everything worked great! That being said, when I go to sort the column by projects that are only red, yellow, or green, it only shows the <DIV style... code that had been entered in color status rather than an actual color or the word color.

Any help you can provide is extremely helpful, thank you.
Kayla
# Kayla
Wow. Thank you SOOOOO much for posting this. Exactly what I have been looking for. The preloaded status list in SP is nice, but doesn't automatically generate an icons for each list item. Thank you again for saving me from further bashing my head against the wall.
RR
# RR
How do I save the HTML Code in Sharepoint and get the link for it? ( I am using sharepoint2010)
Jhyng
# Jhyng
Thanks for this. It helped me instantly. Hope I can learn another process in the future.
Gee
# Gee
Can I have 2 columns with color coding? One for Current item status and one for Over all?
Eshetu Ejigu
# Eshetu Ejigu
This was the missing script on the txt file that i found on another site


John
# John
For SharePoint 2010 users, found a solution on the HAASE site under "SharePoint 2010 setup':
http://blog.splibrarian.com/2012/06/06/using-calculated-columns-to-add-color-coding-to-your-sharepoint-lists/
Just made a small modification to the xsl tag using SharePoint designer and it worked.
John
# John
Followed the instructions but I get the DIV code instead of colors.
I'm working in SharePoint 2010 and I put a link in the CEWP. Any ideas on what's wrong?
Also, in Step 2 you say, 'Create a “Status” column' but in the video you create a 'Color Status' column.
Kylie
# Kylie
I figured it out! :) For some reason it was set to enforce unique values:)
kylie
# kylie
I am having an issue, where I am actually trying to go back and use my list I made a while ago, and it only allows one entry of the high level type.. what would cause this?

So for example i have 6 capabilities that someone can initially select from, and each capability has various sub-capabilities that can be associated with it.

my inital test example used all 6 capabilities on their own line, with the color coding working, but then when i try to add another capability that already exists, with any sub capability, it throws an error saying only one of those items is allowed in the list, and cannot have more.

Any idea on what would cause that?
Gee
# Gee
used this and it worked great...HOWEVER, when the form had to be revised, the entire columns disappear. The worst is that the script isn't won't work anymore.

What am I doing wrong?
JD
# JD
How to achieve a Progress Bar instead of Status Color?


Thanks !
Connie
# Connie
Kylie, thanks a lot for your quick and useful answer.
The colors appeared with the 'expanded'-solution, but I found it a little bit unuseful as the group-by idea disappeared with this long list.
So, I turned it back into the 'collapsed' setting AND something strange happened - the system 'remembered' the colors!
So all in all - the solution is:
Set the group-by to 'expanded', save.
Thereafter, set the group-by to 'collapsed' - voila - you've got colors :-)
Kylie
# Kylie
Connie, I found another site, which suggested to set the Grouping as EXPANDED, in the view, and then the colors showed up correctly.
Connie
# Connie
I've got the same problem as # Kylie with the DIV code appearing instead of the colors in a Group-by view. It would be great if someone is able to crack the code.
Kylie
# Kylie
How would I get the HTML code to work, if I use grouping and sub grouping?

Like I have each row, and the person selects a capability, and then a sub capability, and I group by capability, then sub capability, but either way, with grouping, the color circles turn back into the DIV code line.
Tom
# Tom
Hi there

I could resolve the issues mentioned in my previous post however the script does not seem to work with the following two statements which are now without synthax errors:

RAG Overall
=IF(OR([Overall Status]="Some issues, being managed, needs to be closely monitored",[Overall Status]="Project on hold","Gold"),IF([Overall Status]="Serious issues, dates being missed, recovery plan required","Red"),IF([Overall Status]="Project on track","Limegreen"))

RAG Project Tasks
=IF(AND([Due Date]<[Today],[Status in %]>"79","Gold"),IF(AND([Due Date]<[Today],[Status in %]<"79","Red"),IF([Due Date]>[Today],"Limegreen"),IF([Status in %]="100","Black")))

What could be the issue?
Tom
# Tom
Hi there

I created some IF Statements required for a Project Mgmt Dashboard based on Sharepoint 2010. Unfortunately only the two statements for RAG Action Log and Issue Log so work. The other ones don't. Note I use your HTML script.

RAG Overall
=IF(OR(Overall Status="Some issues, being managed, needs to be closely monitored",Overall Status="Project on hold"),"Gold",IF(Overall Status="Serious issues, dates being missed, recovery plan required","Red"),IF(Overall Status="Project on track","Limegreen"))

RAG Project Tasks
=IF(AND(Due Date<[Today],Status in %>"79"),"Gold",IF(AND(Due Date<[Today],Status in %<"79"),"Red",IF(Due Date>[Today],"Limegreen"),IF(Status in %="100","Black")))

Risk Map
=IF(OR(AND(Probability="Low",Risk Impact="Low"),AND(Probability="Low",Risk Impact="Medium"),AND(Probability="Medium",Risk Impact="Low")),"Limegreen",IF(OR(AND(Probability="Low",Risk Impact="High"),AND(Probability="Medium",Risk Impact="Medium"),AND(Probability="High",Risk Impact="Low")),"Gold",IF(OR(AND(Probability="Medium",Risk Impact="High"),AND(Probability="High",Risk Impact="Medium"),AND(Probability="High",Risk Impact="High")),"Red")

RAG Action Log
=IF(OR(Status="Not Started",Status="Deferred"),"Yellow",IF(OR(Status="In Progress",Status="Complete"),"Green",IF(Status="Waiting on Someone Else","Red")))

RAG Issue Log
=IF(OR(Severity="Major"),"Gold",IF(OR(Severity="Minor"),"Limegreen",IF(Severity="Critical","Red")))


Do you guys see what's wrong in it?

Thanks in advance.
Rosa
# Rosa
Thank you so much. It works. It's easy to do... I'm so happy.
Mima
# Mima
Hi,

I tried it and unfortunately it does not work on SharePoint 2013.
I added at the beginning the link to:
https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js
Please help!
Thanks!
DemureEccentric
# DemureEccentric
This is the best thing ever. Thank you.
Henrik
# Henrik
Hi,

I'm using SharePoint 2010 and activated the Ajax automatic refresh, but than it destroy the script. The script works perfectly on manual refresh (F5), but with AJAX enabled on the webpart it will after the first refresh return the code:

Artem
# Artem
Another solution: http://www.sparqube.com/SharePoint-Status-Indicator/. It is easy to use, not code required, Web UI designer. It is commercial add-on.
Jen
# Jen
This was super easy to follow and worked perfectly. Thank you so much for sharing your knowledge with the rest of us!
Victoria
# Victoria
I was able to follow this and it works, thanks so much for putting this together. My only issue is that when the status field is blank the dot is a very dark grey which is hard to distinguish from the green. Is there a way to change the color for the blanks, or to make the green lighter?

Many thanks.
Christopher
# Christopher
Thank you so much for this artical, i have been trying to setup RAG status for projects and now I have. Thanks!!!!
Dave
# Dave
Excellent instructions! I was able to get it to work. However, since then, I've tried using it on a SitePage, with three columns, but the color won't work. I only get black dots. In the Content Editor Web Part, it won't let me "hide" it, the "hidden" option is ghosted.
Stuart
Excellent article and video. It was just was I was looking for. It worked perfectly. We used to have our own SharePoint server in house but recently moved to a hosted solution where things are tied down much more - so I didn;t know if this would work. Unfortunately, we've also gone from 2010 backwards to 2007, but the steps and code worked perfectly.

I don't usually leave feedback but given how well this worked and the effort that went into sharing this information I had to leave a big thumbs up.

Thanks :-)
Karl
# Karl
Is there a way to set the dots so that they are inline with the text. Mine are pushing much lower and when I try to adjust the vertical spacing in the list, it just makes it worse.
Matthew B
# Matthew B
I followed your instructions on a new list with default status options. However, I'm trying to add this to a list with different options. I have:

Not Started
In Review
In Progress
In Implementation
Completed
On Hold
Waiting for information
Future Project

I tried this:
=IF(OR(Status="Not Started",Status="Waiting for information"),"Yellow",IF(OR(Status="In Progress",Status="Completed",Status="In Implementation",Status="Future Project",Status="In Review"),"Green",IF(Status="Waiting on Someone Else",Status="On Hold","Red")))

and I get that it refers to an invalid column, so I changed Status to Task Status, which is what I renamed status too and then I get an invalid syntax error on this:

=IF(OR(Task Status="Not Started",Task Status="Waiting for information"),"Yellow",IF(OR(Task Status="In Progress",Task Status="Completed",Task Status="In Implementation",Task Status="Future Project",Task Status="In Review"),"Green",IF(Task Status="Waiting on Someone Else",Task Status="On Hold","Red")))

What am I missing??
Matthew B.
# Matthew B.
When I enter:

=IF(OR(Task Status="Not Started",Task Status="Deferred"),"Yellow",IF(OR(Task Status="In Progress",Task Status="Complete"),"Green",IF(Task Status="Waiting on Someone Else","Red")))

to create my calculated color field SP gives me this error:

The formula contains a syntax error or is not supported.
Bob S.
# Bob S.
Impressive. It worked really well.
Thank you very much!
Joshua
# Joshua
Thank you so much for taking the time to create this presentation. It was extremely useful!
Paul S.
# Paul S.
Sorry. I forgot to escape the HTML "(DIV style="...") whatever (/div)". Hope that is better. Thanks. PaulS.
Paul S.
# Paul S.
So, when using this color coded calculated columns concept in a tasks list, and the tasks list is set to email the assigned user when the task is created, the HTML shows up in the StatusColor field sent in the email as "
whatever
". How do I either get either the HTML to render or hide the column in the email? Or something? Thanks, Paul S.
Siddhi
# Siddhi
Hi, I want help with grouping my task list by when the tasks are due. For example:- due today, due this week,due next week, due this month, due next month and no due date. I have been able to filter the list according to the due but am not able to group them> please can u give me a task due calculated column which can solve this problem. I will be very grateful. Thank You.
sa
# sa
I just figured out why the colors are appearing black.

I created my "Status" column as a Calculated column instead of a 'Choice' column. This caused the code not to work.

However, I do need the 'Status' column to be calculated column. How do i modify the script to accept 'Calculated' values instead of value already being chosen?



Anjali
# Anjali
Does your color column refect the correct color?
sa
# sa
Hello,
I followed your steps however, the Color Status field only shows Black circles. The below is the logic i followed:

Step 1:
=IF(OR(Status="4",Status="5"),"Green",IF(OR(Status="10",Status="16"),"Yellow",IF(OR(Status="20",Status="25"),"Red")))

Step 2:
="
"

Step 3: Copied your code.

Any suggestions?


Agi
# Agi
Hi,

I loved your video and instruction. You saved me lot of time!Thank you very much!

I got a question, I set up as you wrote down. It is working really good. However one of my user can not see the dote , he saw the code of the dote, do you know what happend? One day ago he could see the dot but today he sees just the code. Do you have any idea what is the problem?
For the other user it is working.
From what does it depend on see the dot or the code?

Thank you for your help in advance!

Josh
# Josh
To get this to work with 2007 I had to set it up like this:

3 Columns,
Color "Calculated Column" with the If statement

Status "Choice Column" with Not Started, Deferred,In Progress, and Waiting on Someone Else

and a Light "Calculated Column" with the Div Statement.

and I just changed the view to hide the "Color" column.
Umair
# Umair
This is great. Excellent to the point explaination Angali. Other websites have a solution but very hard to follow. You are a life saver. Keep up the good work.
Pete
# Pete
Nothing is working for me. Won't even let me create a Status column with that line of code in the formula.
Douglas
# Douglas
Anything on Shading a column or a cell/field?
Michelle
# Michelle
I am in Foundation SharePoint 2010 and when I create a column with the calculated field I get an error

"The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column.
Chris
# Chris
This is exaxctly what I am looking for, but I am recieving an error "circular reference (the calculated column refers to itself)" when using your example for status. I tried making the Color logic refer to a choice column called mychoice, so the user makes the choice, the color column assigns a color based on that choice, etc... Am I misunderstanding the logic here? In your example above, it seems like the color column refers to a "choice" field called status yet the status itself is a calculated field? I am on Sharepoint 2007, thank you!
Timmah!
# Timmah!
AMAZING...my co-workers are going to think I'm super intelligent!

Thanks for the awesome tip!
Joel
# Joel
Thanks for the excellent tip! One problem I am having though....... when I sort one of the fields, the color coding dissapears. Refreshing the page returns the list to it's previously sorted state. Is there a way to get this to work?
Anjali
# Anjali
Thanks for reading my blog. Unfortunately it looks like your syntax for the logic is off. An IF statement works like this: IF(condition, then, else). It may help to review these available calculations http://msdn.microsoft.com/en-us/library/bb862071.aspx. Hope that helps.
LH
# LH
What is wrong with this logic?

=“Green”,IF(OR(Status=”On Schedule”,”On Budget”),”Yellow”,IF(OR(Status=”Schedule Concerns”,”Budget Concerns”,”Red”,IF(OR(Status=”Serious Schedule Concerns”,”Serious Budget Concerns”)))

I am attempting to install a three button option and have never done this before. I am more of a user, not developer. Also, What is the follow up logic for the status column? Thank you
Shiral F
# Shiral F
Hi Anjali,

Thanks for this.. I was able to tweek this to create a rolling health monitor for 320 store that looks awesome..

Thanks Again..
Shiral.
Rosendo N.
# Rosendo N.
Anjali,

This is great thank you. I have grouped the tasks so they can be collapsed or expanded in the web part. Once I did so color status has reverted back to code. Any idea how to get it to work if the task is grouped?

Much appreciated.

RN
Sugandh
# Sugandh
Thanks Anjali,
Thanks a ton!
I tried out the same and it worked
Please make sure that you drag the content editor webpart under the list for this to work. It wasn't working for me untill I brought the content editor webpart to the bottom.
Riteja
# Riteja
It worked for me! Fantastic Post... thanks!
Douglas
# Douglas
Hi Anjali
Thank you for your great post.
It has been very helpful.Anything on Shading a field?
JP
# JP
Hi Anjali
Thank you for your great post.
I had similar work done with images displayed please refer URL http://www.sharepointcommunity.com/spcblog/Lists/Posts/Post.aspx?ID=77

after migrating to 2010 it is displaying as html code. I did change the content editor script from sharepoint location as described in step 6 it still not showing up the image. Any help would be appreciated
thanks
Francisco
# Francisco
Works great but when I set the view in the following way I only see the html output not the color.

Group by Category Field and make the list expanded.
Ollivier
# Ollivier
It works but only with the list default display, with the others styles ... Thx
Anjali
# Anjali
Don't worry too much about the blank IE box as the same thing happens to me. One thing you really have to be sure of is that the Content Editor box is directly under your list actually in the same web part layout box or it will not work.
Ollivier
# Ollivier
Hi.
It's exactly what I was looking for ! But (always a but), I still have the formula in the Status column (with the right color written) and not the point after loading the txt.
problem of language (system in french) ? One strange thing, when I open my txt file in IE, ... empty and when I edit it from the file explorer, the text is here. May be because of the security on the server (does'nt accep txt file ...) Thx a lot.

Post a comment

Name (required)

Email (required)

CAPTCHA image
Enter the code shown above:

Categories