How to create a table from survey data in Excel | Questionnaires & pivot tables for beginners
1) You have raw data in an Excel sheet.
2) Click once anywhere inside the data.
3) Select the tab Insert and click the button Pivot table.
4) I recommend the option New worksheet.
5) Drag your most important variable into the Values box.
6a) You might have to change the type of value.
6b) Right-click on the value.
6c) If need be, drag down to Summarize Values By and change it.
7) Next, drag the same or another variable into another area (box).
***
If you do not see the Fields and boxes, click one inside the table.
***
One way to proceed is to remove the variables and start all over.
Another way is to click, drag, copy, and paste the table.
To keep the first table, you must remove the old variables.
***
Remember. The raw data are never altered. Pivot tables are non-intrusive.
All we are doing is experimenting. Analyzing.
***
The most important box is Values.
I start by dragging my main variable into that box.
In my video, I investigate the attitude to ice cream.
***
After dragging it to the Values box, I get some summary.
It shows the summary of all replies to that survey question.
However, we do not want the count of. We want the mean value.
Just right-click on the number.
Drag down to Summarize Values By, and select Average.
This is the mean value for all participants answering this survey question.
But it is just the beginning.
This is where Pivot tables shine.
I will now break it down by some other variable.
Let's take the Educational level.
Congratulation!
You have created a table from survey data.
***
I know that you think that the Pivot table function seems complicated.
But that is what everyone thinks. You are not alone.
Keep practicing!
Remember, Pivot tables do not alter the raw data.
It is just a tool. Analyzing data is fun!
You will become an extra valuable employee with your new Excel skills.
***
There is no right or wrong here.
It is all about testing, exploring, and solving problems.
***
Copyright
Text, video, and audio © Kent Löfgren, Sweden
