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





24 COMMENTS

  1. Nice Article. By the way, the plural of axis is axes (pronounced ackzees). There’s no apostrophe involved at all. 🙂

    • Hi Eddie, thanks for the comment, you are totally right. I looked it up after creating my next post and have been spelling it correctly ever since 🙂 lol but forgot to go back and update the previous post. Should be up and fixed shortly. Thanks for being a fan! Steve=True

    • Hi JD.

      Good question. To do this, you would want to change your Spectrum Series to all 5 or all 6 values. Then your Slider position series would have to change to a maximum of 6.

      Hope this helps.

      Steve=True

  2. Hi
    Great article, love the color idea.
    All I want is to now move the thumb with a mouseover. Do not want to click and drag.

    How to do ? Thanks

  3. This is exactly what I was looking for when I started my project, so thanks. However, now my boss wants to be able to add a second data point to each bar, even if it’s an arrow below. Help?

  4. Nice job! Once I figured out what you were doing, the concept is very creative.

    I set my slider width to 0 for steps in a project plan that are in the future. That turns them off but displays the bar as “something we will measure when we get there”.

    Thanks for the write up.

    -jimt

  5. Hello Steve=True, thank you for your elaborate article. Maybe
    you’re interested in a VBA automation of the 12 steps it took to create a spectrum chart. Regards, Rene Vis, Netherlands

    Option Explicit

    Private Type typGradientSettings
    RGB As MsoRGBType
    Position As Single
    End Type

    Sub Create_spectrum_chart()
    ‘ source: https://www.exceldashboardtemplates.com/how-to-make-an-excel-project-status-spectrum-chart/
    Dim r As Range
    Dim sh As Worksheet
    Dim lo As ListObject
    Dim shp As Shape
    Dim ch As Chart
    Dim srSpectrum As Series
    Dim srSliderPos As Series
    Dim srSliderFill As Series
    Dim srSliderSize As Series
    Dim axC1 As Axis
    Dim axC2 As Axis
    Dim axV1 As Axis
    Dim axV2 As Axis
    Dim grp As ChartGroup
    Dim gs As GradientStop
    Dim i As Integer
    Dim iCount As Integer
    Dim iMaximumScale As Long

    ‘ 1) set raw data for chart, presumably from range A1; please change to your own needs
    Set sh = ActiveSheet
    Set r = sh.Range(“a1”)
    r.Select

    Set lo = r.ListObject
    If lo Is Nothing Then
    If VBA.MsgBox(“Create table from range ” & r.Address & “?”, vbYesNo) = vbYes Then
    Set lo = sh.ListObjects.Add(xlSrcRange, r.CurrentRegion, , xlYes)
    Else
    GoTo Ex
    End If
    End If

    ‘ 2) create chart, type xlBarStacked
    Set shp = sh.Shapes.AddChart
    shp.Select
    Set ch = shp.Chart
    Set srSpectrum = ch.SeriesCollection(“Spectrum”)
    Set srSliderPos = ch.SeriesCollection(“Slider Position”)
    Set srSliderFill = ch.SeriesCollection(“Slider Fill”)
    Set srSliderSize = ch.SeriesCollection(“Slider Size”)
    iMaximumScale = srSpectrum.Values(1)

    ch.ChartType = xlBarStacked
    ch.SetSourceData Source:=lo.Range

    ‘ 3) delete Slider Position data serie
    srSliderPos.Delete

    ‘ 4) Move ‘Slider Fill’ and ‘Slider Size’ to Secondary Axis
    srSliderFill.AxisGroup = xlSecondary
    srSliderSize.AxisGroup = xlSecondary

    ‘ 5) Show Secondary Vertical Axis
    ch.SetElement Office.MsoChartElementType.msoElementSecondaryCategoryAxisShow

    Set axC1 = ch.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary)
    Set axC2 = ch.Axes(XlAxisType.xlCategory, XlAxisGroup.xlSecondary)
    Set axV1 = ch.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary)
    Set axV2 = ch.Axes(XlAxisType.xlValue, XlAxisGroup.xlSecondary)

    ‘ 6) Check “Categories in Reverse Order” on Both Vertical Axes
    axC1.ReversePlotOrder = True
    axC2.ReversePlotOrder = True

    ‘ 7) Change Maximum Bound to iMaximumScale on both Horizontal Axis
    axV1.MaximumScale = iMaximumScale
    axV2.MaximumScale = iMaximumScale

    ‘ 8) Delete Both Secondary Axes
    axC2.Delete
    axV2.Delete

    ‘ 9) Change Slider Fill Series to “No Fill” Color and Gap Width to 50%
    srSliderFill.Format.Fill.visible = msoFalse
    Set grp = ch.ChartGroups(2) ‘ group of dataseries, plotted against secondary axis
    grp.GapWidth = 50

    ‘ 10) Slider Size series: change Fill Color to medium grey and border to black
    With srSliderSize.Format
    With .Fill
    .visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorBackground1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = -0.25 ‘ medium grey color
    .Transparency = 0
    .Solid
    End With
    With .Line
    .visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorText1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
    End With
    End With

    ‘ 11) Spectrum serie: set GradientStops
    srSpectrum.Select
    With srSpectrum.Format.Fill
    .TwoColorGradient msoGradientVertical, 1

    Dim aGS(2) As typGradientSettings
    aGS(0).Position = 0
    aGS(0).RGB = vbGreen

    aGS(1).Position = 0.5
    aGS(1).RGB = vbYellow

    aGS(2).Position = 1
    aGS(2).RGB = vbRed

    For i = 0 To UBound(aGS)
    .GradientStops.Insert aGS(i).RGB, aGS(i).Position, 0
    Next

    ‘ delete other GradientStops
    iCount = UBound(aGS) + 1
    For i = .GradientStops.Count – iCount To 1 Step -1
    .GradientStops.Delete i
    Next
    End With

    ‘ 12) Delete legend and hide last visible Value Axis
    ch.Legend.Delete
    axV1.Delete

    Ex:
    Set gs = Nothing
    Set grp = Nothing
    Set axC1 = Nothing
    Set axC2 = Nothing
    Set axV1 = Nothing
    Set axV2 = Nothing
    Set srSpectrum = Nothing
    Set srSliderPos = Nothing
    Set srSliderFill = Nothing
    Set srSliderSize = Nothing
    Set ch = Nothing
    Set shp = Nothing
    Set lo = Nothing
    Set sh = Nothing
    Set r = Nothing
    End Sub

    • Hi Dano, sure, there isn’t a limit. The sliders just affect a value in the spreadsheet that then can be used to control other things like index or vlookup formulas that affect what is displayed in the graph.

  6. Hi Steve=True,

    Is there a way to make the slider size on multiple charts with different bounds the same size with a formula, or is the only way to eyeball them manually?

    Great write up!
    Ben

    • Thanks Ben. If you need them all the same size, I would create a chart and make a copy of it and then modify the chart series. Hope this helps. Thanks for your great comment.

    • Hi Wyn, if you mean how can i change a slider other than the numbers, you can create a form control like a spinner and it can change the slider, but in reality, it is just changing a number on the spreadsheet that affects the chart.

  7. Great article, this amazes me! I tried to apply this creative graph to show RAG status of our business metrics. I’m not sure though how to control the gradient, like where the green would stop (and where the amber, or red would start) since we have different target values for each metric. For example, metric 1 green is only up to 20; metric 2 green is up to 5 only, >5 is already in amber. I only use one graph and the values (sliders) move as expected when I change metrics, but the gradients remained as is.

    Is there a possible way to do this?

    Thanks in advance, Steve=True!

    • Hi \Lai, not sure how I understand the gradient numbers you presented as you have 2 greens 🙂 . The gradient is applied across the entire bar, so edit the fill options on your gradient bar and add a few more to get the desired result. Here i have moved the yellow more left and added more red, green and yellow to get less gradient between sections. Hope this helps. https://www.exceldashboardtemplates.com/wp-content/uploads/2018/03/SampleMoreGradientFills-ProjectSpectrumChartorig.png

      • Hello Steve=True, I’m so sorry for the confusion. In my example, I made only one graph to show the status of, say, 5 metrics — but the graph would show only one metric at a time. For example, when I want to see the status of metric#1, I would select metric#1 in my drop-down menu and this referenced the value (say, 5) from a table somewhere. Metric#1’s status should be below 20 to be in green zone. So the graph will then show metric#1’s value of 5 still in green. Now when I select another metric from the drop-down, this has a different target for green. Metric#2’s target to be in green should be <3. So if the value of metric#2 is 7, the graph should then change the gradient to show that metric#2 is way beyond the green zone. I guess, what I'm trying to see if possible is if the colors would move as well depending on the values/target referenced.

        • Hi Lai, you could probably change the gradient position, but you would have to do that with a Macro or VBA to update these behind the scenes as there is no link from the color gradient to the spreadsheet cell available in Excel.

  8. Beauty chart SteveEqualsTrue!
    One thing I would like to note is that in your sample source data you have already accounted for 1/2 the slider fill size.
    If someone is creating a set of data based on some other input, that difference should be accounted for to ensure the slider lines up properly centered on the correct number.

    • Thanks. Yeah the slider size being 1/2 size is to align it on the correct data point and I have reduced the same amount on the data point position so that it aligns.

LEAVE A REPLY

Please enter your comment!
Please enter your name here