Excel – How to graph survey results in Excel

chartsmicrosoft excel

I would like to graph my survey results with the numerical values of the responses listed on the vertical axis and the questions listed on the horizontal axis.

For example, I imagine the questions noted on the horizontal axis as Q1, Q2, Q3, Q4, etc. On the vertical axis, I imagine the actual numerical values (responses) noted by a bar graph.

The possible answers use a 5 point Likert scale (very satisfied to very dissatisfied) that have a numerical 1-5 attached to them, "1" equals very dissatisfied.

How can I graph this?

Best Answer

If I understand what you're asking for, you want a graph that shows, for example:

Question "I like ice cream": 40% strongly agree, 30% agree, 10% neutral, 15% disagree, 5% strongly disagree

Question "I like popcorn": [array of Likert scale proportions similar to the above]

Set up a table in Excel with the questions in column A, % strongly agree in column B, % agree in column C, ... % strongly disagree in column F. You may need to do a fair bit of copy/paste/transposing your data to get this table together. You can also use the raw numbers of respondents instead of percentages.

Then select the whole table, and insert a "stacked column" graph.

If your question stubs are long (more than a few words each), you might be better off with a "stacked bar" graph, running the question stubs vertically and the data bars horizontally.

Hope that helps.

Related Solutions

Excel – How to plot survey results on Excel

If the column contains non numeric values (e.g. "no answer") the average doesn't work. Is there a workaround for this?

I assume you're doing an average like this: =AVERAGE(C1:C13). If so, the formula will simply skip that cells that have text in them. So, in your example, you have 13 rows, the average formula will be dividing by 12, rather than 13. You can check this by replacing the "No answer" with a 0:

AVERAGE(C1:13) = 2.75 (has "No Answer")
AVERAGE(C1:13) = 2.54 (has a 0)

So it's up to you whether you want to count the people who didn't answer as zeros, or just not include them in your statistics.

How can I count the yes/no results and plot only a percentage?

This one is easy - you'll use Excel's COUNTIF formula.

In a new set of cells, you'll want to count each yes or no. For your example, I put my yes in K2 and No in K3. You'll want to use the COUNTIF like so:

For the Yes row (K2): =COUNTIF(A:A, "Yes")
For the No row  (K3): =COUNTIF(A:A, "No")

Then just use a pie chart referencing the countif cells, and it should look like this:

pie chart

Excel: Charting date, time elapsed, and another value

As far as I know the function box shows the time in the system format. Why is it important how the function box display the time? It only means excel detect it is in the datetime format (usually a good thing). From there you can customize how the cells display that datetime (this is where you choose the correct format

If you want the time displayed as 14:04:05 in your cells (hence, in your graphic) you can use this custom format: "hh:mm:ss" or pick one of the predefined datetime format.

If you want to extract the numbers to do calculation with them you can use the following formulas: hour(yourdatetimecell) minute(yourdatetimecell) second(yourdatetimecell)

EDIT : You can work your way with converting your datetime into a sum of minute or a sum of seconds and graph it just like any other number. + Using the scatter graph format I have no issue creating a graph of duration expressed as hh:mm:ss (even if theres an am/pm or even a date showing up in the function bar)

If I'm to chart the following making sure to use two different Y axis for the value of the second and 3rd columns, scatter plot works fine.

11/12/2012  10  4:55:08
12/12/2012  10  3:55:08
1/12/2013   11  4:55:08
2/12/2013   12  7:55:08
3/12/2013   11  3:55:08
4/12/2013   10  7:55:08
5/12/2013   9   6:55:08
6/12/2013   12  6:55:08
7/12/2013   15  14:06:38
8/12/2013   16  48:05:39

Best Answer

Related Solutions

Excel – How to plot survey results on Excel

Excel: Charting date, time elapsed, and another value

Related Question