I recently did a survey, which had upto 10 questions. Now I need to create a graph out of the values, but am stuck. The data is below:
Customer ID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10
1797493605 Yes Yes Yes Yes Compare totals Yes Yes Yes Yes None of the above
1797155787 Yes Yes Yes Yes Compare prices Yes Yes No Yes None of the above
1797116920 Yes Yes Yes Yes Compare totals No No Yes No Catalogues
1797105343 Yes Yes Yes Yes Compare prices Yes Yes Yes Yes None of the above
1797076252 Yes Yes Yes Yes Same places Yes Yes Yes No None of the above
1797015113 No No No No Everything online No No No No None of the above
1796959310 Yes Yes Yes Yes Compare prices Yes No No Yes None of the above
1796950913 Yes Yes Yes Yes Compare prices Yes Yes Yes Yes Catalogues
1796931846 Yes Yes No Yes Compare prices Yes No Yes Yes Email/SMS
I tried using the Excel normal functionality, but it just does not provide the correct graph.
would like to get a Bar Graph, with each question having 2 bars - for each answer. With the X-axis having the questions and the Y-axis the number of people who answered?
Can the data values then should be the actual Yes, No, ? If so, how?
If I understand correctly, you don't care about customer ID.
What you're trying to get is a grid of this format, where row 1 is your heading (I'm fabricating the numbers here):
| | A | B | C |
===========================
| 1 | Q | #Y | #N |
| 2 | Q1 | 6 | 1 |
| 3 | Q2 | 5 | 2 |
| 4 | Q3 | 2 | 5 |
And in order to populate cell B2 (which holds the # of YES answers to Q1, you would use a formula like this one against your grid:
=SUMIF($B$2:$B$10,"YES")
That SUMIF() formula counts the number of "YES" responses in the Q1 column of your grid.
(I'm assuming that in YOUR grid "Q1" responses are contained in Cells B2 thru B10.)
Build that table, and then you'll be able to highlight that table, click into the Excel Chart Wizard, and manipulate it into the bar chart you're seeking.
If that's not your answer, you will need to be more specific about what you're trying to achieve.