Need help? Looking for tips and tricks?

This knowledge base contains loads of useful advice and answers to common questions.

If you're still stuck you can always submit a support request and we'll get back to you ASAP.

Matrix questions - how to create a stacked graph of responses in Excel

Louise Cato -

We have been asked about how to display the responses to a matrix question in a stacked graph, and the following article explains how to go about this using Excel.

To start, you may have a matrix question like the below. This particular one uses a Likert scale (Strongly agree to Strongly disagree), to gauge respondent's feelings 

 

If you were to look at the responses to this question in the 'responses organised by question' section of your dashboard, you will see that Citizen Space provides each statement with its own chart of responses. See an example of two statements below:

image of responses to the leisure question with individual charts for each statement

 

What you might now want is to be able to create a single graph for this question with all statements and the various answer options stacked against one another. I've created this in Excel and put the image below so you can see what this would look like.

image of Excel created chart of data

 

I'll now step you through the process I took to create it.

Helpfully, Excel has a chart creator which is so simple to use that even I can do it. The first stage is to get your data in correctly, and thankfully this is a simple copy and paste job.

 

Here's how you need to put your data into Excel

image of data set in Excel, laid out so this can be turned into a stacked chart

1. Get your scale of options in as column headers on row 1, starting from column B

2. Put your statements in Column A as row headers, starting from row 2

3. Then put in the figures for each option - this is just a simple copy job from the charts in Citizen Space. e.g. For 'The facilities are easy to get to' I just typed the response numbers in the corresponding column, 3 respondents strongly agreed, 2 agreed etc.

That's your data put in correctly, now to get Excel to do the work for you

 

Here's how to make Excel turn that into a chart

I'm using Excel for Mac version 14.4.7, so I've taken screenshots from that - your version of Excel may be different, but hopefully it will have the same or similar features. 

1. Highlight/select your data as if you were going to copy it. This tells Excel what data you want to make a chart from.

2. On your Excel toolbar you are likely to have an option which says 'Charts' or 'Chart wizard', or it may be in the 'Insert' menu or simply an image of a column chart. Whatever you have that does charts, click on it.
My version looks like this:

image of the charts option in Excel

3. You then want to choose the type of chart you want it to make. I want a stacked column chart, so I'm going to click on 'Column' and then choose 'Stacked Column'

4. Boom! This should have created a chart from your data.

chart created from data with x and y axis plus coloured key

BUT for some reason it's put the options along the horizontal x-axis and my statements as the different colours. Oh :(

No worries though, it's a simple click of a button to switch these over!

5. First click on your chart and then find your switch button - my one looks like the one circled below, your version of Excel might require you to click on 'Design' and then a button that says 'Switch row/column' - if not, your Excel help will tell you.

image of the switch plot button in Excel for Mac

And that's it - you now have a stacked chart of your matrix question data. If you try this in Excel yourself, the first time it may take about five or ten minutes to find your feet. After that, you'll have these done and ready for your reports in next to no time.

 

Some helpful links I found for other versions of Excel and general guidance

The Microsoft Office site has some guidance on creating column charts in Excel

This is a great video for creating stacked charts in Excel, there are some parts of it that can be ignored, but may be helpful if you want to be a bit more fancy.

Powered by Zendesk