Creating a management report that project status within a program or portfolio often requires Microsoft PowerPoint or 3rd party software to develop a project dashboard. Executive management simply wants to see status at a glance and spending unnecessary time creating great looking PowerPoint or Visio diagrams just adds to the project manager’s administrative burden.
Wouldn’t it be easier if you could just click on a button to format and communicate status appropriately?
Over the years, I’ve experimented with Microsoft Project’s custom formulas and bar styles to develop a Microsoft Project dashboard that displays the colors of the project status by changing a custom Health field in the project schedule. In this tutorial, you’ll learn how to create the dashboard with red, yellow and green graphical indicators that automatically format the Gantt chart bars.
Step 1: Add a custom Health indicator
The first step is to customize an available Text field to display the “traffic light” graphical indicator. You need to modify an existing Text field with three valid values: Red, Yellow, and Green. In this example, I use Text30 however, you can use any unused Text field in Microsoft Project.
1. Insert the Text30 field.
2. Right-click the Text30 field and select Customize Fields.
3. Click the Rename button and name the field Health.
4. Click the Lookup button.
5. Add the values Red, Yellow, and Green.
6. Click Close.
7. Click the Graphical Indicators button.
8. Add three tests for the Health field by entering “equals” and Red, Yellow, and Green for the appropriate value. Add a red, yellow, and green Image indicator.
9. Click OK.
By inserting the Health field into the Gantt Chart view, you can select from the Red, Yellow, or Green drop-down values, and the appropriate traffic light will be displayed.
The next step is to modify custom flag fields based on the Health indicator.
Step 2: Create the Late, At Risk, and On Target fields
1. Insert three custom flags in the Gantt Chart view. I selected Flag1, Flag2, and Flag3.
2. Right-click the Flag1 field and select Customize Field.
3. Rename the Flag1 field to “Late.”
4. Rename the Flag2 field to “At Risk.”
5. Rename the Flag3 field to “On Target.”
6. Select the Flag1 field, click the Formula button, and enter the formula:
7. Click OK.
8. Click the Flag2 field, click the Formula button, and enter the formula:
9. Click the Flag3 field, click the Formula button, and enter the formula:
10. Click OK.
By changing the Health graphical indicator field, the Flag1, Flag2, or Flag3 columns will change from No to Yes as appropriate.
11. Once you see the Late, At Risk and On Target columns change when the Health indicator changes, go ahead and hide the Late, At Risk and On Target columns.
Step 3: Create Red, Yellow, and Green bar styles
The next step is to create the specific Red, Yellow, and Green Gantt chart bar styles that will align to the graphical indicator color.
You can modify the Gantt chart bar styles to suite your preferences. In this example, we will add three new bar styles to match the Health indicator and the three flags.
1. Select Format | Bar Styles.
2. Insert three rows and name the rows Red, Yellow, and Green at the bottom of the Bar Styles box
The Bar Styles are processed in a hierarchical order so you need to move these styles to the bottom. Otherwise, another style can overwrite your custom style if the Show For … Tasks criteria matches.
3. On the Red row, click the Color drop-down menu and change it to Red.
4. Change the Show For … Tasks field to the Flag1 field. Repeat the step for Yellow and Green and assign Flag2 and Flag3 respectively. Remember to assign the yellow and green colors as appropriate.
5. Click OK
By following these first three steps, you’ve created a set of customized fields that will change the color of the Gantt Chart bar, depending on the Red, Yellow, or Green status.
Step 4: Enter project data
The next step is to enter the projects or work streams into the dashboard.
In this example, every project in your program or portfolio will be represented as a single task in the portfolio. Please note the the individual tasks existing in a different MS Project plan. You could role up all the project schedules to provide more detailed reporting. However, I’ve found for displaying program or portfolio health with timelines, creating a separate MS Project file is easier.
For a typical program or set of projects in the portfolio, I recommend entering the following data:
- Project Name
- Project Start Date
- Project Duration (which calculates the project finish)
- Any dependencies between the projects as necessary
Step 5: Change the Health indicator based on status
As you prepare for your next portfolio review or program status meeting, you can use this program dashboard to quickly communicate each project’s status in the portfolio. You can also add various Microsoft Project data fields to the Gantt Chart table or customize the Gantt Chart further. To change the color, select the appropriate status from the Health field and the Gantt chart bar will change color.
Instead of drawing a separate Gantt chart or PowerPoint chart, project managers can use the Microsoft Project data to quickly represent status at a summary level. The dashboard can also be extended to assess new portfolio opportunities or identify specific projects that are funded, not started, or cancelled.
Additional Project Dashboard Considerations
If you are familiar with Microsoft Project’s formulas, you can write additional formulas that will calculate the status rather than rely on purely subjective interpretation. If a project misses its end date by 5 days, the indicator could be set to automatically to Yellow. If it exceeds 10 days, the status can be changed to red. Graphical reporting helps draw attention to issues and problems in a portfolio or program without having to dig into a lot of schedule data. Let the tool do it for you!