How to create a min-max bar chart in excel 2007 ?

[Junkyard Engineer]

I have a list of 24 set of data. Each category have a min-max set in $.

When I'm using stacked bar chart and define the min value with no fill, I
get the look I'm looking for. The problem is with the X-axis which shows the
total amount of min+max instead of using only the max series as data set.

After looking around for quite a good time, I figured somebody here might
have stumbled on that problem.

Thanks

 

[Ed Ferrero]

Hi Junkyard,

I'm not sure that I understand your question. However, have you tried using
Up/Down-bars to plot a min/max chart?

To do this
- select the two data series (minimum and maximum values)
- use the Insert ribbon to add a line chart with no markers
- select the chart and use the Chart Tools - Layout ribbon to show Up/Down
bars
- format each series to show no line (in line color) and no marker

This shows just the Up/Down bars which look like a floating column chart.
The series remain simple line charts, so it should be easy for you to format
the axis as desired.

Ed Ferrero
www.edferrero.com

 

[Andy Pope]

Hi,

In order to get the Max point of the bar in the correct place you will
need to re calculate the max values used. So in another column plot the
formula which would be MaxValue - MinValue.

Or use a clustered bar chart. Format the Min series to have no border
and a fill colour that matches the plot area fill colour.
Set the Overlap value to 100.
reverse the plot order so Min is plotted on top of Max.

The only draw back with the overlap approach is the obscuring of gridlines.

Cheers
Andy

 

[Junkyard Engineer]

Thanks Ed. Didn't saw that option.

This approach have 2 drawbacks as I can see it :

1. there's no possibility to add automatic label to show $ values min/max on
each side of the bars
2. the bars are always vertical. This type of graph is best shown sideways,
i.e. using horizontal bars.

or maybe there's more hidden options ?

 

[Junkyard Engineer]

Thanks Andy. Good trick !

In fact, I used the max-min values and selected min and that 3rd series to
get real numbers on the axis. But label values should be edited manually to
enter correct max values.

This is a pretty standard graph, I can't imagine that the guys at MS didn't
saw that coming.

 

[Andy Pope]

Have a look at Rob Bovey's chart labeler to make changing the data
labels easier.
http://www.appspro.com/Utilities/ChartLabeler.htm

Cheers
Andy