Pivot Table Survey Exercise

1. Open up the COVID Relationship Excel spreadsheet in Google Spreadsheets. File > Open

2. Click in the box above the 1 row and before the A column to select all the data.

3. Go to Insert > Pivot Table. Leave it at New Sheet and click on Create.

4. Select the attitudinal question you wish to measure (These are the ones that begin “Please rate your response to the question.”

5. Select the measures or factors you want to compare, age, sex, employment status, etc.

6. Click on the Row, “Add” in the Pivot Table editor. Go to the attitudinal question and select it.

7. Next go to the Column, “Add” in the Pivot Table editor. Select the factor — age, sex, etc. — you wish to compare.

8. Next go the Values, “Add,” in the Pivot Table editor. Select the same factor.

9. Review your chart and do a common sense check to confirm you have done the analysis correctly.

10. Copy the row where the Grand Total is. Go to the bottom of your spreadsheet and then go to Edit > Paste Special > Values. We will use this row in a minute.

11. In order to compare between factors, we need to convert the data into percentages — this is because we can’t make a comparison of responses between say, men and women, if we don’t first take into consideration how many of each responded to the survey. To do this to to the Values in the Pivot Table editor. The default is likely COUNTA. Instead, use the pulldown menu to select “% of column.”

12. Next we need to extract the data from the Pivot Table and paste it below the original or in a new sheet if you wish. Select the data in the table, Command C or Control C on PCs to copy.

13. Position your cursor where you want to paste and then go to Edit > Paste Special > Values only.

14. Next select Column A and then go to the wrap icon — it looks like a rounded arrow between two lines — and wrap all the text.

15. You can delete the question for your factor, eg. How old are you? This will be self explanatory.

16. Position your cursor between the A and B columns and stretch out column A so the attitudinal question doesn’t distort the chart height.

17. Select all the columns with data and use the align tool to center it to make it more legible.

18. The numbers may be converted into decimal places, to change this, select the data and go up to the tool bar and select %. If the percentages go to the hundredths, select the data and to the .0 with a decreasing arrow.

19. Delete any empty rows or rows with no clear significance.