How to Analyze Survey Data Part 3 - Summarize with Pivot Tables and Charts

[@ianb4916]

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.

[@alexatheoharides6957]

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.

[@miriambonilla6407]

I always like your videos and thank you for sharing your knowledge.

[@waltonvira3008]

Nice easy illustration. Thanks heaps

[@paulanderson5078]

Really helpful video. Thank you.

[@jovannienralin4578]

THANKS. YOU SAVED MY JOB :D

[@ExcelCampus]

Glad to hear that! 😀

[@sallydiaz1111]

Thank you so much, Jon! You have taught me so much with your tutorials. I liked and subscribed!

[@kafetzou]

Thank you - this was very helpful. Is there a way to do this for more than one column at the same time?

[@TheGoldDiggerPodcast]

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.

[@tristanquintus3200]

Very useful survey tutorial series - thanks Jon.

[@ExcelCampus]

+Tristan Quintus Thanks Tristan!

[@sajalbaxi4270]

really helpful!!!! Thanks Jon

[@maxfueloon2006]

Great for excel business intelligence (statistics & visualisation made simple)

[@niyilivesinLondon-bm6ds]

Thanks so much very helpful tutorial video helped me a lot in analyzing data with multiple answers. Many thanks for sharing your knowledge

[@ibrahimsani9718]

loved it,helpful and grateful.

[@asmitaaryan]

We do lots of data collection from surveys, would love to understand if you can teach me crosstabs as well

[@jobaerrahman4113]

Thank You

[@adadmohamedcherif3697]

Thank you Jon

[@SuperChica06]

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

[@dilbarzokirova3026]

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?

[@smorisch]

This is great, thank you.

[@susannewalter3989]

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 :)

[@ExcelCampus]

Hi Susanne, sorry about that. You may change your playback speed under the settings of the video. Hope that helps! 😀

[@findthetruth3021]

Amazing video thanks again

[@kafetzou]

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.

[@osman9750]

Great video but in the Excel Pivot Tables playlist part 1 should probably come first.

[@vadardosky]

thanks this is very useful

[@yaolouis-hervedibi7296]

great videos ! How long should we waiting for the next videos ?

[@gazbathdard]

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?

[@ExcelCampus]

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.

[@babatundekozeem1649]

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

[@YanisKyr]

Those percentages total up to 215%.. Can you explain why you chose not to divide by the 43?

[@windycitytwins]

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%.

[@birdsong2552]

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.

[@ScissorPaperRoc]

This is exactly what I'm looking for. Did you ever find a solution?

[@97mollylawrence]

What about if you have multiple questions with multiple, multiple choice answers

[@stevennye5075]

well done!

[@jayesoteric9106]

I got the result from google form and then into Excel form raw...how do i go from there ? I need help

[@critviet6971]

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 ?

[@ExcelCampus]

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!

[@Mojomatazz]

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.

[@clintonphilip505]

Awesome!

[@nyliram2009]

what type/format of excel did u used??

[@yaolouis-hervedibi7296]

very useful videos

[@ExcelCampus]

+Yao Louis-Hervé DIBI Thanks Yao!

[@asksimon9813]

What if instead of "Entry ID" we have "Department name" and we want to SEE along with department name?

[@wilsonsantiago39]

excuse me sir, B4/B$9$ ???? 100 %