Today’s tip is an introduction to Pivot tables and Pivot table summaries.

Pivot tables are an amazing way to summarise data. They are extremely versatile, and can provide many types of summary seamlessly. They can save hours of trying to write formulae to create summaries.

Lets look at some example data, and then see how it can be summarised in a pivot table.

Here’s our data:

This example data represents spending by several people over several years. The white cells contain data that we have entered. The grey cells contain “Helper columns” The Helper columns are extra columns that we have added in order to be able to summarise the data by month and year, but you can add whatever helper columns you want. If you want to get a daily summary of expenditure, add a helper column with the formula =DAY(A2) in the first cell... then copy the formula down.

Now, lets look at the kind of summaries we can make with a pivot table. First, take a look at my first tip on Pivot tables so you understand how to construct one. If you can't remember how to add a pivot table, follow this Pivot Table refresher. Now, you can start to create summaries.

Summary 1: Yearly Summary.

Summary 2: Monthly Summary.

Summary 3: Necessary spending.

Summary 4: Total Spending Per Person.

Summary 5: Yearly Spending Per Person.

Summary 6: Average Yearly Spending Per Person.

... we can go on and on... we could even create a summary of all the times that the average yearly spending has been above a certain number so we can tell off the culprits.

You can also create pivot tables of pivot tables of pivot tables and so on !

Download sheet to practise Pivot table summaries

Training Video on Pivot Tables for Dummies: