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:
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:
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.
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.
Then press your delete key. Your chart will now look like this:
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…
Then select the Secondary Axis radio button in the Series Options:
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:
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:
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…
Then from the Axis Options, click on the check box of “Categories in Reverse Order”
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:
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…
Then from the Axis Options menu, change the Maximum Bound to 8.0
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:
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:
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.
Then change the Gap Width in the Series Options to 50%
And then click on the Fill and Line menu and choose the No Fill radio button:
Your chart should now look like this:
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.
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:
Your chart should now look like this:
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. 
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.
Your chart should now look like this:
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:
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
