Recently I was tasked with creating a report for our PMO that showed information about all of our current projects and a stoplight status indicator on whether the project was on track or not. After doing some research online, I found out that I could create a calculated field based on a status and display a colored circle that will help executives quickly view the status of each project. I have created a tutorial to show the steps, so that you can create your own colored status field in SharePoint.
In our case we used the following status codes:
- Green = Project is on track
- Yellow = Project is falling behind
- Red = Project is behind
- Purple = Project is on hold
Important Note: For the calculated field to work, you will need to make sure that your Status field contains a number, because we will base the color selected on this number. See the Status column in the screen cap in step #1 below.
- The first thing I did was collected all of the project data and entered it into an Excel spreadsheet and saved it to my computer. Here is some sample data that we will work with for this example:
- Then imported my Excel data into a SharePoint List. In SharePoint, navigate to Site Actions -> More Options -> Filter By: List -> Import Spreadsheet and click the Create button.
- Fill in the Name that you want your list to be called, browse for your spreadsheet, and click on the Import button.
- In the Import to Windows SharePoint Services list dialog box select Range Type: Range of Cells, click on Select Range input box and use your cursor to select the range of cells that need to be imported from your Excel Spreadsheet, and click the Import button.
- Your imported list will display.
- Navigate to List Tools -> List -> List Settings.
- Click on Columns -> Create Column.
- Enter the Column Name, select Calculated as the field Type.
- Enter in the following Formula and click the OK button.
Note: that the formula is selecting the 1st character of the value in the Status Column “LEFT(Status,1)“. If the number in your status field is in a different location you may have to adjust the formula a little to get the correct result. The number in the status column is then used in the CHOOSE() function to determine which color should be used. In our example if the number is 1 then it will be green, if the number is 2 the color will be orange and so on.
html1="<DIV style='font-weight:bold; font-size:24px; font-size:24px; color:"&CHOOSE(LEFT(Status,1),"green","orange","red","purple")&";'>•</DIV>"
- Under List Settings, click on Title, description, and navigation and change the navigation preferences to display on Quick Launch and click Save.
- Click on our Project Status Report on the Quick Launch bar and navigate to List Tools -> List -> Modify View.
- Update the Stoplight to be in the 1st position from the left and uncheck the Display checkbox for the “Status” field to hide it. You can also order your list based on Client Name if applicable.
- You will notice that our Stoplight column displays the HTML code we entered in the Formula field rather than actually displaying our colored circles. This is because we still have one more setting to change…
- Navigate to Site Actions -> Edit Page and click the Add A Web Part link.
- Select the Content Editor Web Part from the Media and Content Category and click the Add button.
- On the Content Editor Web Part click the link that says Click here to add new content, and then click HTML -> Edit HTML Source.
- Drag the Content Editor Web Part to position below your Project Status Report.
- Navigate to Page -> Stop Editing to save your changes.
- And you now have a colored status indicator for your report using a Calculated Field based on the Status of your projects!