Excel® 2007 PivotTables and PivotCharts

Chapter 2. Building A PivotTable

Prepare Your Worksheet Data

The most common method for building a PivotTable is to use data that exists in an Excel worksheet. You can make this task much easier by taking a few minutes to prepare your worksheet data for use in the PivotTable. Ensuring your data is properly prepared will also ensure that your PivotTable contains accurate and complete summaries of the data.

Preparing your worksheet data for use in a PivotTable is not difficult or time‐consuming. At a minimum, you must ensure that the data is organized in a row‐and‐column format, with unique headings at the top of each column and accurate and consistent data — all numbers or all text — within each column. You also need to remove blank rows, turn off automatic subtotals, and format the data. In some cases, you may also need to add range names to the data, filter the data, and restructure the data so that worksheet labels appear within a column in the data. You may not need to perform all or even any of these tasks, but you should always ensure that your data is set up according to the guidelines you learn about in this section.

Organize Your Data

In the simplest case, Excel builds a PivotTable from worksheet data by finding the unique values in a specific column of data and summarizing — summing or counting — that data based on those unique values. For this to work properly, you need to ensure that your data is organized in such a way that Excel can find those unique values and compute ...