Step-by-Step Guides 3:
Pivot Tables
Pivot Tables: Overview
Pivot tables allow us to easily summarise our data into a contingency table. This type of table is a matrix that displays the frequency distribution of the variables you select.
Below we can see a contingency table that shows us the average salary by Ethnicity and Martial Status
Note: The variable you select for your row and columns must be categorical (ordinal or nominal).
These tables should be relatively easy to interpret. As you can see from the image above. The value of interest will be at the intersection of the column and row.
In the case above, we are interested what the average gross income for Pakistanis who are Married or living with their spouse is (£13.14 per hour). This way we can easily compare values between different groups.
You can also easily add layers (essentially grouping your data) to the rows and/or columns. Though we would suggest using them wisely - probably no more than 1. The table in the image below now gives you information not only about Ethnicity and Marriage or living with spouse status but also about Gender in relation to Ethnicity. You can now look up what the difference in gross hour play is for a White male and a female.
Note, in the example below the layer refers to ethnicity. So it tells you nothing about the difference in pay for Marriage Status. If you want this information you would need to add a layer to the column too.
Reminder: Row and Column Percentages:
One of the things you will come across a lot in Pivot tables/contingency tables is Row and column percentages. So here is a quick reminder of how they are interpreted.
Column percentages show the proportion of observations in each row from those in the columns.
Row percentages show the proportion of observations in a column category from among the rows.
Create Pivot Tables
Step-by-step Guide: Create a Pivot Table
Note: Your data should already be arranged as a data table. See Excel data tables for details
Go to the Table Tab > Summarise with Pivot Table
2. In the pop-up box select what data you want to include in your pivot table.
Usually, the default settings are fine. However, you can change this if you wish (e.g., you might not want to include all columns in your Pivot table, or you might want to add the Pivot table to an existing worksheet.
3. Once you have clicked OK. You have created your first Pivot table.
4. You can now select the variables you want to display in the Rows and Columns as well as the values.
5. Now you can drag and drop the variables you want information about into the appropriate fields.
Your table will appear in the Left of the Excel Spreadsheet.
You can find lots of information about how to build and tweak your Pivot Tables on the Mircosoft site.
Step-by-Step Guide: Caluclated Fields and Number Formatting
Pivot tables use certain default settings. The defaults are not always that useful. Calculated Fields and Number formating allow you to manipulate the table output.
For example, if you have selected a continuous variable in your values field, it is usually displayed as a Sum of your data. This is not very helpful as we might want the average, the minimum, etc.
We can change the table output by right-clicking on any value in the Pivot table. We then have a series of options:
Summarise values by ...
Use this option if you want to display your values as Averages, Minimum, Maximum, etc. This will only work if your value field data is continuous data - otherwise, the data displayed will make little sense.
Show values as ...
Use this option if you want to display row and column percentages. This is usually a good way to display categorical and nominal data.
Number Format
Here you can easily change the format of the number, whether that is adding currency details or changing the number of decimals.
Step-by-Step Guide: Filters and Slicers
Use Slicers to filter your data. Slicers are a great way to allow users to interact with a pivot table and easily change the data that is displayed. To add a slicer, go to the "Analyze" tab, select "Slicers", and then choose the field you want to create a slicer for. You can also format the slicer to make it more visually appealing.
Filter by multiple fields: You can also use filters to limit the data displayed in a pivot table based on multiple fields. To do this, simply apply multiple filters, either by using the "Filter" option or by using slicers.
Clear filters: If you want to remove filters and display all the data in your pivot table, you can do this by clicking on a cell in the pivot table, going to the "Analyze" tab, selecting "Fields, Items & Sets", and then "Filter". You can then select "Clear All Filters".
By following these steps, you can easily use filters and slicers in pivot tables to interact with and display your data in the way you want.
Adding Rows/Columns to your Table
Sometimes you may want to add new variable or another row of data. This is easily done. Follow the steps below.
Note: The new Column can us functions to combined the values of other variables. You can learn more about functions in the next step-by-step tutorial
Step-by-Step: Add a new Column or Row
Adding a new column couldn't be easier.
Hover on the column where you want to add a new column
Right-click > Insert
Now decide if you want the new column to the left or right of the existing column.
Name the variable and add data.
Follow the above steps - but select Rows to add an additional Row
Quick guide: From Table to Pivot Table
The video below takes you through how to get your data into a table and pivot table to create some contingency tables.