Matrix questions - create a stacked graph of responses in Excel

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 

Likert Scale example - Leisure matrix question.

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.

Chart and table of results for a matrix row 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.

Excel stacked graph for the Question please share how you feel about the following statements. Each statement set as a row (example: The police make my community a better place to live.), and then a portion of the row is based on the different answer options, in this case a Likert scale from Strongly Agree to Strongly Disagree showing the number of respondents per answer option (using a scale from 0 to 25).
A stacked bar graph in Excel with each statement set as a row, and then a portion of the row is based on the different answer options.

How to create a stacked graph in Excel

The first stage is to get your data in correctly, and thankfully this is a simple copy and paste job from a downloaded .xlsx file.

How you need to organise your data in Excel

Example table of how to organise the matrix data.

  1. Get your scale of options in as row headers on Column A.
  2. Put your statements in a single row as column headers, starting with Column B.

Now we'll get Excel to do the work for you

Make Excel turn that into a chart

  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. Go to Insert Tab > Charts > See all Charts > Select and insert the desired chart.
  3. In this case, go to Bar chart > Stacked Bar.

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

Screenshot of Excel with the data highlighted and inserted stacked bar graph. Once inserted, you can refine how the chart looks, the axis labels, the colours using the Chart Design tools.

You may need to refine the chart — see more detailed guidance from Acuity Training on how to create stacked bar and column charts.

And that's it - you now have a stacked chart of your matrix question data.