How-to Make an Excel Project Status Spectrum Chart

Here is the long awaited demonstration of a recent Friday Challenge.

 

The challenge was to recreate a Project Management Status Chart that I saw on a recent project.  The unique thing about this chart is that they used a spectrum from Red to Yellow to Green to represent the project status.  Here is what the final chart will look like:image

This was originally a graphic drawn in Microsoft PowerPoint.  But as a Project Manager, you would then move each individual slider left or right, however, since it is a graphic, you can also move it up and down off of the status bar.  In the Microsoft Excel Status Indicator chart, we will change a value and the status slider will move to the correct location.  So lets get to it.

 

The Breakdown

1) Create Chart Data

2) Create Stacked Bar Chart

3) Delete Slider Position Data Series

4) Move Slider Fill and Slider Size to Secondary Axis

5) Show Secondary Vertical Axis

6) Check “Categories in Reverse Order” on Both Vertical Axes

7) Change Maximum Bound to 8 on Both Horizontal Axis’

8) Delete Both Secondary Axes

9) Change Slider Fill Series Color and Gap Width

10) Change Fill Color of Slider Size Series

11) Change Fill Color of Spectrum

12) Delete Legend and Primary Horizontal Axis

 

 

Step-by-Step

1) Create Chart Data

First we need to create our chart data.

a) Column A =  Create Status Categories

b) Column B = Create Slider Position Data (Value between 1 and 8)

c) Column C = Create Spectrum Data (Value = 8)

d) Column D = Create Slider Fill Data (Formula: D2 = B2-E2/2 and copy down to D6)e) Column E = Create Slider Size Data (Value = 0.25)

Your final chart data will look like this:image

 

2) Create Stacked Bar Chart

Now lets create the start of our Project Status Chart.  Highlight cells A1:E6 and then click the Insert Ribbon.  Then click on the Stacked 2-D Bar Chart button.image

 

3) Delete Slider Position Data Series

One of our series in the data doesn’t need to be in the chart as it will be derived by other data series.  So we need to delete the Slider Position data series from the chart.  To do this, select your chart, then select the blue series on the left that represents the Slider Position data series.

image

Then press your delete key.  Your chart will now look like this:image

4) Move Slider Fill and Slider Size to Secondary Axis

We need to move the Slider Fill and Slider Size series to the secondary axis so that they will overlap the Spectrum series.  To do this, select the grey Slider Fill series and press CTRL+1 or right click on it and select Format Data Series…image

Then select the Secondary Axis radio button in the Series Options:image

Then repeat these steps for the orange Slider Size data series and move it also to the secondary axis.  Your Excel chart should now look like this:image

5) Show Secondary Vertical Axis

Now one issue that I have with Excel is that when you move a data series to the secondary axis, it shows you one of the axis but not the other.  I think they don’t want to confuse people.  In this case, even though the secondary vertical axis isn’t shown, it is there but hidden.  So we need to show the secondary vertical axis to perform future actions.  To show the secondary vertical axis in an Excel Bar Chart, first select the chart.  Then go to the Design Ribbon.  Then select the Add a Chart Element button and then choose the Axis menu and then choose the Secondary Vertical option.  Your chart will now look like this:image

 

6) Check “Categories in Reverse Order” on Both Vertical Axes

Now that we are showing both vertical axis’, we need to flip them.  To do that, select the chart, then select the the left vertical axis and press CTRL+1 or right click on the vertical axis and choose Format Axis…image

Then from the Axis Options, click on the check box of “Categories in Reverse Order”image

Repeat this step for the right (secondary) vertical axis.  Then your chart should now look like this with both vertical axis categories matching each other:image

 

7) Change Maximum Bound to 8 on both Horizontal Axis’

The next step is to make both horizontal axis’ a maximum bound = 8.  To do that, select the chart, then select the the top horizontal axis and press CTRL+1 or right click on the vertical axis and choose Format Axis…image

Then from the Axis Options menu, change the Maximum Bound to 8.0image

Repeat this step for the bottom (secondary) horizontal axis.  Then your chart should now look like this with both horizontal axis values matching each other:image

8) Delete Both Secondary Axes

Now that we have done what we needed to do with the secondary axis’, we can delete them.  To do this, select the chart, then select the secondary horizontal axis and press your delete key.  Repeat this step for the secondary vertical axis.  Your chart should now look like this:image

9) Change Slider Fill Series to “No Fill” Color and Gap Width to 50%

We are getting closer.  Now we need to hide the Slider Fill series and make it a little wider on the chart.  To do this select your chart, then right click on the grey Slider Fill series and select Format Series… from the pop-up menu.image

Then change the Gap Width in the Series Options to 50%image

And then click on the Fill and Line menu and choose the No Fill radio button:image

Your chart should now look like this:image

10) Change Fill Color of Slider Size Series

In the example, the slider was grey with a black border.  To do this select your chart, then right click on the orange Slider Size series and select Format Series… from the pop-up menu.image

And then click on the Fill and Line menu and choose the Solid Fill radio button and choose a medium grey color and a black or dark border:image

Your chart should now look like this:image

11) Change Fill Color of Spectrum

Now we need to create the red, yellow and green spectrum that is behind the project status slider.

To do this select your chart, then right click on the orange Spectrum series and select Format Series… from the pop-up menu. image

And then click on the Fill and Line menu and choose the Gradient Fill radio button and then add or delete the Gradient stops until you only have 3 stops.  Then make the left stop a color of green and a position of 0%.  Make the middle stop a color of yellow with a position of 50% and the right stop a position of 100% and a color of red.image

Your chart should now look like this:image

 

 

12) Delete Legend and Primary Horizontal Axis

The last thing to do to match the sample is to remove the legend and optionally the horizontal axis.  You can easily do this by selecting the legend or horizontal axis and press your delete key.  Your final chart should now look like this:image

 

Video Demonstration

 

Free File Download

Download the sample Excel Project Status file:Excel-Project-Status-Spectrum-Chart.xlsx

 

 

Would you use this as a Project Status Indicator by Phase for your project status reports?  Let me know in the comments below.

 

Steve=True