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.
Path to SharePoint... and Beyond! 
Thank you!
Pingback: SharePoint Calculated Column formula generator | End User SharePoint
Christophe,
Nicely done. This makes implementing your calculated column solution even easier!!!
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
Could you solve it? I have the same problem..
thanks
Thank you very much for this. Is there a way color coding can be accomplished inside of a SPD DataFormWebPart?
Thanks,
Mark
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
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.
What are the chances of creating a generator that will incorporate images rather than color coding?
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!
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.
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
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.
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:
https://pathtosharepoint.wordpress.com/2008/10/29/printable-progress-bars/
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.
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.
Pingback from IT Pro Ramblings.
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.
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).
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 😦
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])
Found workarround: =IF(([Riska pakape]*3/25)>=2;”RED”;IF(([Riska pakape]*3/25<=1);"GREEN";"YELLOW"))
This worked perfectly, BUT when i filter the column it doesn’t display text but rather the HTML codes
Right, that’s the catch with this technique. I’ll try to address this in the new solutions I’m working on.