Color coding is very useful to highlight key items in a list. For example in an issues list, you may want to draw the users’ attention on items with high priority.
In the picture below, I have aggregated 4 different ways to do it:
This customization was entirely done through the SharePoint UI. Let’s see how.
The method
I am simply going to apply my post about using calculated columns to write HTML. For each example, we need to figure out the formula that will create the HTML string. Then the script, included in a CEWP, will make it behave as real HTML.
The formulas
In my example, I am applying color coding based on the priority of each issue: red for high, yellow for normal and green for low. Note that the formulas below were built for an issues list, formulas for other lists or libraries may be slightly different.
So here we go, please refer to the above picture to see the result:
– Traffic light (actually a big bullet!):
=”<DIV style=’font-weight:bold; font-size:24px; color:”&CHOOSE(RIGHT(LEFT(Priority,2),1),”red”,”orange”,”green”)&”;’>•</DIV>”
– Indicator (reusing the default SharePoint KPI images):
=”<DIV><IMG src=’/_layouts/images/KPIDefault-“&(3-RIGHT(LEFT(Priority,2),1))&”.gif’ /></DIV>”
– Font color:
=”<DIV style=’font-weight:bold; font-size:12px; color:”&CHOOSE(RIGHT(LEFT(Priority,2),1),”red”,”orange”,”green”)&”;’>”&Priority&”</DIV>”
– Background color:
=”<DIV style=’font-size:12px; background-color:”&CHOOSE(RIGHT(LEFT(Priority,2),1),”red”,”orange”,”green”)&”;’>”&Priority&”</DIV>”
Simply paste your preferred formula in a calculated column, add the script on the Web page as explained here, and voila!
Update: check out this post for more step by step examples!
Hi! This is great. We’ve been needing something like this in the past. We’ve had to open up SharePoint Designer in order to get it to work like the way you did. The one question I had is what if you want to base the color coding off a different column? We wanted to base it off a status column (open, closed, in progress, etc.) and I’ve been unable to change your code to fit that. Any words of wisdom?
Hello Christ,
I did it finally, but instead of get a color bar I just get the word “green”, “red”… I would like to get the color bar, could you please tell me what I should I add in the formula????
I used:
=IF(Status=”open”,”red”,IF(Status=”in progress”,”orange”,”green”))
Josh, there’s a follow up article with more detailed explanations:
https://blog.pathtosharepoint.com/2008/12/09/color-coding-more-examples/
Thanks Amita 🙂
In your case, instead of CHOOSE you can use IF statements. For example:
IF( [status]=”open”, “red”, IF([status]=”in progress”, “orange”, “green”))
Hello,
I am currently trying to do almost the same exact thing, except we are using a call log and I would like to be able to have a choice of Open and Closed with Open being Red and Closed being Green, is there any way I can do this
Well, how about this:
IF( [status]=”open”, “red”, “green”)
Christophe – I am trying to add this code, tweaking it for “approval status” but when I try to post this code:
=”•”
I get this SP Error:
The formula contains a syntax error or is not supported. at Microsoft.SharePoint.Library.SPRequestInternalClass.AddField(String bstrUrl, String bstrListName, String bstrSchemaXml, Int32 grfAdd)
at Microsoft.SharePoint.Library.SPRequest.AddField(String bstrUrl, String bstrListName, String bstrSchemaXml, Int32 grfAdd)
Any Thoughts?
Thank you!
drag the webpart to the bottom
You are most probably not from the US. If using a foreign layout you might have to replace , (commas) with something else. I’m from Denmark. It works if I use ; (semicolon) instead of ,
Hello Christ,
I am trying to do the same using this formula, but after click ok, I got an error message, could you please let me know the complete sentence for this formula?????
Thanks! That worked like a charm.
Have you thought about applying the color coding to other sorts of lists, like a calendar? Our clients are always looking for ways to implement color coding into the calendar web part.
When I use the Traffic Light method the column width is very large. Is there a way to control the width?
Pingback: Applying color coding to SharePoint list : Justgeeking
Amita: I am working on enlarging the scope of this method, and I hope to publish updates in the weeks to come. That’s what I already did for grouped views, based on the feedback on my initial script.
For calendars: I have nothing ready yet. For now, you can play with the script – for example replace ((TDContent.indexOf(“<DIV”) == 0) with ((TDContent.indexOf(“<DIV”) <=12) and see what happens. Also, see Ben Bradley’s comment here:
https://pathtosharepoint.wordpress.com/2008/09/01/using-calculated-columns-to-write-html/
Ryan: what do you mean by “very large”? One way to reduce the width is to use a shorter column name, for example “!” or “::”.
You should also be able to include the width in the formula, though I haven’t tried it myself.
Thank you for this post. I’ve tried other codes provided by different posts with no luck. I actually applied your code and it’s working beautifully.
I’m not a developer by trade so I need further assistance from you. I’m trying to apply it against the % Complete column (>=.9,”GREEN”, etc.) and I’m not sure on how to change the code to make it work. I used the indicator code but I received broken images. Can you help me with this?
Thanks in advance!
I figured it out. I got the formula to work using Nested IF statements.
OK. I’m glad you find this post helpful 🙂
Very very nice!
I am trying to use the IF statement above instead of Choose – not the best with code: Status is the field and WIN, LOST are choices….
=”•”
This worked for me. Thank you – I love this site….
“=”” “
Pingback: My SharePoint scripts tested in Safari « Path to SharePoint
Pingback: Should you replace Excel with SharePoint lists? (Part II) « Path to SharePoint
Pingback: Progress bar + color coding « Path to SharePoint
Christophe,
For months now my boss wanted me to color code a list we maintain to highlight priority and status. Until I discovered your website, I could not get it done. You went above and beyond what anyone else has ever done in helping us overcome some pitfalls and glitches (self-inflicted). Thank you for your commitment to making SharePoint a tool we can use in a more tailored fashion.
I am definitely a novice when it comes to Sharepoint development, so bear with me here. 🙂
I have a calculated column in a Sharepoint List called “Days Overdue” and I am trying to create another column called “Overall Status” which compares the information in the Days Overdue column and sets it to a green or red background based upon the information present. I can get it to work for text – that’s about it.
=IF([Days Overdue]>-1,”Red”,”Green”)
Can someone provide some assistance? Thanks!
In my examples, the color is decided by the function:
CHOOSE(RIGHT(LEFT(Priority,2),1),”red”,”orange”,”green”)
Simply replace it with your function: IF([Days Overdue]>-1,”Red”,”Green”)
Just curious: how do you determine the “days overdue”?
Hmm it’s still not working and this is what I have…
=”-1,”Red”,”Green”)&”;’>”&[Days Overdue]&””
Sample of what I see from table:
71
As you can see, when I view my table I am getting text instead of 71 with a background color of Red. Any other thoughts? I know my company is using an older version of Sharepoint which doesn’t have all of the bells & whistles as compared to the newer version.
As far as your question:
You can trick sharepoint into creating a column for today’s date which you can then compare today’s date to another date (in this case revised date). As a result, I created a column for “Today”, with single line of text and then created another calculated column “CalcToday” and set it equal to the “Today” column. Once I was done I deleted the “Today” column and voila! You have a column which is set to the current date! Then I just created the “Days Overdue” column which compares date information from certain items to be addressed/completed (a.k.a”Revised Date”) to “CalcToday”.
LoL fail. Formula isn’t posting as desired…if you have e-mail Christophe I can send it to you…essentially I took the same information from your background formula and inserted the if statement and also modified the end &Priority& section for Days Overdue.
First, I recommend this post:
https://pathtosharepoint.wordpress.com/2008/08/14/calculated-columns-the-useless-today-trick/
(off-topic for color coding, but relevant to your case)
Then, make sure your double quotes are correct (type them directly in the formula field, do not copy/paste from the post).
And of course feel free to contact me directly at Christophe@PathToSharePoint.com
Figured it out – was missing the script in the code to change information to HTML. It works perfectly. Your site and overall guidance has ben great. Thank you!!!
Pingback: The “HTML calculated column”: stay tuned! « Path to SharePoint
Hello
I try to implemente indicator in my infopath form library using following formula as “My Severity” column name.
=””
here field name is “Form Severity” which contain High, Low, Medium values. As this field contain spaces in field name, so I have used field with square bracket as [Form Severity].
I have also added HTML in CEWP.
Once I implement this in any view , it is giving me #VALUE! under “My Severity Column”.
Can anyone suggest me whats wrong I am doing?
Disha
3-RIGHT(LEFT([My Severity],2),1))&”.gif’
I try to post my formula 2-3 times, somehow it is trimming from the post
what is the best way to post my formula here
Dishaniti: to post your formula, replace the angle brackets with another character. Or contact me by e-mail at Christophe@PathToSharePoint.com
I am trying to put a traffic lights column on a task list. I’ve copied your code to the CEWP as it is on the site. When I go to make my calculated column this is what I use
”
=IF([drop test]=”Red”,”http://sharepoint…/Red.bmp”,IF([drop test]=”Green”,”http://sharepoint…/Green.bmp”,”wrong”))
”
[drop test] is a choice column.
This work but only that it shows a link to the picture and not the picture, what do I need to change so it will work with your code and display a picture
Pingback: Troubleshooting your “HTML calculated column” « Path to SharePoint
I have tried your example and it doesn’t quite work either. I used your CEWP code, made an issues list with a title and priority. I used your calculated column code for the calc column (=”•”)
It works for the logic but the pictures don’t show.
Priority Normal(2) returns this exactly:
Priority High(1) returns this exactly:
There is a troubleshooting section here:
https://pathtosharepoint.wordpress.com/2008/11/01/troubleshooting-your-html-calculated-column/
Let me know if this is helpful.
Pingback: SharePoint calendars: color coding, hover effects, etc. « Path to SharePoint
Pingback: Color coding: more examples « Path to SharePoint
I fall into the group of trying to use this in a custom list (I believe that’s what we created/imported) rather than an issues list. I fooled around with this for a good hour, realized we imported a spreadsheet hence the “custom list” list definition and base type. I finally setup the demo issue list to keep my sanity and confirmed I was doing it right.
Any idea what needs to be changed to get it working in a custom list?
Thanks, great info though!
Brian
Brian, I have published a bunch of new examples recently, see which one is closest to your case:
https://pathtosharepoint.wordpress.com/2008/12/09/color-coding-more-examples/
Hi Christophe, thanks for sharing the tips.
I had tried your method and it works fine. However, when I tried to print it out, the colour have gone. Do you know how to print with the highlighted backgroup color in it as well?
Many thanks.
Demi, this is the expected behavior. The following post should answer your question:
https://pathtosharepoint.wordpress.com/2008/10/29/printable-progress-bars/
See the whole series for other examples:
https://pathtosharepoint.wordpress.com/category/the-html-calculated-column/
For example, a printable calendar:
https://pathtosharepoint.wordpress.com/2008/11/15/sharepoint-calendars-color-coding-hover-effects-etc/
Hi Christophe, I tried and it works but the problem is I don’t have a fixed size for the cell like the example given. The size of the column cell have to depend on the “Texts” that entered by my user. In this case do you know how to make it flexible?
Pingback: Important References - No Programming « Sharepoint Programming
Hi Christophe, I have a birthday list, which has a employee name column and current year birthday column, This current year birthday column is used to extract the birthday month and date and put it in birthday column, however, I want to highlight the particular birthday in the list on the particular birthday date. please suggest me a way to do.
Rudy: I’d suggest to keep it simple and display two views of the list, one filtered with birthday=[Today] and the other with birthday!=[Today].
If for some reason you need to keep all names in one list, you can leverage this post:
https://pathtosharepoint.wordpress.com/2008/11/24/countdowns-a-second-method/
However, I want to highlight a birthday when birthday=[today], but I am not able to apply the if condition on the list, please suggest
Again, see the link I suggested. Birthdays are just a particular case of countdown where nothing happens before or after the date, and the field is highlighted the day the date is reached.
Browse other posts in this blog to find examples of highlighted fields.
I have used these steps to create the birthday list
Create a Custom List for your Team Roster/Key Contacts. Add columns for Name, Role, and whatever other information you are tracking.
2. Create a Custom Column for “Current Year Birthday” column as a Date data type to display Date Only.
3. Create a Custom Column for “Birthday” column as a Calculated data type to calculate a Text data type with the following formula:
=TEXT([Current Year Birthday],”mm/dd”)
4. Create a Custom Column for “Current Month” column as a Calculated data type to calculate a Date data type with the following formula:
=DATE(YEAR([Current Year Birthday]),MONTH([Current Year Birthday]),1)
5. Create a Custom Column for “Next Month” column as a Calculated data type to calculate a Date data type with the following formula:
=DATE(YEAR([Current Year Birthday]),MONTH([Current Year Birthday])+1,1)
6. Create a New View on the Custom List that displays the Title and Birthday columns and filters on the following criteria:
[Current Month] is less than or equal to [Today] AND
[Next Month] is greater than [Today]
Display: Name, Birthday
Sort: Birthday, Ascending Order
7. Add a List View Web Part for the Custom List to a page that uses the View created in Step 6.
however, it only show birthday for particular month, now i want to highlight the current date birthday with particular color, please suggest how the same can be achieved
Christophe, please help
You can do this through SharePoint UI? Can you post some instructions?
I am trying to flag items in a data view. For example if ‘Amount > $500’, I want to highlight it red. Or flag it in some way.
Andrew, the instructions are in the post, and you’ll find more examples to help you here:
https://pathtosharepoint.wordpress.com/2008/12/09/color-coding-more-examples/
Hi Christophe, first of all, I wanted to thank you for all your hard work and for giving us solutions to our SharePoint related issues. I have implemented your example for the colors, and it works beautifuly.
There is one issue though that I am not being able to resolve. If you try to filter on a grid to see only Green bullets (for example) all you see is the HTML code “<DIV style ….". Is there anyway to see either the bullets or a friendly text? I have tried to input comments on the begining of the HTML code like:
=" <DIV style=’font-weight:bold;……"
But it didn’t work. Do you have any idea how to implement this?
Thanks in Advance,
Ralph
My code that I just posted, didn’t post correctly because I had a comment tag 🙂
=”B_ “&IF(Status=”Completed”, “green”, “red”)& “E_ <DIV style=’font-weight:bold; font-size:24px;….."
where B_ is the begining of the comment tag.
where E_ is the end of the comment tag.
Thanks
Ralf: the only friendly text I can think of is “This column cannot be filtered”… and this is possible by setting the “filterable” attribute to false.
I have done some tests to try and display the HTML (bullet or other). I am able to do it for simple cases. I’ll try to improve it and share my findings this month.
Hi Christophe,
Many thanks for your great posts. Just wondering if you ever managed to get a workable solution going for filtering on the “traffic lights”?
steve.
Thanks for your answer…I was able to do it, the problem was because I was putting the code to have the comment before the and the java-script expects to be in the position 0.
So, all I had to do was put it after the …it doesn’t look great, but it is better than nothing. 🙂
I will send you the image by email.
Thanks again
I just implemented this solution for a client on a SharePoint task list and it is working wonderfully. The best thing about it for me is that the “traffic light” code allowed me to use colors other than red/yellow/green, which was the client’s request. Merci mille fois, Christophe, for the solution and code!
Genious!
How can I donate? 🙂
Thank you very much for the code, you are such a genious.
Hello Christophe,
I am trying to create a ‘Traffic Light’ status indicator for tasks using the % Complete. I have been unsuccesful at creating the script in the calculated column for this. I kept getting a Syntax error. Would you mind taking a look at my script and see what I did wrong?
Sure, send me an e-mail: Christophe@PathToSharePoint.com
Thank you so much for this post. It has been super helpful. The execs definitely like a color coded pretty SharePoint dashboard over a mono-chromatic one.
God you are the man! I’ve been searching for a solution to this.. thank you for being kind enough to post the solution.
If you have time.. How would you code it if you want 0-3 ?
I was thinking –
”CHOOSE((RIGHT(LEFT([Expected User Impact],2),1),0)”green”,”yellow”,”orange”,”red”)
Of course it’s not working though..
RIGHT(LEFT(column,2),1) extracts the first character: 1,2 or 3.
If your choice is 0-3, then replace it with:
RIGHT(LEFT(column,2),1)+1
and add a color choice.
I also suggest that you check out the following post, it has lots of examples to play with:
https://pathtosharepoint.wordpress.com/2008/12/09/color-coding-more-examples/
Hi Christophe,
I’m not a programmer by profession, but a PM trying to set up a risk mgmt list in sharepoint. I have a field called color and I’m trying to have its color changed based on the value of a field called “Weight”. I created the color column as a calculated field and put the following formula in it:
=IF([Weight]<=6,”GREEN”,IF([Weight]<=11,”yellow”,”red”))
Now I’m not sure where exactly to write the web part. I opened up the list, chose the CEWP from site actions, and am confused as to what code to write in it, and also howwould the web part know which field’s color I’m trying to change?? Sorry if this sounds too dumb, but any help you can provide would be very appreciated. the above foormula dispalys the word “GREEN” or “red” appropriately, but does not show any color in the color field. Thanks so much.
Kirti: you are on the right path. As you are just getting started with this, I recommend this page where the examples are more detailed:
https://pathtosharepoint.wordpress.com/2008/12/09/color-coding-more-examples/
For the code in the CEWP, simply follow the links in the post. It may look like a maze, this is because color coding is just one application, the scope of the method is actually much broader.
Hope this helps. If you need more details let me know!
Hi Christophe,
Thanks for your help on this. It works (finally!) in the standard view. However, when i view it in the datasheet view, it still shows the HTML and not the color. I looked in the datasheet view and iv can see the CEWP code there, so am not sure what else is missing. Any suggestions?
Thanks so much.
The method only works with HTML. It won’t work in datasheet view which is an ActiveX control – specific to Internet Explorer.
This collection of posts has been invaluable. I have several “dashboards” that have benefited from this. Thank you so much Cristophe!
Hi Chris,
Which feature should i enable to add the html in Calculated Field.
rgds.
Hi Cristophe,
The color coding works when i use your code.
I am trying to set up the field to show a bullet (trafic light) as well as show the name of the color next to it (since we have some color blind folks here). It does that correctly, except that if i reduce the font size to 12, it also reduces the size of the bullet. How can i change the lettering to 12 while still keep it as a big bullet. Please respond only if you have time, since i know that this is something that i may be able to find in an HTML manual as well. Thanks so much.
Kirti: right, an HTML manual will help you write more advanced formulas.
For your case, you’ll need to use two SPAN tags, each with its own font size.
As an example, take a look at this:
https://pathtosharepoint.wordpress.com/2008/10/29/gantt-view-first-test/
I am trying to use the bullet point formula on SharePoint 2003 server. what happens is I get the html code instead of the colored bullet point any ideas?
Thanks
J
Christophe –
Excellent articles on how to do this stuff. I’ve adapted some of your work for a client and in the process discovered a number of things. First, in your KPI Indicator example above, you use an indexing of 1, 2, 3 for the indicators. I have a WSS 3.0/MOSS 2007 installation and found out that the default indexing is really 0, 1, 2. I also found lots more KPI Indicators that I discuss in my blog (http://tjdoherty.spaces.live.com/blog/cns!4FFA26922BDE54A3!405.entry). Keep up the good work and thanks for your assistance in helping me help a client.
Christophe –
Please disregard my earlier comment. Not being familiar with the formula you used for constructing the KPI Indicator (KPIDefault-”&(3-RIGHT(LEFT(Priority,2),1))&”.gif’ />”). I thought the math it was doing was 3-(2 or 1) resulting in either a 3, a 2, or a 1 index number. Now I realize that the math is 3-(the 2nd character of “Priority”, of length 1) which will result in either a 0, a 1, or a 2.
Sorry, I did not mean to intentionally disparage your excellent work.
Pingback: links for 2009-02-16 | Alpesh Nakar Blogs on SharePoint, Microsoft and that's IT
HI Christophe
I have a Status Field, like Amita, and want to display GREEN, YELLOW, RED based on the value of the status. I have tried replacing your &CHOOSE with the IF example you gave and SP thinks there is an error in the formula. Here is my formula – see anything wrong?
=”•”
where Goal Confidence is the status field.
Any help would be appreciated
hi,
I tried applying the font code to a different column then the priority one but it just doesn’t seem to work. i keep getting a syntax error.
this is the IF statement that i made
=IF([On Time Status]=”Overdue”, “red”, IF([On Time Status]=”On time”, “orange”, “green”))
but I’m not sure how to past it in the the original code :
=””
just doesn’t work. what am i doing wrong ?
netta, follow the link at the end of the post, you’ll find more step by step examples showing you how to continue once you have selected the color.
Your first formula seems technically OK, I just don’t understand why “on time” is associated with orange.
Christophe,
This is awesome stuff.
I know too little about HTML. I tried to copy your code, but the result of the calculated column is showing the HTML rather than formating it.
My formula:
=””&[Serial Number]&””
Result:
3001302813-010-1
Any ideas? Thanks
Eric
Please ignore my post. I didn’t fully understand https://pathtosharepoint.wordpress.com/2008/09/01/using-calculated-columns-to-write-html/ . Sorry to waste your time.
Eric
Christophe,
Using the code below, I was hoping the calculated columns would produce dates. I do have Sharepoint set to the date data type.
//=””&[Notf Date]&””
Instead of getting 2/17/2009, I get 39861 conditionally formated correctly.
Do you have any ideas?
Eric
Trying this to display the code.
“&[Notf Date]&”
I am posting this here because you are using the CHOOSE function. My question related to, I think, CHOOSE. While we create these colorful fields I have found that you can not add “SUM” these values, because it is not just a number, it is am entire string. Looking at my string it ends the same way. my integer and two closing DIV tags. Can the CHOOSE function or any function allow me to grab just my integer so I can sum multiple calculated values?
Example:
[DIV ….][DIV…]0.234[/DIV][/DIV] can I grab the 0.234 from the string? It may not be 3 decimal places, but will always be between the DIVs
Have you tried using either MID or a combination of LEFT and RIGHT to select just the digit and then convert it to an integer before using SUM? Excel has a VALUE function that convert a number string to an actual number value. I think I was able to use it in a calculated column. Just be prepared, your formula may get very long. I suggest creating columns for the intermediate steps. And then once you get it all working, you can combine the formulas in all the columns.
Looks complicated. *Anonymous* is correct, use a combination of LEFT and RIGHT, and also FIND and SEARCH. On top of this don’t forget VALUE to convert your 0.234 from string to number…
I assume 0.234 is pulled from somewhere else, so why not just use the original column?
Thanks for the great code. One quick question. How can I center the text within the display column (I don’t want it left aligned).
For more advanced HTML strings, I recommend to refer to a HTML tutorial, like w3schools.
I your case you’ll use the style attribute:
http://www.w3schools.com/html/html_styles.asp
You’ll need to set properties like “width=100%” and “text-align:center”. The exact expression will depend on the tags you use.
I’m using your script to highlight the title of a calendar item based up a selected category. When I change my calendar month view title to use my new highlighted title it breaks the <a href tags so there is no longer a link to that calendar item. Although the highlighting works great.
Jim: for calendars, check out this post:
https://pathtosharepoint.wordpress.com/2008/11/15/sharepoint-calendars-color-coding-hover-effects-etc/
Pingback: Modificare modelli in Sharepoint Web Service 3 | hilpers
Hi,
I am working on a document library and I wish to add indicators and colours as shown in your post. When I convert to an XLST data view to apply conditional formatting, the “edit in datasheet” dissapears from the actions menu.
Is there a way to resolve this issue or maybe another approach using the document library?
Thanks
Regards
Chris
Chris, let me clarify a few points.
First, this post is NOT about the XSLT data view. We are talking about the standard SharePoint lists here.
Second, it is normal that you lose the “edit in datasheet” when you use the XSLT data view. The “edit in datasheet” is not HTML, it is a Microsoft control (which btw only works in IE).
Note that if you use my method, you’ll see that it doesn’t apply to “edit in datasheet” (for the same above mentioned reason).
So far so good – I managed to get my list working with the ‘Traffic Light’ style of indicators. Now I am trying the ‘Indicator’ style and can’t find the KPI default images that you refer to…
At first: Thanks for your great work!
I try to paste the formula but I keep getting Syntax errors. I suspect it has something to do with the fact I have to use semicolons as separators, in stead of comma. I experienced this before with more simple formulas. I believe this has something to do with using non-us versions of WSS (although my SP language is english).
When I replace the comma’s with semicolons I still get the syntax error.
Do you know how I should adapt the formula, so that it works?
Do not paste the formula, type it. My blog editor formats quotes in a way that is not suitable for SharePoint.
This was one of my first posts. In later posts I have included friendly “copy to clipboard” options (see for example the update at the end of the post).
Thanks; this worked. But also i had to change the comma’s into semicolons.
Found this great site which lists all other color words that can be used:
http://plantphys.info/demo/Colors.html
Sadalit: I did a quick check, and some colors are not correctly rendered on that page (e.g. puce and aliceblue).
My usual reference is w3schools. They have charts that help match text color and background. However, it seems that their list is not complete (puce is missing for example).
Hello Christophe,
When I try to apply the color coding to a calculated column supposed to display a date, I get as a result the date numeric value and not the date formatted value.
The calculated column has the following settings:
Data type returned: Date and Time
Date and Time Format: Date Only
The formula used is:
//=”30,”red”,IF([Date Difference]>5,”orange”,”green”))&”;’>”&[Planned Date]&””
[Planned Date] has the value “31/03/2009”
[Date Difference] has the value of “0”
The calculated column shows “39903” instead of “31/03/2009” although the color is green (that part works fine).
Is there something that could be done or are dates not an option here?
Thanks for the help.
Christophe
In calculations, dates are expressed in number of days. To get a date format, you’ll need to use functions like YEAR, MONTH and DAY.
See this article for references on calculated columns:
https://pathtosharepoint.wordpress.com/2008/08/07/calculated-columns/
And see this one for an example:
https://pathtosharepoint.wordpress.com/2008/11/24/countdowns-a-second-method/
I have implemented this in many places…THANK YOU. Now I am doing one where they need me to on the VIEW “Group By” the Indicator column itself. When I Group By the indicator field I only see the HTML code, but if you expand the grouping I see the proper indicator lights underneath. So I assume I need to modify the Java Script??
Pingback: Color Coding - Formula Generator « Path to SharePoint
I still do not understand how to get it to render properly when the Indicator field is a “Grouped By” field in a view. It works fine if my indicator field is NOT the Grouped By field…but doesn’t render properly if I make it the Grouped By field.
Hello all!
First of all, congratulations for this site!!!
I am trying to apply this formula to my Sharepoint site (v2):
=””&Priority&””
but I have the following message each time I try to validate:
“The formula contains a syntax error or is not supported.”
Everything works well for the v3 version (I have another site on this version and it works!).
Any idea?
Thank you very much for your help,
Marcia
=””&Priority&””
Sorry, this is the right code…
Marcia
//=””&Priority&””
This is not directly related with this topic but since it touches a similar subject…
I’m experiencing a problem with formulas, I have 2 farms running. On one of them I used the following formula “=IF([OSInstalled],0.75,0)” which works fine. I tried using the same formula on the other farm and I get the syntax error message.
I’ve tried switching the “,” to “;” and I still have the same problem.
The only difference between these farms is that 1 is x86 and the other is x64.
Any ideas?
Thanks
I can’t seem to figure out a way to remove commas from numeric values in SharePoint 2007 lists. ?
Jenny, check out the TEXT function in SharePoint calculated columns.
I would like to return the value of the Priority Column into the Font Color column, but remove the (1) from the value returned, so my colored text does NOT display (1) Low, but rather just Low
Using MID, the syntax for this “should” be: =””&PullFrom&””
this is accepted but returns #VALUE
Should I be using something different?
This is great! Works perfectly! Does anyone know of a way to look up a column value from a previous version of a list item? Essentially, want to show previous priority of an item so you can see previous and current states. Thanks!
??
I can get both the new code and the old code to work perfectly in my list, but when I try to bring my view from my list to a Webpart on another page it does not work. I have verified that I have used the same code on both pages, any ideas?
Ken, make sure you put the CEWP with the code after/under the Web Part on your other page. Apart from this, I see no reason why it wouldn’t work…except if you have some very specific customization on that other page that conflicts with my code.
That was my first concern, so I pulled everything out and left just the Announcement List and the CEWP on the page, same issue.
I also started a new page and but the issue continues.
Christophe,
Looking more into it I think I have narrowed something down.
My Test Page contains the Following:
Annoucements Web Part(List with color)
Calendar Web Part(with Color)
CEWP (TextToHtml)
CEWP (Mini Calendar)
My Color on the Calendar works both on the Calendar Page and in the Webpart
My Annoucements work fine on the annoucement Page but displays
background: test announcement
Instead of the color.
I can remove the Annoucment Webpart with no Issues, I can remove the Calendar webpart, and the mini calendar and the issue remains.
How do I get this to work with the preview pane? This is my last hurdle. Thanks!
Pingback: HTML Calculated Column – Updated script « Path to SharePoint
Pingback: HTML Calculated Column – Updated script v2.1 « Path to SharePoint
Hi friends,
I am trying to use Traffic Light color code,
Pls let me know which code to be added to CEWP
Hi, would like to find out.
How to auto update the library information?
I have a Task Library, with a calculated column on Tasks’ Status with colour applied (using the colour coding method). The formula I used in the calculated column is to compare System Date with the Plan Date column. Actually it works pretty well. The only issue is the Task’s Status/colour will not get auto update if System Date is over than Plan Date.
Is there a way to refresh the Task records in the Task Library?
Thanks
Demi
I noticed that when I used Bullets with a Task List % Complete that the bullets were slightly off set from the rest of the items. I solved it with a simple CSS addition of:
line-height:50%;
=”=0.8,”Green”,
IF([% Complete]>=0.6,”Yellow”,
IF([% Complete]•”
Great stuff! Thanks for all the help and code support!!
Pingback: An example combining Gantt view and color coding « Path to SharePoint
Hi,
I am facing a problem that the filter on the view for the color coded column still show the tag. any idea if i can disable it in some way?
Regards,
sahil
Is it possible to perform the conditional formatting using a formula to is if the date in a column is less then or greater then todays date. Having are hard time getting around this since i can not use =Today in a calculated column
We recently applied SP2 for MOSS. Since then, my color coded columns are not showing the colors. If I go into Edit Mode, then it is working again, but as soon as I exit Edit Mode, the background colors disappear. Any idea what is causing this and is there a work-around?
What can I do if I do not access to CEWP??? Any other way to make it behave as real HTML???
🙂
Thanks for this great site!
I’m a complete newbie to SharePoint, but I’d like to use color coding on a task list I’m developing.
Do the examples above apply to SharePoint Portal Services 2003?
Thanks again!
Dbo, the initial script published on September 1st 2008 worked with SP 2003. I would expect the latest versions to work with SP 2003 too, although I haven’t tested them.
Many thanks – it has worked great so far!
Hello!
I have created a custom list using Import spreadsheet. Im my original excel file I have column “A” color coded blue, column “B” yellow etc. My question is how to colorcode the different columns, not the rows. EG I want column called “Title” be blue, Column “Name” be yellow etc.
I only find how to color code a row, not how to colorcode a column.
I hope you understand my question and can help me out.
Many thanks in advantage
Staffan
The method allows you to color code one cell in a row. So if you simply apply the same color to all items, you should get your color coded column. Did I miss something?
I am having one minor quirk when i use the traffic light color coding. For some reason the bullet itself is wanting to push down in the cell causing the row to be off on spacing. I noticed that all of your data on the row for each of the calculated columns lines up exactly. What am i missing that is causing my bullet to be further down in the cell causing my spacing issues?
As always, great site and thanks for any and all assistance.
Ex. data data data
bullett
Are you calling me a cheater? Well, you’re right…by default a bullet will not be centered in the cell. So for the screenshot I applied some additional styles to the DIV element (a bottom margin or something like that).
Pingback: The HTML Calculated Column, one year later « Path to SharePoint
Hi Christophe,
This is really amazing. I can get everything to work properly except the Indicators. I seem to be misunderstanding the process. Can you please explain it a bit more. Thank you so much for everything – Heidi
Hello again, I can make the indicators work for the three different priorities. However, I need a fourth called Archived and that won’t work even though I made a shape for it that followed the naming convention for MOSS. We’re on WSS 3. Thanks again. This is just what we need!
Sorry to keep posting, but my problen was on my part. It was in the naming of the GIF files. One didn’t pass properly.
Hi Christophe,
This is an awesome tutorial. It’s very well written, and easy to follow. I have a different question that relates to this problem (color coding!)
Essentially, I’m in the process of building a sharepoint site to deal with emergencies we end up having on my military installation. One of the things I’ve created is a digital version of the checklists we use. Currently, the status of a checklist item is a choice box (Unset, NA, In-progress, On-going, or Completed). The viewable side is a Standard View of the checklist, and it’s edited by a Data Sheet View.
The boss wants the word “Completed” on the Standard View side to turn green when people using the Data sheet View switch it from “Unset” to “Completed.” Is there any way to accomplish this via CEWP or the XML version? I know your tutorial here calls for a Calculated column to change colors, but I’d like to try and avoid adding (yet another) column if at all possible.
I don’t care if it turns the entire row’s background green, or just the word “Completed.” I can explain away any weirdness provided it changes color. Unfortunately, being that it’s government computers on a Sharepoint server owned by a higher headquarters, I can’t go in and do it the easy way with Sharepoint Designer. Any help you could give me would be greatly appreciated. Thanks!
Jonathan: if you can’t use the DVWP and its conditional formatting, creating an additional calculated column seems to be the simplest approach.
You could also write a script that will search for the word “completed” on the page and turn the background green.
I am looking to do some of the field coding using calculated fields utilizing preexisting images included with sharepoint. Other than the KPI icons is there a way to view all the images included in the _layouts directory since DIRECTOR LISTING IS DENIED.
Here is a link to a site that has images for viewing:
http://www.bitsofsharepoint.com/ExamplePoint/Site/SharePointLayoutImages.aspx
yep, thanks Heidi 🙂
One more question. Using this color idea is there a way to edit the form to show the same color as the calculated fields. For example, when i create a new entry for the list can the drop down field for priority show the same color or image as the calculated field in the view?
As always thanks for the help.
theerdman
Sure, you could do this, but it would be another customization, separate from the calculated column thing (the content of the calculated column is only created after you send the form).
Wonderful information! I was wondering how would I use the ISERROR function with this. I am using this formula to capture if the projects have been baselined to show the overall health. However, if there is no baseline I would like the display to show “No Baseline”, or if I type nothing in for the Priority, I would like for it to display “No Baseline”, but definitely not the #Value erro. I hope this makes sense.
Going back to one of the original posts using the IF statement, I am trying to change the background based on the value of a column called ‘RPN’
If the value is greater than 40 it should be red
If the value is greater than 20 is should be orange
Else it should be green. Here is the code that I have tried;
=”40,”red”,IF([RPN]>20,”orange”,”green”))’>”RPN&””
This is based on;
=IF(RPN>40,”red”,IF(RPN>20,”orange”,”green”))
which is successful, but when I try the HTML version I keep getting syntax errors but cant figure out why.
Any help would be greatly appreciated.
The last comment did not have the proper code, here is what it should have been;
=”40,”red”,IF([RPN]>20,”orange”,”green”))’>”RPN&””
TwoFries, it seems that an & is missing in your formula.
Dear Sir,
I’m currently studying WSS 3.0 and I wanted to have a calculated column that will show a green traffic light if a column called COMMENCEMENT is ‘Y’. Below is what I have:
=”•”
When I create the calculated column, it shows a syntax error. Can you help?
=”•”
Hi – i’ve applied the formula to traffic light status, and got nice coloured bullets appearing on my list. However, this only seems to work on the front page of hte sharepoint site. If i click into the different list views then only the html code displays, rather than the nice coloured bullets. Is there a way of ensuring the html conversion reaches the different list views?
Thanks
Rich: the script is linked to a page, not to a list. To make it work with different views, you need to add the script to each view.
A good practice is to add the script before you create additional views. This way, you can reuse the first view with the script to build the others.
I have a similar issue as Rich. I’ve created an “In/Out” log and use the traffic lights script. I created several views for each team with no problem – the traffic light works fine. However when I create a “Personal View” for just myself I have the same issue as Rich – I only see the HTML. I tried adding the Text to HTML web part to the Personal view but with no success. Any thoughts?
My create view is gona now, when i get it back by creating a new view but It’s all messed up. I get multiple lists with the same content when I create another view or list disaapears when I pick a certain view.
Anyway it gets REALLY messed up when you use this in the listpage
But great tip ofcourse
Also can’t edit anymore in DataSheet either
Sorry, Carel, what are you talking about? I think this has nothing to do with the current post…
Good day – I have a list that reflects dates of when folks post 4 different forms. Basically – the column headings are the names of the forms, to the left are the offices for which the forms were submitted. I’ve looked but didn’t see this post – but could have missed it. I’d like to set this list up where if a date in any of the 4 columns is older than 6 months – it is highlighted in red. Any info would be helpful. Thanks much.
Marina, search my blog for “countdown”, this will give you a start point. Comparing a date with today is not easy in SharePoint.
@ rschedin: everything works fine on my tests. Could you precise what changes you made in personal view?
From the View drop down I choose “Create View”. I then “Start From Existing View”. From the “Create View” page I give the view a name, click the “Create a Personal View” radio button, select filter criteria (which is to filter on the Name column and enter my name). The view comes up like I want – with just my entry but the “Traffic Light” only has the HTML showing.
What I want is for each staff member to create a view with just the entry for their name on the list.
I’ve tried adding the TextToHtml web part on this view but am unable to do so. The Traffice Light HTML I use is this:
=”•”
I recently came across a post that confirms that you cannot add a CEWP to a personal view. That would explain your issue. Here is the link:
http://servergrrl.blogspot.com/2009/10/content-editor-web-parts-standard.html
Christophe, is there any way to apply colour coding in preview pane view to the column that is on the left (the one you hover over to display the preview). Can apply colours to any column in the right pane no problem.
I don’t think so. The left column displays the default Title field.
Hi Christophe,
Can u please take a look at my IF funtion is not working.
=”IF(“&[Availability Status]&”=”&”Available”&”, “&”Yes”&”)”
This will return just string,
IF(Available=Available, Yes)
Do you think this problem occurs from my Sharepoint Services 3.0 itself?
I dont know why but it seems all the functions aren’t working.
Regards,
Fayadh
Hi Christophe,
Great work. Thanks for sharing this.
For some reason when I try this my column just shows the code and not the result.
Canada
Any ideas,
Thanks,
Jay
you just saved my life – great post! Exactly what I needed and it worked like a charm!
Pingback: ÁghyBlog » Listatrükkök
Pingback: ÁghyBlog » links for 2009-06-18
I have a related question. I have a dashboard that I want to use conditional formating on.
I want to compare 2 columns(fields) from 2 different web parts, if the data is ” to eahc other, then turn one of the coulmns “RED’.
Thanks.
Hi,
Thanks for this example. I have been able to implement it in my site. Now, I want to export the list to excel. However, the nice colors are lost when I export to excel. Is there anyway to preserve the formatting so that it can be used as a “portable” report?
Thanks.
No, the formatting is done in html and won’t be rendered when you export to Excel.
How do you apply the color to the Issue title instead?
Well, simply replace Priority with Title as displayed content. You may also want to check out this post on how to highlight the whole row:
https://blog.pathtosharepoint.com/2009/02/26/highlight-rows-in-sharepoint-lists/
But Title is not a calculated column. What I would like to achieve is color code the entries in the Title column. I can certainly replace Title in the html column and it will display the title in the right color. But the title is displayed under the calculated column, not under the Title column.
Hello
i have a queation. how did he made the gantt-diagram , so that you can see the month and not a weekly view? i have no Sharepoint Designer, so is it possible to change the view anyway with Sharepoint
Thank you for you help
Christina, search for “Gantt” on this blog… or register for our Gantt workshop!
The method doesn’t require SharePoint Designer.
I have one question regarding the color coding on the sharepoint list. Has the issue where the header does not render the html coding when you try to filter or sort the list been resolved? I love being able to use all of the graphical information but when users are trying to do the sort all they see is garbled mess of HTML code.
I’ll send you a beta version that should fix the issue.
That would be awesome. Please send to rd.covington@bankofamerica.com so i can begin using it on my site.
Thanks
RD
Done 🙂
Was there ever a final version of this released to address the Group By not rendering the HTML in the calculated column? I have tried the version that was sent in the above string and it doesnt seem to render the code either.
Thanks,
RD
No, I haven’t published a version that works on grouping headers.
Hello,
Thank you for the wonderful post. I am trying to create KPI for a new SharePoint Portal that needs FOUR colors. How would I code it if I have 4 choices and how can I add a new icon to the KPI image folder?
Hi Christpohe,
Great information here! I needed a quick solution like this. I’ve added colored indicators and a colored font column. I would like to be able to sort based on these, but I also just see the html when clicking the heading to sort.
Could I see the beta version that fixes the issue?
Hi Christophe
For some reason filter menus, groupings and data-sheet edit/view, just shows the code and not the result. Am i missing something?
Thanks
Avi
Avi, these are the limitations of the currrently published version. There are ways to make it work in filters and grouping. The datasheet view however won’t work, simply because it is a proprietary Microsoft format, not HTML.
My status columns values are:
(1) Pending
(2) Passed
(3) Failed
I want Pending to be yellow, Passed to be green, and Failed to be red.
I have this calculated column in place:
– Indicator (reusing the default SharePoint KPI images):
=””
Which produces Pending = yellow, Passed = red, Failed = green. !!
I have tried everything to get the colors to “flip” but nothing works. Can you help?
Sorry, the code stripped out of my above vomment. It was the default “Indicator” code shown above.
=img style=’float:left;’ src=’/sites/Workspaces/images/kpidefault-“&(3-RIGHT(LEFT(Status,2),1))&”.gif’/>
Do you have something like this for sharepoint 2010
Working on it, check my progress on my recent blog posts (June 2010).
I’m not sure there is a fix for this. I discovered yesterday that my formula isn’t working as expected. While it does compare todays date to another column date, it only seems to do it if I edit my row and save it. I’ve tried other methods to compare against todays date but it seems Sharepoint does not like the Excel 4.0 formula =NOW(). Any suggestions? In the included formula CalcToday is a calculated column in my list that is producing todays date. The goal here is to produce a colored diamond for each row in my list based on the value in other columns or the current date. It works fine but requires I update the row for the coloring to change. I want the colors to change without my having to update the row.
IF([Func check out]=”Yes”,””
,”CalcToday,”aqua”
,IF([Est Cmplt Dt]=”Unknown”,”orange”
,IF(CalcToday>DATEVALUE([Est Cmplt Dt]),”red”
,”green”))))&”;’>♦”)
For example, on 6/2 Start Date was = 6/1 but the diamond was still aqua until I clicked on edit then save without making any changes. It should have been green.
Im assuming your date is using the Today function in some manner. Therfore you have created the false column, entered your calculation for the calculated field and removed the Today column.
The only way i have found to update the moving target such as a date to ensure that the calculations are refreshed and your color changes do happen is to create the Today column daily and delete it. This causes all the rows to recalc without having to edit each one individually. If you come up with an automated way of doing this i would be greatly interested.
Thanks,
RD
RD, if you’ve read my articles you know that I am not a fan of the Today trick. That said, and to answer your question, have you considered using Web services? You could create a console application or a script for that.
I assume you are referring to the useless Today trick:
https://blog.pathtosharepoint.com/2008/08/14/calculated-columns-the-useless-today-trick/
Search my blog for “countdown” and you might find something useful.
Thanks to both of you. Yes, as it turns out I am using the Useless today trick. I’ve read much on the countdown Christophe asked me to search but in the end, I believe it can not be done. My mistake was assuming the code would always use the current date for it’s comparison. In fact, it is using the current date from the last time I edited the row.
Nuts!
BTW, sorry about my previous post. There was HTML in my formula and naturally that didn’t display in your browser.
This help me a lot is just what I was looking for and I’m so siccess thaks to you..!!
Thanks a lot !!!!
I have sucessfully added your color examples to a custom list uisng sharepoint 2007. The problem I am having I when I view the custom list item, it shows the calculated list item. Is there a way to hide this calculated column in a view?
Still looking for help with this problem! Anyone got any suggestions?
I have sucessfully added your color examples to a custom list uisng sharepoint 2007. The problem I am having is when I view the custom list item, it shows the calculated list item. Is there a way to hide this calculated column from the view?
At least two ways:
– use content types, and declare the column as hidden
– add the script to the DispForm.aspx page:
https://blog.pathtosharepoint.com/2008/10/01/display-forms-and-the-html-calculated-column/
i still can’t have that magic output…
want to give up already….huhu
Pingback: ใน SharePoint List จะสร้างแถบสี หรือใส่ สัญลักษณ์ให้กับ Column ของข้อมูลที่สนใจได้อย่างไร « Vipnetty's Blog
Guys, help needed.
With this formula, every outcome is colored green, and that`s wrong. Why doesn`t these formulas work with > or =”9″;”Red”;IF(([Riska līmenis])=”8″;”Yellow”;IF(([Riska līmenis])=”6″;”Yellow”;IF(([Riska līmenis])=”5″;”Yellow”;IF(([Riska līmenis])=”4″;”Yellow”;IF(([Riska līmenis]) “&([Riska līmenis])&”
I am a novice and I guess I need more help to understand the whole thing. I have a column name “CI” which is an user input date field. I want to color code this field based on a calculated result. I have created a new column call “CalcDate” which is set to 10/31/2010. What I want is if “CI” is smaller than “CalcDate”, then the font in “CI” will turn red, otherwise green. Is this doable?
Hi all,
I used the script for Background color: Font color but not able to see the proper result. it’s showing the text only as below.
(1) High
Please let me know If I am missing something somewhere.
Regards
Deeptyranjan
Hello everybody,
I have forgotten to reply that I got succeeded to sort it out.
I was making the mistake to put the script (script to change the formula to HTML format) at the top of the page. rather it should be at the end of the page.
Note: Try by putting the script within a content editor web part and put the web part at the bottom of the page.
Regards
Deeptyranjan
This works great on flat views but not my grouped one.. do I need to add something about ‘childnodes’?
Stefanie, this post is two years old, and I have published many updates since then. Try the latest published version of the script, available here:
http://sp2010.pathtosharepoint.com/SharePoint-User-Toolkit/Downloads/
oh – thanks so much. I did not realize.
are there any further instructions/documentation?
oops – I see the documentation. thanks.
i’ll try figure out the issues I’m having on my own.
thanks again
You’ll need to collect information from different posts (and you’ll find a lot of other useful stuff on the way!). Some pointers:
https://blog.pathtosharepoint.com/2008/12/09/color-coding-more-examples/
https://blog.pathtosharepoint.com/2009/11/19/html-calculated-column-minor-update-and-some-advice/
https://blog.pathtosharepoint.com/2010/01/04/html-calculated-column-and-unicode-graphics/
https://blog.pathtosharepoint.com/2010/04/06/tutorial-add-color-coding-to-your-sharepoint-2007-calendar-in-15-minutes/
i tried to add colored indicators, but still get a syntax error.
=”<DIV style=’font-weight:bold; font-size:24px; color:”IF=([Days Open]14,”red”,”orange”))&”;’>•”
gustav, the steps are more detailed and the formulas easier to grab in this follow up post:
https://blog.pathtosharepoint.com/2008/12/09/color-coding-more-examples/
HI Christophe,
thanks for this, appreciated, i have to however use IF and not CHOOSE as the result could fall in 3 different ranges. i used:
=”<DIV style=’font-weight:bold; font-size:24px; color:”&IF([Days Open]14,”red”,”orange”))&”;’>•”
I still get syntax errors.
thanks
This has been very helpful so far, but I am trying to take it a little and hopefully you can help me. I implemented background color column based on the 3 status’.
Now I trying to format the background of a DIFFERENT colomn in the same list that is a dropdown choice of about 30 or so selections. I only want 2 of those selections to show a colored background. Can you provide the syntax to do so?
Cheers
Christophe – I tried this today to color status in a alist and ti worked briliiantly. THANKS!!! But once this worked I tried to export my list to excel adn all it exported was the formula and not the colos – is there anyway to do that?
Thanks
Anu
Anu, this is the expected behavior. Excel is not a Web application, and fields of type rich text or HTML will be displayed as is.
Is there a way to calculate an overall health using indicators built in sharepoint and Project Server. I have created a Issue indicator in SharePoint for the that gives one health color for all the issues in a project workspace. In project server I have calculated a health indicator for SPI and CPI. I would now like to calucate the overall health which should include Issue Health, CPI Health, and SPI health. How can I accomplish this if the data for Issues is in SharePoint and the data for CPI and SPI are in Server.
Christophe,
Simple Priority fields: =”•”
The output i am getting is ‘No’? :S Also if i use the CHOOSE function it will only output if numerics are used i.e 1,2 not “High”,”Low”.
Please advise?
Hi, Very Very Thanks.
I tried much time, but i did n’t get.
By this help i done.
Hi Friend, Thanks a lot.
Hello,
Thank you for this post.
I done.
Pingback: Jose M. Tamez | Text to HTML
You can achieve this via using this solution
http://spconditionalfield.codeplex.com/
Thanks, but note that my solution doesn’t require server side access.
Chris,
In an eariler version of this article (posted on SPC), you used your own icons and there were 4 ‘IF’ statements… I would like to use my own icons, instead of the KPI defaults. I have worked through all the ohter examples and made them work, cna you help wiht the ‘user your own icons/gif/jpg version?
Thanks!
Rick
SPC?
There’s another solution – Infowise Color Field.
aalthough it’s not free, it’s doing the job in the most convenient way to the SharePoint users.
Give it a look:
http://www.infowisesolutions.com/product.aspx?id=ColorField
Definitely an option, but “most convenient” is subjective. The solution presented here can be implemented from scratch by an end user, as it doesn’t require any kind of server side install. It also offers more flexibility in the rendering (see the calendar tutorial for example).
i added a new column name MilestoneStatus as calculated column data type, with this formula
=”<DIV style='background-color:"&IF(MilestoneDate”&MilestoneDate&””
but the return value gives me a number, it should be MilestoneDate. How can i transform it to date or can i use some Format functions? pls Advise.
got the answer YEar(MilestoneDue) & “/” & Month(MilestoneDue). Thanks alot for the owner of this site
as i apply to other machines with MOSS 2007, why is it that it is not displaying the background color, but in my first machine its working…
Are you logging in with the same credentials on the other machines? Are you using a different browser?
How would this be applied to a list column with only two options? (yes, no) I keep getting syntax errors. Here is one of the options I tried
=”•”
Ok, massive failure. I’m getting the The formula contains a syntax error or is not supported.
I moved the web part to the bottom, but I seriously think I am misreading the instructions and simply doing something completely stupid.
I have a column that is a choice column with a drop down selection for the options Not Started, On Plan, At Risk, Mitigation Required, Complete.
So, now I’m creating a calculated column “Status” and using the forumla above with the embedded IF statements pointing to the choice column.
See if this tutorial helps you build the conditional formula:
https://blog.pathtosharepoint.com/2010/04/06/tutorial-add-color-coding-to-your-sharepoint-2007-calendar-in-15-minutes/
Well, i did get rid of the error and I’m getting closer, but now it just displays the words, not the bullets or the color. I know I am doing something wrong.. however, if I put the =”•” in the calculated column formula, it errors out, I do have it in the CEWP, So, in the column I have: =IF( [Budget]=”Not Started”, “black”, IF([Budget]=”At Risk”, “gold”, IF([Budget]=”On Plan”, “limegreen”, If([Budget]=”Mitigation Required”, “red”, “blue”))
and in the CEWP I have: both parts.
This results in a semi working piece because the column displays the words “black” “limegreen”, etc. Because I am new to all of this, I know I’m just missing something.
You current formula only displays the color. You are still missing the second step, which is to render it.
Thanks a lot for great article. It really helped.
I want to highlight the row in task list if the task is overdue
what will be the formula in calculated column
Identifying overdue tasks is not easy with SharePoint. Search my blog for “countdown” for some examples.
Hello Christophe, in a related scenario, back in 2009 you did an article on Ben Schlaepfer’s Trend Reporting. That article shows a color gradient. I’m unable to locate the scripting for that gradient column — can you help me? Very grateful for your assistance.
Did you check out nothingbutsharepoint.com? This is where the articles from endusersharepoint.com were migrated last year.
Firstly, thank you so much for your solution. It is awesome and works fine! Great blog!
Well, I have used the trick exposed here with 4 priorities. It worked fine. I have also succeed replacing the bullet by a Wingding. Next step would be associate the following formula to show a different Wingding for each color/priority:
=”‘☠”
Would you help me with that?
Thanks so far!
You’ve done a lot!
The formula: =D
=”☠”
Hi there, i seem to be having a problem with this piece of code, i followed your steps however all i see is still the text and not the HTML piece. I am using Sharepoint 2010 Foundation. Could you help me with where i might be wrong
Hi it seems that it was working however i had the list on group by which did not work. Can you advise how i can resolve this on group by?
For grouped views you’ll need to use a more recent version of my Text to html script. The latest one is v2.1.1 .
Pingback: SharePoint 2007: Priority/Progress Bar using color coding within a SharePoint List
For the Grouped views, please make sure the groupings should be bydefault : Expanded…
If not understood plz follow the belkow blog :
http://jayakrishnaakkiraju.blogspot.com/2011/11/color-coding-custom-list-column-with.html
… or use the most rescent script (v 2.1.1) in SharePoint 2007.
Hi Christophe.
I had some trouble with your traffic light/bullet solution when the item value is blank or not in the form “(#) Desc”.
I resolved it by adding some particularly ugly (but effective) error checking:
=IF(ISERR(RIGHT(LEFT([Priority],2),1)-1),””,”<DIV style=’font-weight:bold; font-size:24px; color:”&CHOOSE(RIGHT(LEFT([Priority],2),1),”red”,”orange”,”green”)&”;’>•</DIV>”)
Definitely a needed addition if the fields are not required. The ISBLANK function would work too.
Hi Christophe: As you said there are ways to make work in filter and grouping, Can you please let me know the ways? I am having the filtering issue(give the data like )
Thanks in advance!!!
Lovekush Kumar
Pingback: A Program Management Office…in a Team Site « Ellen's Digital Workplace
hi Christophe,
actually i have implemented the above steps but the problem is that i am only getting the name of color but not the actual color light….please specify how to implement in the content editor part…as after implementing the content editor par i m only getting a black dot and not the actual traffic light as shown in picture..kindly help
,
I added this code to the default view on a list and it worked beautifully. However, when I added a second wider view, which has a horizontal scroll bar, the icons did not appear. On editing the page, it looks like the wider view uses an entirely different page, because the webpart for the icons was not there. Once I copied the webpart to this page, it worked fine.
can somebody tell how to implement this thing in content editor web part…i m posting the script in HTML option and all i m getting is a black dot….i am implementing this in custom list.
Thanks, the traffic light solutions you posted worked a treat and the explanation was very good.
This code works great. However, I need to modify it slightly. First, I want to be able to change the text w/o it changing every cell in that column. Possible? I just want the background color.
Second, I need to calculate days from a date for another column. I’m not sure about how to do the IF statements for dates (say, if 30 days from TODAY it is yellow background color). Again, I’ll need to add the date to the cell.
Maybe none of this is possible?
All of this is possible, but not necessarily easy. Check out my other posts for more examples. For dates, search for “countdown” in my blog.
Pingback: Using calculated columns to add color coding to your SharePoint lists | sarahlhaase
if I have multiple images to choose from for auto-populating via a percentage range in another column, what should the code look like? All four images are saved in a folder on sharepoint. Example:
90% = red
images are saved as:
bullet-green_gif
bullet-yellow_gif
bullet-orange_png
bullet-red_gif
if I have multiple images to choose from for auto-populating via a percentage range in another column, what should the code look like? All four images are saved in a folder on sharepoint. Example:
70 and 80 and 90% = red
images are saved as:
bullet-green_gif bullet-yellow_gif bullet-orange_png bullet-red_gif
Great idea, thank you. I have successfully implemented this on several Document Libraries using columns created on the libray, however if I try to setup the columns at the site level I get an error that the column contains a data type that cannot be calculated in formulas.
Additionally when you set up the columns on the library they are listed as “Calculated (calculated from another column)” but when set up at the site level they show as just “Calculated”. You thoughts would be much appreciated.
Christophe,
Please ignore my earlier post. Doh! Have found a typo which I have now corrected and all works as expected. Apologies.
Bob
Good to hear everything’s working fine 🙂
Hi, I am not sure why i can’t find the javascript to put/point to my CEWP. Am I missing something? Looked all over…
Another solution – Sparqube Status Indicator.
Benefits:
No code required, condition editor and it supports today in conditions.
Minuses:
it is not free..
http://www.sparqube.com/sharepoint-status-indicator/
The tool looks interesting, however you can’t really say that no code is required. Compared to the solution presented here, the tool requires a server side install, and apparently it doesn’t support SP 2007.
Thank you for this! Looking all over for a relatively easy solution and this fit the bill. The additional links to explain step-by-step were especially helpful as I’m not a master SP programmer.
Hi,
Nice Article Chirs! trying to implement PriorityIndicator Field based on Priority field in SP List. using the following formula
[=”•”]
I have also added HTML in CEWP.
Once I implementing this in any view , it is giving me #VALUE! under “PriorityIndicator” Field.
Please suggest me whats wrong I am doing?
Raj
I am trying this with SharePoint online and it does not work. I can get this to work with SharePoint 2010 onprem solution. Do you have a solution that works on SP2013 online?
Please send me an email if have details on how to get this working in SP2013.
Rick, check out the alternate solutions I posted in 2010 and also in the past few months.
Can anyone help, I am just learning sharePoint and it seams i need a crash course on coding. I am trying to change the background color to “red” based on a sum. the field name is [total average] if it is less then 100 i would like the background to be red if its between 1000 and 100 would like the background to be yellow.
Hi, excelent blog!!! It helped me a lot!
Does anyone know if it is possible to set more than 3 color codes for Indicator option? I was able to do that for the traffic light, but not sure how it works for the Indicator…
I have this code which makes my SH list extremely slow. Any ideas how to change that so the list loads faster:
var x = document.getElementsByTagName(“DIV”) // find all of the TDs
var i=0;
for (i=0;i<x.length;i++)
{
if (x[i].innerHTML=="0")
//find the data to use to determine the color
{
x[i].style.backgroundColor='#FF6666'; // set the background color as red
}
if (x[i].innerHTML=="1" || x[i].innerHTML=="2" || x[i].innerHTML=="3" || x[i].innerHTML=="4" || x[i].innerHTML=="5" ||
x[i].innerHTML=="6" || x[i].innerHTML=="7" || x[i].innerHTML=="8")
//find the data to use to determine the color
{
x[i].style.backgroundColor='FF9900'; // set the background color as orange
}
if (x[i].innerHTML=="9" || x[i].innerHTML=="10" || x[i].innerHTML=="11" || x[i].innerHTML=="12" || x[i].innerHTML=="13" ||
x[i].innerHTML=="14" || x[i].innerHTML=="15" || x[i].innerHTML=="16")
//find the data to use to determine the color
{
x[i].style.backgroundColor='#FFFF66'; // set the background color as yellow
}
if (x[i].innerHTML=="17" || x[i].innerHTML=="18" || x[i].innerHTML=="19" || x[i].innerHTML=="20" || x[i].innerHTML=="21" || x[i].innerHTML=="22" ||
x[i].innerHTML=="23" || x[i].innerHTML=="24" || x[i].innerHTML=="25" || x[i].innerHTML=="26" || x[i].innerHTML=="27" ||
x[i].innerHTML=="28" || x[i].innerHTML=="29" || x[i].innerHTML=="30" || x[i].innerHTML=="31" || x[i].innerHTML=="32" ||
x[i].innerHTML=="33" || x[i].innerHTML=="34" || x[i].innerHTML=="35" || x[i].innerHTML=="36" || x[i].innerHTML=="37" ||
x[i].innerHTML=="38" || x[i].innerHTML=="39" || x[i].innerHTML=="40")
//find the data to use to determine the color
{
x[i].style.backgroundColor='#66FF66'; // set the background color as green
}
}
Well, how about following the explanations in the post?
I can understand that your current code is slow, it includes so many tests.
Thank you, I have recently been looking for info about this topic for ages and
yours is the best I’ve came upon till now. However, what about the conclusion? Are you sure in regards to the supply?
Has anyone been able to make this work for a grouped list that is not expanded by default? This has been so very useful and would like to show it this way too. Thanks!
Heidi, what’s your SharePoint version? v2.1.1 will work for collapsed grouping in SP 2007.
SharePoint 2010
Excuse me. SharePoint Foundation 2010
For SP 2010 see the updated code and resources that were published in 2010 and later. The code that was written for SP 2007 might not work.
I downloaded the TextToHTML-v2.1.1.txt but still can’t get it to work on the grouping for SharePoint 2010. Is there another version for 2010? That one only indicates SP 2007.
Hi Christophe,
Your code works great.Thanks a lot 🙂
Please let me know where can I find the updated code for SharePoint 2010.
Also let me know how to filter with the above code as it is showing HTML content while filtering and I cannot implement this way if there is no filtering option.Please help.
Thanks in advance.
There are no easy ways to deal with filtering… For the latest updates check out the SharePoint User Toolkit, and my articles from 2010 and 2012.
I am not able to display colour coding icon when I group the data in a List. Could u please help me out.
Using sharepoint 2010, i’m trying to locate some images to use as you described. Can’t find the images, and even if putting my own images for this purpose, i don’t know how to find out what is the path for them. Can you help me out here?
I also tried the bullets solution, which is great, but the bullets are coming below the line of the actual text, and it is a bit confusing.
Right, the bullet is not easy to align. The path to your own image should be easy to get:

Pingback: Using Client-side Rendering to color-code list cells in SharePoint 2013 | On Point
My traffic light is center aligned vertically but sharepoint list has everything top aligned. How do I top align vertically my traffic lights?
Pingback: Color coding in SharePoint lists using calculated columns. | Junaid Khan
Hi Christophe, thank you so much for your work. I need some help on a slight variation.
I need to use the KPI icons, since a team member is color blind. I have a column titled “Status”, with three options, which I would like to color/shape-code as follows:
Progressing (green circle)
Issues (yellow triangle)
Stalled (red diamond)
Using the formulas here, I have to reverse the data entry order and I have to include a number schematic, i.e. (1) (2) and (3) to get the right color/shapes attached to each entry.
Is there a way to keep my ordering and match the colors/shapes to my order? (i.e. the first in the list is green circle instead of red diamond)
Also, is there a way to lookup the KPI icons using text only so I do not have to include the numbering? I looked at your followup blog and can replicate the changing of font colors using the “e” trick, but I’m wondering if this can also be done when changing the KPI icons?
Thanks!
Great help, works as supposed on sp2010, but I can’t make it work on sp2013. The html code is generated via calculated field, but the script part does not run. Is there some limitation on sp2013. I tried CEWP, HTML form webpart and script editor webpart.
Hi, I am a complete novice looking for help!
I have a calculated column for growth, shown as a %… I am looking to show any nagative as red (i.e. -58%)
The calculated column I have is ‘Book Growth’ with the following calculation:
=SUM([Book, 2014]-[Book, 2013])/[Book, 2013]
Where do I start with this?
Thanks in advace for your help!
Helen
Pingback: Apply color coding (KPI Status Indicators) to your SharePoint lists | SharePointKeith
When I initially left a comment I seem to have clicked on the -Notify me when new comments
are added- checkbox and now every time a comment is added I recieve four emails
with the same comment. Is there a way you can remove me from that
service? Appreciate it!
It took me some time to figure it out, but this url worked for me:
https://dashboard.wordpress.com/wp-admin/index.php?page=subscriptions&option=comments
If it doesn’t work for you, then you’ll probably have to contact wordpress.com which is the hosting provider for my blog.