Il est bien plus beau de savoir quelque chose de tout que de savoir tout d'une chose. [Blaise Pascal]

Using Traffic Lights in Project reporting

Following my previous article on Earned Value Management (Project Management: Understanding Earned Value Management), here is a very simple technique that probably many of us are already using, which helps to clearly define project health on an easy way.
Traffic Lights definition and usage
These indicators are generally used in summary reporting (the status of activities within a project), or in program reporting (the status of projects within a program). They are particularly useful for giving management focus on activities or projects which require particular attention.
We sometimes use the term RAG for these indicators, stating for Red / Amber / Green.
The definitions of RAG indicators are agreed at the beginning of the project and used in reports and presentations regarding its status.
The project team has agreed to define following boundaries on Cost Performance Indicators. (Remember that CPI = EV / AC) The “traffic light” indicators could then be defined as:
  • Red if CPI < 80%
  • Amber if 80% ≤ CPI ≤ 95%
  • Green if CPI > 95%
Usage in Microsoft Project® (Project plan)
Here is an example of using traffic lights inside Microsoft Project®.
How can you implement such indicators in Microsoft Project ®?
  1. Create a new custom field for task, with type: Number,
  2. Select Custom attribute: Formula,
  3. Enter the following formula,
The result is:
  • 1 if SPI >=0.9,
  • 2 if 0.8=<SPI<0.9,
  • 3 if SPI<0.8

  1. Select display graphical indicators,
  2. Choose your graphical indicators.

Usage in Microsoft Office Excel (Project report)
Another example of traffic lights usage, an issue log in Excel, I have used the conditional formatting to color cells.

Here are the rules used for the conditional formatting.

Usage in Microsoft Office PowerPoint (Steering committee presentation)
The last example I wanted to show is the usage of traffic lights in PowerPoint presentations. Here is a print screen of one of my sides regarding my project cost status.
Overall project cost indicator is on top, and a more detailed presentation for Capex / expenses is given below.

Advantages of such technique
  • It is very intuitive and visual,
  • And very effective for summary reporting, or management presentations.

You should pay attention on following points
  • The values of the indicators must be agreed at the beginning of the project, and should be consistent with other projects
  • The summary nature of the indicators can be misleading (i.e. how “green” is an activity, what is the trend?)
  • They do not provide sufficient information to allow diagnosis and interpretation, without discussion.

Consequences and actions
  • Maintain the performance of projects/activities which are “green”,
  • Diagnose the risks and issues of projects/activities which are “amber” and take appropriate action to remedy problems,
  • Diagnose and remedy issues with projects/activities which are “red”, seeking management support and endorsement as appropriate.


  1. worthless .. can't read the formula and no cut/paste functionality.

  2. Hello,
    Click on the image to zoom in. Otherwise the formula is:
    Switch([SPI]>=0.9;1;[SPI]<0.9 And [SPI]>=0.8;2;[SPI]<0.8;3)

  3. I get a syntax error when I enter the formula in Project 2003. Whats the correct syntax for Project 2003? Thanks

  4. Hello Jude, Sorry i cannot check i do not have Project 2003. What I can recommand is to try by yourself to build the command. You should have Function and Fields buttons. Try to identify if the problem is linked to Switch function that maybe do not exist or to the SPI field that could have a different name.

  5. Can you post a link to the template that you posted screen shots of?

  6. the excel version- was the one I referenced in the above request.

  7. Sorry i don't have it anymore, but not too complex to recreate it. If you look at PM templates in general, you can check

  8. Very insightful article, I came across PMstudy which has some great free resources. They also have a free test which can gauge your readiness to take the pmp exam.

  9. change the semi-colon to comma and the formula works


Note: Only a member of this blog may post a comment.