Creating StopLight for SharePoint 2013 List and Row highlights

1. StopLight using calculated column:

Create calculated columns & use below formulas based on your requirement.

  •  Traffic light (Actually a big bullet!):

            =”<DIV style=’font-weight:bold; font-size:24px; color:”&CHOOSE(RIGHT(LEFT(Priority,2),1),”red”,”orange”,”green”)&”;’>&bull;</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>”

Formula to add the stoplight on a custom list column called status:

=”<DIV><IMG src=’/_layouts/images/KPIDefault-“&IF(Status=”Completed”,”0″,IF(Status=”In Progress”,”1″,IF(Status=”Failed”,”2″,IF(Status=”No Schedule”,”1″,IF(Status=”R and S”,”1″,IF(Status=”Missed”,”1″,IF(Status=”D S”,”1″)))))))&”.gif’ /></DIV>”

2. List ROW highlight using calculated column:

Create a calculated column and the below formula and make sure to choose the data type as NUMBER

The formula would look something like this, where Env is the name of the column driving the color-coding:

="<div style='text-align:left; background-color:"&IF(Env="Production","yellow",IF(Env="QA","orange",IF(Env="Development","pink","cyan")))&"'>"&Env&"</div>"

Calculated Column Settings Screenshot

Example Screenshot



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s