Our long Excel export is built to enable you to easily create pivot tables in Excel. Pivot tables are a simple way for you aggregate and summarise a data set, producing a summary table to display results that have been filtered and enabling comparisons between variables in your data.
How to create a pivot table within your Excel export
Start by creating an empty pivot table
Once you've downloaded your Excel export from your results dashboard, navigate to the Master Data sheet.
Select the whole data set by clicking on cell A1 then CTRL+A (Windows) or CMD+A (Mac).
Click the Insert tab in the top ribbon and select Pivot Table.
4. A window will appear including the range of cells you selected in step 2. Click OK.
5. A new sheet will be created containing the empty pivot table. If the PivotTable Fields sidebar has not appeared click the pivot table within the sheet.
We now have an empty pivot table into which we can enter the survey data.
Building a pivot table to summarise your survey results
Working in the PivotTable Fields sidebar, the Field Names list at the top refers to the columns in our Master Data tab. We build the pivot table by dragging these field names into the four areas: Filters, Columns, Rows and Values.
To create a pivot table that simply gives the number of answers to each question we start by dragging the following items from Field Name into the Rows area: Question #, Question, Subject and Response
Drag the Anon. ID field into the Values area. Anonymous ID identifies when multiple answers have been given by a single respondent, and so this step aggregates the results by counting the number of Anonymous IDs which gave each answer - i.e. how many respondents gave that answer.
We can now see each question grouped with its responses, plus a column which counts the number of respondents who gave that answer, summarising the full survey results.
Filtering the pivot table to look at specific data
We can filter the data shown in the pivot table by dragging an item in the Field Name into the Filters area. This generates a drop down filter at the top of the pivot table from which you can filter in or out any items (answers) in the corresponding column in the Master Data sheet.
One key use here will be to filter out of the pivot table respondents who did not pass your qualifying questions. To do this:
Drag the Qualified Field Name into the Filters area.
De-select disqualified from the drop down box in cell B1.
This step will refine the contents of your pivot table, to focus your attention on the respondents or questions you're most interested in.
Using the pivot table to compare columns
You can now start to build out your pivot table to allow you to compare how different demographic responded to the same questions.
Drag your first demographic Field Name into the Columns area. This will automatically update the pivot table to display the the results split by every item (answer) in the corresponding column in the Master Data sheet.
2. We can also apply a second (etc) demographic to group demographic profiles together and form wider segments. For instance, you may want to now see home regions further broken down by gender. To do this simply drag the Gender Field Name into the Columns area too.
The pivot table will automatically generate lots of Subtotal columns which may be too noisy in your table. You can remove those by navigating to the Design tab on the top ribbon and in the Subtotals menu clicking Don't Show Subtotals.
If you're interested in adding custom columns to your Master Data sheet in order to compare more specific demographic segments you can find out how to do that here. Custom columns can then be treated like any other demographic profile and dragged from the Field Names area into the Columns area.
For more help creating pivot tables, don't hesitate to reach out to our team via the in-platform live chat.