Creating a PivotTable

You can create a PivotTable from either an Excel range or an external data source. If you're working from an Excel range, your data should meet the criteria for a well-constructed list. That is, it should have column labels at the top (the headings will become field names in the PivotTable), each column should contain a particular kind of data item, and you should not have any blank rows within the range. If the range includes summary formulas (totals, subtotals, or averages, for example), you should omit them from the PivotTable; the PivotTable will perform its own summary calculations.

For information about connecting to and querying external data sources, see Chapter 23, "Working with External Data."

The source range on your Excel worksheet can be a table (as described in Chapter 21, "Managing Information in Tables") or an ordinary list. Starting from a table has the advantage of allowing for expansion. When you create a PivotTable from a table, Excel references your source data by its table name (either a default name, such as Table1, or the name you assign to the table). If you add rows to a table, the table name automatically adjusts to encompass the new data, and hence your PivotTable stays in sync with the expanded source data.

For information about converting a list to a table, see "Creating a Table" on page 669.

To create a PivotTable, select a single cell within the source data and do either of the following:

• Click the Insert tab, and then click PivotTable (in the Tables group).

• If your source data is a table and you're currently on the Design tab under Table Tools, click Summarize With PivotTable (in the Tools group).

Either way, the Create PivotTable dialog box appears. If your source data has a name (we've assigned the name BookSales to the source table in our example), that name appears in the Table/Range box. Otherwise, Excel discerns the extent of your source data and presents a range reference in that box:

By default, your PivotTable arrives on a new worksheet, and that's generally a good arrangement. If you want it elsewhere, specify where in the Location box. After you click OK, Excel generates a blank table layout on the left side of the worksheet and displays the PivotTable Field List window on the right (see Figure 22-5). The PivotTable Field List window is docked at the right by default. You can make it wider or narrower by dragging the split bar on its left edge. You can also undock it or drag it across the worksheet and dock it on the left.

Figure 22-5: As you select the check boxes for fields in the PivotTable Field List window, Excel populates the table layout at the left side of the worksheet.

Note 

If you want to work with only a subset of items in a field, you can filter the field before you add it to the table. If your data source is large, and particularly if the source is external, you can save some time by filtering in advance. (You can also filter fields after you have created the table, of course.) To filter a field before you add it to the table, select the field name in the PivotTable Field List window, and then click the arrow on the right. For more details, see "Filtering PivotTable Fields" on page 728.

To put some fields and data on that blank layout, begin by selecting the check boxes for those fields in the Choose Fields To Add To Report area of the PivotTable Field List window. As you select fields, Excel positions them in the four boxes below. These four boxes represent the various components of the table. The Row Labels and Column Labels boxes hold the fields that will appear on the row and column axes. The Report Filter box holds the field (or fields) you want to use to filter the table (comparable to the Category field in Figure 22-4), and the Values box holds the field (or fields) you want to use for calculations-the data you're summarizing (your sales, for example).

Initially, Excel puts selected fields in default table locations that depend on their data types. Most likely you'll want some arrangement other than the one you get by default. That's not a problem, because you can move fields from one location to another easily; just drag them between the various boxes below the PivotTable Field List window. Let's look at an example.

To create the table shown in Figure 22-3, we want to put the Category and Channel fields in the Row Labels box, the Year and Quarter fields in the Column Labels box, and the Sales field in the Values box. When we select the check boxes for those fields, Excel drops the Category and Channel fields in the Row Labels box (because they are text fields) and the Sales field in the Values box (because it's a numeric field). These are all good guesses on the part of Excel-and, in fact, it's just what we want. In addition to putting field headings in the appropriate boxes, Excel begins creating our PivotTable- as Figure 22-6 shows.

Figure 22-6: Excel builds the table, piece by piece, as you select fields.

So far, so good. The numeric formats aren't right, but we can fix that easily enough.

What remains is to put the Year and Quarter fields into the Column Labels box. Unfortunately, if we simply select their check boxes, Excel drops these fields in the Values box, because the fields are numbers and the program has a predilection for adding numbers. This (see Figure 22-7) is definitely not what we want.

Figure 22-7: By default, Excel puts all numeric fields, including years and quarters, in the Values box. You can fix that by dragging field headings to the appropriate locations.

The solution is simple: Select the check boxes for the Year and Quarter fields, and then drag the Sum of Quarter and Sum of Year headings from the Values box to the Column Labels box. (Alternatively, you can make sure your field headings go where you want them by dragging them directly from the Choose Fields To Add To Report box to the appropriate boxes below, disregarding the defaults.)