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 accurate summaries.

Row-and-Column Format

You can perform some Excel tasks on data that is scattered here and there throughout a worksheet, but building a PivotTable is not one of them. To create a PivotTable, your data must be organized in a basic row-and-column format, where each column represents a particular aspect of the data, and each row represents an example of the data. For example, in a parts table, you might have columns for the part name, part number, and cost, and each row would display the name, number, and cost for an individual part.

Unique Column Headings

The first row in your data must contain the headings that identify each column. Excel uses these headings to generate the PivotTable field names, so the headings must be unique and they must reside in a single cell.

Incorporate Labels as Columns

Many worksheets use labels — cells that contain descriptive text — as headings to differentiate one section of the worksheet from another. For example, a parts table might have separate sections for each warehouse, and labels such as "East Warehouse" and "West Warehouse" off the side of or above the appropriate section. Unfortunately, this setup prevents you from using the warehouse data as part of the PivotTable — in the page field, for example. To fix this, create a new column with a unique heading, such as "Warehouse," and copy the label value to each row in the section.

Prepare Your Data

To get your data ready for PivotTable analysis, you may also need to run through a few more preparatory chores, including deleting blank rows, ensuring the data is consistent and accurate, and turning off subtotals and the AutoFilter feature.

Ensure Accurate Data

One of the most important concepts in data analysis is that your results are only as accurate as your data. This is sometimes referred to, whimsically, as GIGO: Garbage In, Garbage Out. PivotTables are no exception: You can be sure that the summaries displayed in the report are accurate only if you are sure that the values used in the data field column are accurate. This applies to the other PivotTable fields, as well. For example, if you have a column that is supposed to contain just a certain set of values — for example, North, South, East, and West — you need to check the column to make sure there are no typos or extraneous data items.

Turn Off Automatic Subtotals

Excel PivotTables are designed to provide you with numeric summaries of your data: sums, counts, averages, and so on. Therefore, you do not need to use Excel's Automatic Subtotals feature within your data. In fact, Excel will not create a PivotTable from worksheet data that has subtotals displayed. Therefore, you should remove all subtotals from your data. Click inside the data, click DataSubtotal, and then click Remove All.

Delete Blank Rows

It is common to include one or more blank rows within a worksheet to space out the data and to separate different sections of the data. This may make the data easier to read, but it can cause problems when you build your PivotTable because Excel includes the blank rows in the PivotTable report. To avoid this, run through your data and delete any blank rows.

Ensure Consistent Data

It is important that each column contains consistent data. First, ensure that each column contains the same kind of data. For example, if the column is supposed to hold part numbers, make sure it does not contain part names, costs, or anything other than part numbers. Second, ensure that each column uses a consistent data type. For example, in a column of part names, be sure each value is text; in a column of costs, make sure each value is numeric.

Turn Off AutoFilter

If you want to use only a subset of the worksheet data in your PivotTable, do not use Excel's AutoFilter feature. If you do, Excel will still use some or all the hidden rows in the PivotTable report, so your results will not be accurate. Instead, you need to use Excel's Advanced Filter feature and have the results copied to a different worksheet location. You can then use the copied data as the source for your PivotTable report.

Use Repeated Data

The power of the PivotTable lies in its ability to summarize huge amounts of data. That summarization occurs when Excel detects the unique values in a field, groups the records together based on those unique values, and then calculates the total (or whatever) of the values in a particular field. For this to work, at least one field must contain repeated data, preferably a relatively small number of repeated items.