This was easier for me as horizontal bars. When I rotated to columns, I had to think about what I was doing.
You might also look at bullet charts:
https://peltiertech.com/bullet-charts-in-excel/
http://stephanieevergreen.com/easiest-bullet-charts-in-excel/
I'll assume you have normalized the data and the sweet spot range to a zero to 100 scale. I chose to have a box for values between 15 and 80 on that 100 point scale. I've written these instructions for Excel 2013 and later
The data:
Book1 |
---|
|
---|
| B | C | D | E |
---|
4 | Stacked Columns | | | |
---|
5 | | Low | Mid | |
---|
6 | Alpha | 15 | 70 | |
---|
7 | Beta | 15 | 70 | |
---|
8 | Gamma | 15 | 70 | |
---|
9 | Delta | 15 | 70 | |
---|
10 | | | | |
---|
11 | Scatter Plot | | | |
---|
12 | | x1 | Data_Pct | x3 |
---|
13 | 1 | 15 | 89 | 85 |
---|
14 | 2 | 15 | 13 | 85 |
---|
15 | 3 | 15 | 26 | 85 |
---|
16 | 4 | 15 | 69 | 85 |
---|
17 | | | | |
---|
18 | Lo_Labels | Hi_Labels | | |
---|
19 | 3.2 | 30.1 | | |
---|
20 | 1.8 | 7.8 | | |
---|
21 | 0.4 | 2.6 | | |
---|
22 | 1.6 | 7.7 | | |
---|
|
---|
My default charts appear with no axis lines. Yo will have to delete any that may appear.
Chart One
Select cells B5:D9 and insert a stacked column chart. Set the vertical axis to a fixed minimum of zero and a fixed maximum of 100. Delete the legend. I chose a nearly square chart size of 3 x 3.25 inches.
Chart Two
Select D6:D9 (excluding the headers) and copy, Ctrl+C. Select the chart. From the Home tab, press the downward pointing arrow beneath the large clipboard icon, the Paste icon. Select Paste Special. In the pop-up dialog, select the radio buttons for "New series" and "Columns". Select the "Categories (X Labels) in First Column". The other two checkboxes should not be selected. They should be blank. Click OK and the new series will be pasted into the chart. At this point I selected one series and formatted the Gap width to 180%.
Chart Three
Select one series. From the right-click context menu, select "Change Series Chart Type". Series 3, 4, and 5 should be changed to "Scatter" (markers and no lines) on the secondary axis. If the secondary horizontal axis (that's the secondary x-axis) does not appear, add it now. The secondary vertical axis should have appeared automatically. Delete that secondary vertical axis.
The scatter plot points don't line up with the columns.
Chart Four
Change the secondary x-axis to a fixed minimum of 0.5 and a fixed maximum of 4.5. This should center the scatter plot points on the columns.
Not for Excel 2010 or earlier –
Select Series 3 and add Data labels to the right of the plotted points. With the data labels selected, go to the format pane. Under "Label Options", uncheck all the default checkboxes and place a check in the box labeled "Value From Cells". In the pop-up, enter B19:B22 or use your mouse to select those cells. Press OK.
Do the same for Series 5. This time the labels are in cells C19:C22.
The data label appeared too close to the columns. I fixed this by formatting the cells in B19:C22 with a custom number format: _)0.0 (underscore+rt.parenthesis+zero+dot+zero). This adds a space equal to the width of the parenthesis on the left side of the numbers. The cell formatting is automatically carried over to the data labels and add a small space between the column and the text.
Chart Five
Choose Series 3 in the chart and add Error Bars. Format the horizontal bars ("Series 3 X Error Bars") to Direction: Both, End Style: No Cap, Error Amount: Fixed value: 0.18. The 0.18 works here; you may have to adjust the value.
Format the Series 3 Y Error Bars to Direction: Minus, End Style: No Cap, Error Amount: Percentage: 100.0%. I changed the line width from 0.75 pt to 2.0 pt for the thicker line.
Add Series 5 Error Bars. The horizontal error bar has exactly the same format settings as the Series 3 error bars. The Series 5 Y Error Bar setting differs from the Series 3 setting only in Direction, you set this to Plusfor Series 5.
We can't delete the upper horizontal axis or the scatter plot points will shift. Instead, select that axis and go to the format pane. Under "Axis Options: Number" change "Category" to "Custom". In the Format Code box, enter: ;;; (three semicolons, no spaces). This is the format code that tells Excel to not display any number or text.
At this point, I deleted the primary vertical axis and the gridlines. I then clicked inside the inner plot area to select it and used the grab handles to enlarge the plot area upwards and to the left.
Chart Six
Select Series "Low" and format it to have no fill and no line.
Series 3 and Series 5 are formatted to have Marker: None.
Series 5 Is formatted to have a 10 pt black diamond marker.
Series "Mid" was first formatted to a light blue-gray fill with no line.
Personally, I found the gradient distracting—my attention went to the pretty colors instead of the more important plotted data point. If you want a gradient, the ones shown here are Type: Linear, Angle 270°, four stops: yellow at Position: 0%, green at Position: 25%, green at Position 75%, and yellow at Position: 100%.