Create project status spectrum chart in Excel

This tutorial will talk about how to create an especial chart – project status spectrum chart in Excel. The project status spectrum chart is a type of bar chart with a slider block which the bar is filled as a spectrum from red to yellow to green to represent the project status as below screenshot shown.

• Create a project status spectrum chart in Excel
• Create a project status spectrum chart in Excel with an easy feature
• Download Project Status Spectrum Chart sample file
• Video: Create project status spectrum chart in Excel

Create a project status spectrum chart in Excel

To create the project status spectrum chart, first, you should insert three helper columns, please do as follows:

First, create helper column data

1. Enter number 200 into column C which will be used as the max scale value, see screenshot:

2. In column E, please enter number 5 which is used as the width of slider block, you can adjust the width as your requirement, see screenshot:

3. And then, in column D, apply the below formula into cell D2, and drag the fill handle to copy the formula to other cells, see screenshot:

Second, create the project status spectrum chart based on the data

4. After creating the helper data, now, please select the data range including the helper columns, and then click Insert > Insert Column or Bar Chart > Stacked Bar, see screenshot:

5. And a stacked bar chart has been inserted, you can delete the unneeded elements, such as chart title, legend and gridlines as you need, see screenshots:

6. Then, click to select the order data series bar (the blue bar), and then right click, in the popped out context menu, choose Delete to delete this data series, see screenshot:

7. Then, go on right clicking the slider fill data series bar (the grey bar), select Format Data Series from the context menu, see screenshot:

8. In the opened Format Data Series pane, under the Series Options tab, select Secondary Axis option, and you will get the chart as below screenshot shown:

9. Then, repeat the above step 7- 8 to format the slider size data series bars as Secondary Axis as well, see screenshot:

10. In this step, you need to make the slider fill series bars a little wider on the chart, please right click the slider fill data bar (the grey bar), and then choose Format Data Series in the context menu, in the Format Data Series pane, under the Series Options tab, change the Gap width to 50%, and you will get the below chart as you need:

11. And then, you should hide the slider fill series bars, still in the Format Data Series pane, go to the Fill & Line tab, and select No fill in the Fill section, see screenshot:

12. And then, you can change the color and border for the slider block, select the slider size data series, and right click, then choose Format Data Series to open the Format Data Series pane from the context menu, under the Fill & Line tab, select Solid fill in both of Fill and Border sections, then specify a fill color and border color for the slider block as you need, see screenshot:

13. And now, you should create the red, yellow and green spectrum that is behind the project status slider. Right click the spectrum data series bar (the orange bar), and choose Format Data Series, in the Format Data Series pane, under the Fill & Line tab, in the Fill section, please do the following operations:

• Select Gradient fill option;
• Choose Radial from the Type drop down list;
• In the Gradient stops section, you should create 3 stops, give 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 color of red with a position of 100%.

14. And then, click to select the chart, and click the Chart Elements icon, and select Axes > Secondary Vertical to show the secondary vertical axis, see screenshot:

15. Then, right click the left vertical axis, and choose Format Axis from the context menu, see screenshot:

16. In the opened the Format Axis pane, under the Axis Options tab, check the Categories in reverse order option, see screenshot:

17. Go on clicking to select the right axis, and still in the Format Axis pane, check the Categories in reverse order option as well, and you will get the chart as below shown:

18. Then, right click the top axis, and then choose Format Axis, in the Format Axis pane, under the Axis Options tab, change the Maximum Bound to 200 which is the max scale of your chart, you can change the number to your own data, see screenshot:

19. After changing the max number of the top axis, then press Delete to delete it, and then you can delete the right axis as well, and you will get a chart as below screenshot shown:

20. Then, go on right clicking the bottom axis, and choose Format Axis, in the Format Axis pane, under the Axis Options tab, change the Maximum Bound to 200 which is the max scale of your chart, you can change the number to your own data, see screenshot:

21. Now, the project status spectrum chart has been created successfully, when changing the original value, the slider block will be moved atomically as below demo shown:

Create a project status spectrum chart in Excel with an easy feature

Download Project Status Spectrum Chart sample file

Video: Create project status spectrum chart in Excel