Analyzing Microsoft Excel 2010 Data with PivotTable Reports

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 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 performs its own summary calculations.

The source range on your Excel worksheet can be a table (as described in Chapter 22, “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 dimensions automatically adjust to encompass the new data, and hence your PivotTable stays in sync with the expanded source data.

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 displaying 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 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 23-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 23-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.

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 at the bottom of the window. These four boxes represent the various components of the table. The Row Labels and Column Labels boxes hold the fields that 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 23-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 at the bottom of the PivotTable Field List window. Let’s look at an example.

To create the table shown in Figure 23-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 23-6 shows.

Figure 23-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 23-7) is definitely not what we want.

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.)

Figure 23-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.