I already published an article about color coding for SharePoint lists three months ago, and its sibling for calendar views last month. Both rely on the same client side method, the “HTML calculated column“.
Color coding involves 3 steps:
1/ Select the color
2/ Apply the formatting: traffic light, font color, background color, etc.
3/ Include the magic script that renders the formatting (SharePoint OOTB would just display text)
People are usually comfortable with step 3, which is just a copy/paste of a generic script. But some users are struggling with the first two steps, because they are not so familiar with SharePoint calculated columns.
So I have decided to publish a list of examples based on the default SharePoint lists. They can be used as is and should cover a wide range of use cases. For each example, you can grab the formula by clicking on “copy to clipboard”.
If you are a seasoned list designer, skip this post…but forward the link to your friends!
1/ Color selection
In HTML, you can identify a color by its name or its code. Here we’ll stick to the friendly color names. A good reference is W3schools.
Let’s call [Color] the column that will store the value. You can choose among the following column types:
– Calculated column
The latter will be the most useful, as the color usually depends on another factor. For example, we’ll pick red for issues that are tagged as “Critical”.
If you are not familiar with calculated columns, you can start by reading this introduction.
Example 1: Priority (Issues list, Tasks list)
(1) High red
(2) Normal gold
(3) Low limegreen
Use this formula in your [Color] calculated column:
How it works: if the user selects “(2) Normal”, RIGHT(LEFT(Priority,2),1) will extract the second character. It is the number “2”, so CHOOSE will select the second item – gold.
Example 2: Category (Issues list)
(1) Category1 red
(2) Category2 gold
(3) Category3 limegreen
The formula in this case:
Example 3: numbered list
Let’s say you have a [Choice] column with following values:
1. First choice red
2. Second choice gold
3. Third choice limegreen
In this case, the number is in first position, and we get a shorter formula:
Example 4: Issue Status (Issues list)
No number to help us identify the choice, so we need to find a trick. My choice: identify the position of the letter “e”. If for example “e” is in fifth position, it means that the status is “closed”.
=CHOOSE(FIND("e", [Issue Status],1),,"gold",,,"limegreen","red")
How it works: the CHOOSE function will pick a color according to the position of the letter “e”. So if the status is “Closed”:
FIND(“e”, [Issue Status],1) = 5 (the letter “e” is in fifth position)
So the CHOOSE function will pick the color that is in fifth position, “limegreen” here. Note that in our example the letter “e” is never in position 1, 3 or 4, this is why I didn’t enter any color name for these positions.
We can also use nested IFs for a similar result, but the formula is heavier (especially if we have a longer choice list):
=IF( [Issue Status]="Active", "red", IF([Issue Status]="Resolved", "gold", "limegreen"))
Example 5: Status (Tasks list)
Not Started red
In Progress gold
Waiting on someone else darkorange
Here again we’ll use the “e” letter trick:
Here, FIND(“e”,[Status],1) can only take five values:
2 for Deferred –> black
6 for Completed –> limegreen
9 for In Progress –> gold
10 for Not Started –> red
15 for Waiting on someone else –> darkorange
After setting the value of the [Color] column, we can now format it. For this we need another calulated column. The formulas below are the same as the ones I used in the initial post on color coding.
Example A: traffic lights
="<DIV style='font-weight:bold; font-size:24px; color:"&Color&";'>•</DIV>"
I am using a bullet (•) to create the shape.
Example B: font color
My formula is for the [Issue Status] column, adapt it if you work with another column:
="<DIV style='font-weight:bold; font-size:12px; color:"&Color&";'>"&[Issue Status]&"</DIV>"
Example C: background color
Here too, replace [Issue Status] with your own column:
="<DIV style='font-size:12px; background-color:"&Color&";'>"&[Issue Status]&"</DIV>"
3/ The script
Please let me know if you find that detailed posts like this are helpful.