high/low averge bar chart

[miller291]

I'm trying to set up a high/low average bar chart, but can't seem to get the "high" and "low" to be on the same "bar". Am I making any sense? Any suggestions?

This is monthly with an average through a range of a number of years.

Thanks in advance.

 

[thisoldman]

There are two different methods to accomplish this. I've shown column charts. The same methods can be applied to bar charts.

The first method is to plot the high values on the primary axis and the low values on the secondary axis. Then delete the secondary axis, forcing the low values to use the primary axis for the scale.



The second method requires computing a new series, the difference between the high and low values. Then plot the low and the difference series as a stacked column. The high values are not plotted. With a bit of formatting, the columns can "float." This could be the start of a bar and whisker chart.

 

[thisoldman]

I forgot to mention a third method.

Plot a clustered column as in my first example above. The low series remains on the primary axis. Select the low series and change the "Series Overlap" to 100%.

 

[Jon Peltier]

I have a couple alternate approaches.

1. Line Chart.

A. Make a line chart with Low, Average, and High series.

B. Add Up-Down Bars which connect the first and last line chart series.

C. Format High and Low so they have no lines and no markers, format Average so it has markers but no lines.

2. Stacked Column Chart.

A. Insert two columns, with (Average minus Low) and (High minus Average).

B. Make a stacked column chart.

C. Format the bottom series so it has no fill, and the others however you like.

 

[miller291]

Jon,

Option 1 is what I want to end up with. How do I get the up-down bars in there? Thanks.

 

[Jon Peltier]

Excel 2007/2010: Select one of the lines, and on the Chart Tools > Layout tab, click the Up/Down Bars button near the right of the tab.

Excel 2013: Select one of the lines, click the plus icon next to the chart, and check the Up/Down Bars box, or on the Chart Tools > Design tab, click the Add Chart Element dropdown and select Up/Down Bars.