Best chart format to use?

[Trish]

Hi, I am using Excel 2003.

Let's say we want to implement a new system, and to get an idea of what to
buy, we survey a number of like organisations to see what they are using,
whether or not they recommend the system and if they have had any problems.

Eg:

Response Recommend Problems
WSBS 5 4 1
ABC 2 0 2
DEF 1 1 1
GHI 0 0 0
XYZ 2 1 1

The left column shows hthe system. The Response column shows how many
organisations use each system. The Recommend column shows how many
organisations recommend the system (out of the original response) and the
last column shows how many organisations had problems with the system.

If I wanted to present this information in a clear and concise manner, what
type of chart(s) should I use? I did three separate ine charts initially but
I am thinking I should combine them into one to give it more relativity. I
have viewed a number of options and some of them look too cluttered or clumsy.

As an add on (and not necessary an Excel question) what other information
should I solciit in my survey to make a meaningful presentation without
confusing people with too much graphics? Keep it simple and use different
ones, or combine?

I would be very grateful for any suggestions! Thanks. Trish

 

[Jon Peltier]

I think I would plot it this way. Use only the Recommend/Problems data, and
take the negative of the Problem data:

Rec. Prob.
WSBS 4 -1
ABC 0 -2
DEF 1 -1
GHI 0 -0
XYZ 1 -1

Make a stacked column chart. The recommendations reach above the axis, the
problems below.

- Jon

 

[Trish]

Thanks Jon, I have just done what you suggested and it looks good. Is there
a way I can put the labels below the problem area rather than "in" the
problem area?
Thanks so much for your help

 

[Del Cotter]

Thanks Jon, I have just done what you suggested and it looks good. Is there
a way I can put the labels below the problem area rather than "in" the
problem area?

Annoyingly, as you discovered, data labels only show "Center", "Inside
Base", and "Inside End" as display locations. I can see three ways
around this, with various levels of usefulness/difficulty

1) abandon the positive/negative display and just go for a stacked
column chart with the Rec and Prob adding up to "Response". This was my
first idea, but I admit Jon's looks better (no problem with being able
to compare bars that don't line up). This way means you can just use X
axis labels.

2) create a second stacked bar series which simply copies the "Prob"
column, format it to be invisible (no area colour and no border colour)
and format its data label to be "Inside Base". Manually delete it from
the legend, if any.

3) Third option is to change the chart type of that series to "Line"
instead of "Column" type, and format the data label to be "Below".
Again, invisible formatting and delete from the legend, since all you're
interested in is the label.

How much better it would be if Microsoft offered "Outside End" as a
label position!

 

[Del Cotter]

1) abandon the positive/negative display and just go for a stacked
column chart with the Rec and Prob adding up to "Response". This was my
first idea, but I admit Jon's looks better (no problem with being able
to compare bars that don't line up). This way means you can just use X
axis labels.

Oh, there's another couple of options I forgot!

1.5a) format the Y axis scale so that the X axis crosses it at "-9999",
i.e. at the bottom of the chart.

1.5b) format the X axis so that, although it crosses the Y axis at zero,
the tick mark *labels* are "Low" instead of "Next to axis"

I've been assuming it's the category labels you're talking about, and
not the value of the numbers. The numbers are so small I think you
should trust people to use their eyes instead of showing the numerals,
or choose a table instead if you really prefer the integers, or use
Autoshape pictures to turn the stacked columns into countable stacked
shapes if you prefer graphics but want people to see the integers.

 

[Jon Peltier]

I think 1.5b is just what the OP needed. I'd avoid 1.5a, since moving the
axis off zero distorts the relationship between length of bar and value of
data point.

- Jon

 

[Trish]

Del, thank you for your response and lots of useful suggestions. Actually I
did come across a solution that was helpful, which i am happy to pass on.

Double click the 0 line (ie the Category axis) and that opens the Format
Axis box. Under "tick mark labels" select "low". This was perfect for my
given problem. Again, I appreciate your support. Cheers. Trish