1. Basics of pivot tables
1.1. You can build a pivot table in about one minute
Building a pivot table is much simpler than it used to be and compared to the time it would take you to build the same report manually, pivot tables are incredibly fast. If you have well-organized source data, you can create a pivot table less than a minute. Here’s how:
- Select the source data
- On the Insert tab of the ribbon, click the PivotTable button
- In the Create PivotTable dialog box, check the data and click OK
- Drag a “label” field into the Row Labels area (e.g. customer)
- Drag a numeric field into the Values area (e.g. sales)
1.2. Perfect your source data
Starting with good quality data makes things much easier later on. To minimize problems with your pivot tables, always use good quality source data, organized in a tabular layout. Perfect data will have the following attributes:
- No blank rows or columns, and no subtotals. If fact if you have totally blank columns you cannot create a pivot table (there must at least be a heading).
- Each column will have a unique name (on one row only),
- Every field (columns = fields) will have a value in every row, and
- Columns will not hold repeated groups of data (i.e. month names, location names, region names, etc.) unless absolutely necessary.
1.3. Plan before you build
Although it’s a lot of fun dragging fields around a pivot table, and watching Excel produce yet another way of looking at your data, you can suddenly find that you have spent quite a lot of time doing this only to realise that one of the first ways you tried gave the way to understand your data.
Before you start building a pivot table, jot down what you are trying to measure or understand, and sketch out a few simple reports on a notepad. These simple notes will help guide you through the huge number of choices you have at your disposal.
Above all keep things simple, and focus on the questions you need to answer.
Excel Monkey 