Indicating the minimum and maximum on column charts

[Mark Winter]

I am a management consultant with basic Excel 2003 experience.

I want to create a dashboard for a client, which is going to be a column
graph displaying the average scores (max score of 5) for 6 questions. The
questions are assessing a relationship between two teams.

I want to add in an indicator to show the maximum score and the minimum
score for each question - I would call this the range, but I know Excel has
its own terminology. Can this be done? It would allow me to show if the
answers are polarised e.g: some people are very happy, some are very unhappy.

I have spent much time reading and searching, I know this is basic but the
help would be very appreciated!

 

[Andy Pope]

Hi,

Assuming data is laid out in range A16, where A2:A6 contains question
text, B2:B6 average scores.
C2:C6 Max scores and D26 Min scores. B11 contain series names.

Create a clustered column chart on the range A16
Select the Average scores series and move to the secondary axis. Format >
Axis > Secondary.
Delete the secondary Y axis so the series uses the main Y axis for it's
scaling.
Select the Max Score series and format overlap and gap width to 100. Format

Series Options > ...

Select the Min Score series and format the border to none and the Pattern to
that of the plot area. Format > Patterns > ...

You should now have an area behind each average score column showing the min
to max score.

You might want to change the Average scores chart type to a line chart with
Markers Only.

Cheers
Andy

 

[Mark Winter]

Thank you Andy, easy to follow instructions and solved my problem....much
obliged!

 

[Mark Winter]

Actually, follow up question...

Could I do this for each individual question, over say 3 months?

Example: The question is "Do they treat us an equal partner?"
Month 1: 5 Respondents, Max score of 5, Min score of 2, Average Score of 3.6.

Can I chart months 2 and 3 in the same chart? The idea would be to show
(hopefully) positive trends as the months progress.

The reason I want to chart each individual question is to contrast technical
questions (e.g: Are they delivering on time?) versus relationship questions
(e.g: treat us an equal?)

Thanks once again (I hope)

Mark

 

[Andy Pope]

If you add additional rows of data for each month you should be able to do
this.
Also add another column of labels in order to group Months with Questions.

Put the question text in A2,A5,A8,A11 and A14
In B2:B16 repeat Month 1, Month 2, Month 3
In C2:C16 scores for each question and month
In D216 maximum score for each question repeated across months
In E2:E16 minimum score for each question repeated across months

Now chart A1:E16 and format as per previous instructions.
I think now you really should have the Average score as a line.
In order to break the line between questions insert blank rows above and
below each question.
You will need to adjust each series to use the rows 2:26. And move the
Question text up in order for the axis labels to appear correct.

Cheers
Andy