60. Excel Tips - Pivot Tables for Dummies

Nick's picture



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:

pivot-tables-for-dummies

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.

pivot-tables-for-dummies

Summary 2: Monthly Summary.

pivot-tables-for-dummies

Summary 3: Necessary spending.

pivot-tables-for-dummies

Summary 4: Total Spending Per Person.

pivot-tables-for-dummies

Summary 5: Yearly Spending Per Person.

pivot-tables-for-dummies

Summary 6: Average Yearly Spending Per Person.

pivot-tables-for-dummies

... 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:

AttachmentSize
pivot-tables-for-dummies.xls48 KB

Pivot Table

How do you make a list in a pivot table?

Example:

2008 Nick 2
Sam 2
Sue 4
2009 Sue 5
Nick 6

I want 2008 to show on every row and 2009 to show on every row.

Nick's picture

pivot table

I've not found a way to do this apart from using VBA