How to Analyze Survey Data Part 3 - Summarize with Pivot Tables and Charts
Вставка
- Опубліковано 23 лип 2024
- Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
Checkout the full article and download the file at: www.excelcampus.com/pivot-tabl...
In this third part of the series we learn how to use Pivot Tables and formulas to analyze the multiple choice survey data. We also create a chart that shows the percentage of total responses for each item (choice) in the survey question.
Please subscribe to my free email newsletter to get more Excel tips and tutorials like this. www.excelcampus.com/newsletter
PART 2: • How to Analyze Survey ...
PART 3: • How to Analyze Survey ...
00:00 Introduction
00:41 Pivot Tab les
05:42 Conclusion - Навчання та стиль
Thanks so much very helpful tutorial video helped me a lot in analyzing data with multiple answers. Many thanks for sharing your knowledge
I always like your videos and thank you for sharing your knowledge.
Thank you so much, Jon! You have taught me so much with your tutorials. I liked and subscribed!
This is great, thank you.
loved it,helpful and grateful.
Very useful survey tutorial series - thanks Jon.
+Tristan Quintus Thanks Tristan!
Nice easy illustration. Thanks heaps
really helpful!!!! Thanks Jon
Really helpful video. Thank you.
Thank you for this useful video, Jon. What I'm looking for, though, is how to analyse ranked data from a customer survey - ie where, say, 20 customers put in ranked order their preferences about a certain product type. So I'd want to see how many customers ranked a certain option as 1st, 2nd, 3rd, 4th 5th. Using averages doesn't help because if some really liked a feature and others hated it, the average rank would be 3rd! So, I'm hoping to use the skills outlined in this video, to create something that shows more than just one choice (eg which drink with your breakfast), but has how many people ranked which feature in which place.
Amazing video thanks again
thanks this is very useful
Thank you Jon
Great for excel business intelligence (statistics & visualisation made simple)
Thank you - this was very helpful. Is there a way to do this for more than one column at the same time?
We do lots of data collection from surveys, would love to understand if you can teach me crosstabs as well
THANKS. YOU SAVED MY JOB :D
Glad to hear that! 😀
Do you have a video to show how to join multiple pivot tables? I'm having a hard time with this. I did a large survey that had a few fields with multiple check box selections and so now I need a way to easily analyze this data through pivot tables so I can make correlations to define my buyer personas.
Very helpful. I would love to see how you transform a survey monkey excel export document into a useable table. It is not easy to compare across surveys.
well done!
Awesome!
great videos ! How long should we waiting for the next videos ?
Thank You
Hi! Thank you very much for this video. However I didn't get the f4 thing, had to calculate separately for result. Do you have avideo where you show how to analyze questions with multiple answers?
Question: in my case I would need to show how much % of the age range chose the particular response, its also a multiple response question. How can i do that? Please help
very useful videos
+Yao Louis-Hervé DIBI Thanks Yao!
Thanks Jon, another great video tutorial. How could I analyse a survey with for example 20 questions (the export places each question in a new column), and the answer for each entry (row) is either "Yes / No / Not Applicable"... Please can you help me out?
Hi Gaz, Great question! You would still want to normalize this data to get it into one long list with 3 columns (Name, Question, Answer). There might be additional columns with other information about the person taking the survey, but you really only need those columns.
You can use Power Query to unpivot the data to get it in that format. You can then use a pivot table to count the number of each reponse by question. It's the same basic process as this series, just a different starting point. I hope that helps.
Thank Jon, I am currently in the same situation now. I have 103 respondent and 23 questions. I want to summarize base on questions i.e., trying to see answer based on two questions for example, But after I unpivot into three columns (ID, Questions, & Answer) I couldn't analyze even after I was able to select multiple entries across questions. Please help me out excel campus Jon
what type/format of excel did u used??
Is there a way to compute the percentages with base = 20 automatically within the pivot table? I often do a lot of segmenting and subsetting the data (like gender, age), and it's cumbersome to compute totals for each subset over and over.
This is exactly what I'm looking for. Did you ever find a solution?
Also, my data is in a different format, because the survey was multiple answer, i.e. the respondents could choose more than one option for each question.
Great video but in the Excel Pivot Tables playlist part 1 should probably come first.
Many thanks. However, Jon, I think in this video, when you calculate for the percentage, you should use the number of GrandTotal (43) instead of 20. Is it ?
Hi Crit, yes that calculation might be a little confusing. I think it should show the number of responses instead of the percentage of the total. Thanks!
If there are 20 respondents and the question was "choose all that apply", then Jon is correct. The total of those percentages should NOT = 100%, as participants were allowed to select more than 1 option. 43 would have been applicable if the question was "select one" and there were actually 43 participants.
What about if you have multiple questions with multiple, multiple choice answers
What if instead of "Entry ID" we have "Department name" and we want to SEE along with department name?
Thank you very much for these videos, but please... a little bit slower... I have to pause every few seconds to replay to see what you did :)
Hi Susanne, sorry about that. You may change your playback speed under the settings of the video. Hope that helps! 😀
Those percentages total up to 215%.. Can you explain why you chose not to divide by the 43?
I believe it is because dividing by 20 results in the percentage of people who would drink juice 12/20 respondents = 60%. If it was to be divided by 43, that would indicate 12/43 total responses were juice, but keep in mind people could have chose multiple options so that's why the total % you see is above 100%.
excuse me sir, B4/B$9$ ???? 100 %