Stacked column chart (I suppose)

  • Thread starter Thread starter Hans Knudsen
  • Start date Start date
H

Hans Knudsen

Hello
I have the following data:

Your Benchmark group
company minimum median maximum
587332 47609 271686 406557 638592 923238

I want to have 0 at the top of the x-axis and 1,000,000 at the bottom. If I set up a stacked column chart of the Benchmark group
data (47609, 271686, 406557, 638592, 923238) how can I indicate the value for "Your company".

Hans Knudsen
 
Hans said:
I have the following data:

Your Benchmark group
company minimum
median maximum
587332 47609 271686 406557 638592 923238

I want to have 0 at the top of the x-axis and 1,000,000 at the bottom.
If I set up a stacked column chart of the Benchmark group
data (47609, 271686, 406557, 638592, 923238) how can I indicate the
value for "Your company".
Click to expand...

Assuming I understand your goal, here's one way: Add another series for
"Your company." Format that series as a line chart. Change the
appearance of the series to have no line and a horizontal mark (i.e. -) at
each data point. Change the size of the horizontal mark to match the
width of your columns, then label the data point with the series name.
 
Hi Hans -

Here's one way to chart your data. I rearranged your data as follows:

Benchmark Your Company
Min 47609
224077
Median 134871
232035
Max 284646
Your Company 587332

The numbers under Benchmark are the difference between that level and
the previous level, so the stacking of values makes sense.

Then I selected the entire 7-row, 3-column range, and made a stacked
column chart, with the data by rows. The result is a six-series chart.
Five series only have values stacked in the left column, and the sixth
just shows Your Company.

You can dress it up a bit. Make the Min series invisible (no border, no
fill), so the rest of the stacked columns float between min and max. Or
even make a box and whisker chart, like I describe here:

http://www.geocities.com/jonpeltier/Excel/Charts/BoxWhiskerV.html

You can also change the chart type for Your Company to a line chart, to
get a marker that floats alongside the stacked bars. Select just that
series, and choose Chart Type from the Chart menu.

- Jon
 
Thanks to dvt and Jon.
I have to say that Jon's solution was almost exactly what I wanted. Two more questions however:

1. Is there any way to have the value for Your company shown inside the relevant data point of the Benchmark group series?
2. Is there any way to get rid of the borders at top and at right but still have the y-axis and x-axis at the bottom. Note that I
have the values in reverse
order (0 at the top of the y-axis and 1.000.000 at the bottom).

Hans
 
Hans -

1. To line up the specific company with the stacked columns, put its
value in the same column as the bars. Convert this series only to a
line chart as described in my earlier post.

2. I forgot about the reversal of values. Double click on the Y axis,
and on the Scale tab, check the Values in Reverse Order and Category
Axis Crosses At Maximum Value options. To eliminate the lines at the
top and side, remove the plot area border. Double click on the plot
area (within this box), and on the Patterns tab, click None under
Border, and pick a fill color you like.

- Jon
 
Back
Top