The number one application of the “HTML Calculated Column” method is color coding. This can be applied to SharePoint lists or calendars.
The tricky part is to get the formula right in the calculated column. I have already published step by step examples to help users get started. But as soon as users’ needs slightly differ from these examples it becomes difficult for occasional site designers to get it right.
In an attempt to make the method more accessible, I have built a Formula Generator. Fill out the form with your choice list and the associated colors, select one pattern (Background/Font color/Traffic light), and the Formula Generator will build the formula for you. Hopefully this will allow more users to benefit from the HTML Calculated Column.
I plan to build other formula generators in the future to accomodate other needs (progress bars, etc.). Spread the word!
Update [April 23rd, 2009] Based on readers’ feedback, I have added a formula generator for numeric values.
22 comments
Comments feed for this article
April 21, 2009 at 11:37 am
robin
Thank you!
April 21, 2009 at 2:39 pm
SharePoint Calculated Column formula generator | End User SharePoint
[...] Read the article [...]
April 21, 2009 at 3:39 pm
Peter
Christophe,
Nicely done. This makes implementing your calculated column solution even easier!!!
April 27, 2009 at 4:22 pm
Michael Palko
This is a great tool, but I just can’t seem to make it work on my site.
I chose to add color to the background of the title but when I view the calendar item I see: TEST
instead of just a yellow background behind the title, TEST.
I read thru all of the related posts, and there are smal tweaks that folks suggest, including inserting the brackets ([Title]), but I haven’t felt the love yet.
Any help is appreciated.
Thanks
April 28, 2010 at 4:12 pm
Virginia
Could you solve it? I have the same problem..
thanks
April 28, 2009 at 2:36 pm
Mark
Thank you very much for this. Is there a way color coding can be accomplished inside of a SPD DataFormWebPart?
Thanks,
Mark
April 30, 2009 at 6:08 am
Christophe
Michael: it should work as is (for example the brackets are included in the generated formula).
Feel free to send me your generated formula and I’ll take a look:
Christophe@PathToSharePoint.com
April 30, 2009 at 6:13 am
Christophe
Mark: you can certainly make it work on Data Form Web Parts. However, once you are in SharePoint Designer the natural way to do color coding is to use the conditional formatting options.
May 17, 2009 at 3:54 am
Anonymous
What are the chances of creating a generator that will incorporate images rather than color coding?
May 17, 2009 at 3:56 am
Anonymous
I hit submit too quickly before… Just wanted to say that your efforts here are truly appreciated by me and many of my colleagues who use your site as a necessary reference. Thanks from all of us!
May 18, 2009 at 12:53 am
Christophe
The issue with images is that you first need to define a set of images and their location. It could easily be done for a specific company or team, but not as a generic solution.
May 22, 2009 at 2:49 pm
larry
Hey Christophe, it’s been a while. hope you enjoyed that traveling you mentioned. I see many uses for this, one issue I keep running into is modifying this. Instead of background color, additional background attribiutes. Is there a way to apply a css class instead of a style? Easier to manage the css then trying to manipulate the formula.
thanks again
larry
June 22, 2009 at 1:47 pm
Chris
Christophe,
First I want to thank you for all yuour posts. You have truely helped me a great deal with building out my sites for our customer. One issue I am having with this particular thing is the ability to print the color coding. Whenever I go to print the site for say a pdf or for a meeting it does not render the color just the text in the fields. Is there a way to get the color coding to appear with the text? Again thanksagain for everything and any help on this issue would be greatly appreciated.
June 22, 2009 at 2:31 pm
Christophe
This is the expected behavior, by default browser don’t print backgrounds.
If it’s just for you, you can change your browser settings to print backgrounds.
If you need a more general solution, it becomes more complicated as you’ll need to apply the color to something else than the background. I have some examples on my blog, like this one:
http://pathtosharepoint.wordpress.com/2008/10/29/printable-progress-bars/
June 22, 2009 at 3:15 pm
Chris
Christophe,
Once again you are amazing. Thank you for the link to your other post. Your code there is exactly what I needed (well parts of it). Thanks again.
July 16, 2009 at 9:55 pm
Gigi
is there a way to combine formulas for KPI Default indicators and traffic lights in one calculated column – I want to be able to assign KPI default indicators for status (complete, in progress, on hold) and then a colored traffic light for status (planned, not planned). I can’t figure out to nest the different formulas together.
October 2, 2009 at 8:03 am
Patrik
Pingback from IT Pro Ramblings.
February 8, 2010 at 9:45 pm
Byron J
Chris,
Is there an easy way to add colour formating to a column that is populated with dates? And note that there is no specific formula for the colours, in our case, we would have date fields, and want to be able to change the colours of certain dates.
I drilled through your site to see if I could find an example, and all I could find was changing colours based on pre-set fields (i.e. choices)
Byron.
February 9, 2010 at 1:27 am
Christophe
Byron, you can use date and numbers in the formulas. I may not have an example that addresses your exact need, but you can search my blog for “countdown”, and for “progress bar” (shows how a number can be used to format fields).
September 3, 2010 at 11:30 am
baarmenis
Please help me with this:
=”=”9″;”Red”;IF(([Riska pakāpe])=”8″;”Yellow”;IF(([Riska pakāpe])=”6″;”Yellow”;IF(([Riska pakāpe])=”5″;”Yellow”;IF(([Riska pakāpe])=”4″;”Yellow”;IF(([Riska pakāpe]) “&([Riska pakāpe])&”"
But it`s all green now
September 3, 2010 at 11:31 am
baarmenis
Sorry – here`s the code
IF(([Riska pakāpe])>=”9″;”Red”;IF(([Riska pakāpe])=”8″;”Yellow”;IF(([Riska pakāpe])=”6″;”Yellow”;IF(([Riska pakāpe])=”5″;”Yellow”;IF(([Riska pakāpe])=”4″;”Yellow”;IF(([Riska pakāpe]) “&([Riska pakāpe])
September 7, 2010 at 1:46 pm
baarmenis
Found workarround: =IF(([Riska pakape]*3/25)>=2;”RED”;IF(([Riska pakape]*3/25<=1);"GREEN";"YELLOW"))