4.4 PivotTable
04-Oct-2022
3 mins
The PivotTable is used to summarise data in a database that lets you see comparisons, patterns and trends in your data.
Let’s for instance take the database below. To add a PivotTable, select the database (with headers) and click PivotTable from the Insert tab:
To insert the PivotTable into a new worksheet in the workbook, leave the default values and validate:
An empty PivotTable is then created:
You can then format your PivotTable and select the variables you want to appear in the table. To do this, use the PivotTable Fields window to create the PivotTable by choosing which fields to include. The result will appear in the report area.
Check the fields that will be taken into account in the PivotTable, for example, all database variables here: Category, province, and number of active health centers.
- The Row Labels area defines the fields that will appear on the left side of the PivotTable.
- The Column Labels area defines the fields that will be displayed at the top of the PivotTable.
- The Filter area of the report corresponds to the Page field in older versions of Excel. It is used to display and filter subsets of data in a PivotTable.
- The Values box contains numeric data synthesized according to the Row Labels, Column Labels, Report Filters, and operations applied to the fields in that box.
By default, the field performs a sum. If you want to change the operation you can access it:
- By clicking on the field name
- By choosing the operation that suits you in the Value Field Settings menu: average, minimum, maximum, count, etc.
Available common operations include:
- SUM: returns the sum of all values in the field
- COUNT: returns the number of records in the field
- AVERAGE: returns the average of all values in this field
- MAX: returns the largest value in the field
- MIN: returns the smallest value in the field
- PRODUCT: multiplies all values in the field
- COUNT NUMBERS: returns the number of digital records in the field
- STDEV: returns the standard deviation of the field
- STDEVP: returns the standard deviation of a population
- VAR returns the variance of the field
- VARP returns the variance of a population
Please note :
- You can insert the same field several times in different areas and several fields in the same area
- In a PivotTable, all filters are additional: each additional filter is based on the active filters and refines the subset of data.
The following exercise is only available on the French page at this stage- translation coming soon!
In order to practice creating and using PivotTables, here is a practical exercise using clean data from the case study - developed by CartONG as part of the Data Analysis Toolbox publication. A more complete presentation of this case study can be found in the “Data Analysis Toolbox” – Part “7 The case study ”.
In this example, we propose to generate PivotTables for:
- The average age of the head of household by district
- The standard deviation of the age of the head of household by district
- Access to food assistance
- Sanitation (access to different types of sanitary facilities)
- Food consumption score (FCS) thresholds by region
- The reasons why some households did not receive food assistance
Use the [“Excel Toolbox - Analysis 4.4 - Practice exercise - V1 - Statement”] file to practice creating PivotTables.
In case of difficulties, or to compare your results with those of the case study, refer to the file [“Excel Toolbox - Analysis 4.4 - Practical exercise - V1 – Solutions”].