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.

  1. 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:
    SharePoint Excel Spreadsheet Data
  2. 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.
    SharePoint Create List
  3. Fill in the Name that you want your list to be called, browse for your spreadsheet, and click on the Import button.
    SharePoint Import List
  4. 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.
    SharePoint Exel Import
  5. Your imported list will display.
    SharePoint Imported List
  6. Navigate to List Tools -> List -> List Settings.
    SharePoint List Settings
  7. Click on Columns -> Create Column.
  8. Enter the Column Name, select Calculated as the field Type.
    SharePoint Calculated Column
  9. 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.
    SharePoint Calculated Field Formula
  10. Under List Settings, click on Title, description, and navigation and change the navigation preferences to display on Quick Launch and click Save.
    SharePoint Quick Launch
  11. Click on our Project Status Report on the Quick Launch bar and navigate to List Tools -> List -> Modify View.
    SharePoint Modify View
  12. 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.
    SharePoint Modify Columns In View
  13. 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…
    SharePoint Unformated HTML
  14. Navigate to Site Actions -> Edit Page and click the Add A Web Part link.
    Site Actions Edit Page
  15. Select the Content Editor Web Part from the Media and Content Category and click the Add button.
    SharePoint Content Editor Web Part
  16. On the Content Editor Web Part click the link that says Click here to add new content, and then click HTML -> Edit HTML Source.
    SharePoint Edit HTML Source
  17. Paste in the following code into the HTML Source and click OK. The JavaScript code was provided via Path to SharePoint.
  18. Drag the Content Editor Web Part to position below your Project Status Report.
  19. Navigate to Page -> Stop Editing to save your changes.
    SharePoint Page Stop Editing
  20. And you now have a colored status indicator for your report using a Calculated Field based on the Status of your projects!SharePoint Final Result
Like this post? Share it!