Excel Cookbook

Chapter 11. PivotTables

PivotTables are one of Excel’s most powerful features because they let you interactively analyze, summarize, and explore large amounts of data with just a few mouse clicks. You can slice and dice data in many ways, apply various summaries, and insert custom calculations when you want to go beyond Excel’s built-in aggregations.

This chapter contains a collection of recipes designed to help you get the most out of PivotTables and includes the following areas:

• Summarizing data using different aggregations

• Displaying values as percentages, running totals, and more

• Changing a PivotTable’s default layout

• Filtering data using slicers and timelines

• Grouping date/time and number fields and manually defining ad hoc groups

• Using calculated fields and items to insert custom formulas

• Using the PivotTable cache to control whether to share groups, calculations, and filters and reinstate deleted tables without using a backup

11.1 Organizing Data for PivotTables

Problem

You want to organize data so you can use it to create a PivotTable.

Solution

Suppose you have a dataset that you want to be able to summarize using a PivotTable. In this situation, the data should have the following structure:

• Unique column headings in the first row, where each column is a unique category (for example, Product, Month, and Amount).

• Each row is a separate record or data item (for example, a sales transaction).

• You should also convert the data to a table because this ...