How to insert an easy to read chart for multiple Axis?

[ejwjohn]

Hello,

I have a Yacht Club survey in place which is being summarized in an excel sheet as indicated in the attached image.

There are 6 questions similar to that in A6, with the answer specific to 2 types of boat as in B/C6 and D/E6
Each question has 3 possible answers as in A7,8 & 9, with the answers in the corresponding B/C and D/E cell pair which represent the same value but in different formats. ( numeral and %)

The challenge for me is how do I get one chart that represents the A6 to D9 cells? as indicated earlier I have 5 other questions on this sheet so I was hoping to get the 6 charts along with the data on the same sheet and still make the charts understandable?

Thanks in advance of your help.
JohnW

 

[GraH]

What about this for starters?

Now depicting the right chart is about passing on a message. Not sure what your message would be. Some examples: response levels are high/low, No/Yes/Maybe stands outs, differences between Dragons and Squids, differences between questions, simply the distribution of answers.

 

[ejwjohn]

G,

Thanks, so if I layout the data differently, you are applying a BarChart is that correct? or have I missed something?

JohnW

 

[GraH]

John, it's a clustered, stacked column chart. Google it, and you'll quickly find a tutorial by Jon Peltier, Excel MVP, data visualization expert and member here.

 

[ejwjohn]

Hmmm...

Ok, I need to do some reading and I will get back to you thanks....

JohnW

 

[ejwjohn]

OK, getting on with the tutorial, but i have come to a point and cannot proceed.

There is a para, that starts as:-
"....Reduce the gap between columns/bars to give the chart a clustered appearance: select one series of columns, press Ctrl+1 ...."

I am on a Mac and I am not sure if this is an issue, but how do I select One Series of Columns? if I do it in the excel sheet I cannot get the correct screen to be displayed and within the chart, there is no way I can select just a portion of the chart?

Help?

JohnW

 

[GraH]

Oh, I'm not at all familiar with Mac. CTRL + 1 simply opens the format options. Does that help?
That tutorial has already some years on the counter and deals with a lot of situations to ensure the chart appears as it should and everything is explained in great detail too. For yours, I can offer a shortcut (I hope). If you mimic the data layout and you insert a stacked column chart changes are it will show up as I posted.
They key element is that the second stacked column is shifted by a column. In my example I also use a double Axis label.

 

[ejwjohn]

Hello,

Can you confirm, should i be selecting "1 series of Cols" within the chart or in Excel?

And if it is in the Chart how do you do that?

Thx
JohnW

 

[ejwjohn]

OK, GUYS SORTED MY ISSUES I THINK WILL UPDATE YOU WHEN I AM 100% COMPLETE?

THX

JOHNW

 

[GraH]

Cool. I only see your earlier question just now.
On the chart select one of the vertical bars to select the series, as you seem to have figured it out on your own.