Section 21.2. Building Pivot Tables | Excel 2007[c] The Missing Manual

21.2. Building Pivot Tables

Now that you've learned the role that pivot tables play in summarizing data, it's time to create your own. Before you begin, you need to have a long list of raw data that you want to summarize. You could use the customer list from the previous example, but it's too small to really demonstrate the benefits of pivot tables. A better example is something like the list of order information shown in Figure 21-4.

Figure 21-4. This worksheet shows some entries from a list of 2,155 grocery store items that have been ordered. Lists like this make great pivot table candidates.

Not all data is suited for a pivot table. To work well, your data needs to meet a few criteria:

It must include at least one column that has duplicate values . In the order table shown in Figure 21-4, there are multiple records with the same value in the Customer column. Accordingly , you can create a separate group of ordered items for each customer.
It must include some numeric information . You'll use this information to create subtotals. Often, you'll be interested in generating a simple count, total, or average, although you can also find maximum, minimum, and standard deviation, and you can use your own formulas.

Note: It's technically possible to create subtotals without using a numeric column. In this case, the subtotals just count the number of values in the group. This approach is occasionally useful, but it's not as powerful as other types of subtotals.

The order information table is perfect for a pivot table because there are several columns you can use to group the order rows. These include:

Product and Category . Find out how well specific products are selling or what the hottest product categories are.
Customer . Find out who's making the most purchases.
Ship City and Ship Country . Find out where the majority of your customers reside.

A pivot table can handle all of these comparisons. You don't need to choose one column or another before you start building the pivot table.

Tip: The best way to learn about pivot tables is to perform the steps in this chapter, and then start experimenting. If you don't happen to have a table with hundreds of records on hand, you can download the workbook shown in Figure 21-4 from the "Missing CD" page at www.missingmanuals.com. It gives you 2,155 rows to summarize and pivot to your heart's content.

21.2.1. Preparing a Pivot Table

Creating a new pivot table is a two-step process. First you need to run the Pivot-Table and PivotChart wizard, which asks you to identify the data you want to summarize and select the location where you want to place the pivot table. The next step is to actually define the structure of the pivot table and try out different ways of organizing and grouping your data.

The following steps lead you through the first step in creating a new pivot table:

Select the range of cells that you want to use for your pivot table .

If you plan to add more rows later, consider using a defined table (Chapter 14) instead of selecting a range of cells. That way, when you add rows to the data list and refresh the pivot table, Excel automatically adds your new rows to the pivot table.

Select Insert Tables PivotTable PivotTable .

If you're creating a pivot table for a table that you've defined with the Insert Tables Table feature (Section 14.1.1), theres a shortcut. Just move anywhere inside your table and choose Table Tools Design Tools Summarize with Pivot.

Either way, the Create PivotTable dialog box appears (Figure 21-5). Excel automatically chooses the "Select a table or range", with the table name or cell range you selected.

Figure 21-5. The Create PivotTable dialog box asks where your raw data is (in a table, cell range, or external data source) and where you want to put the pivot table that summarizes it.

Note: You can also build a pivot table based on records that you select from an external database. But before you take this step, you must configure your database as an Excel data source. To learn how to do so, see Section 24.1.1.

Select "New worksheet" to create a new worksheet for your pivot table, which is usually the easiest option .

Pivot tables are fairly complex creations, so the easiest way to manage them (and keep them separate from the rest of your data) is to pop them into a new worksheet. Alternatively, you can choose "Existing worksheet" to insert your pivot table on a worksheet that's already in your workbook. In this case, you need to specify the cell reference for the top-left corner of the pivot table. If there's data under this cell or to the right of it, then Excel may overwrite it as it generates the pivot table. Usually, the best approach is to place a new pivot table on a separate worksheet.
Click OK .

Excel inserts the new pivot table. Because you haven't defined the columns it should use for grouping and analyzing rows, this pivot table appears as an empty placeholder, as shown in Figure 21-6.

Tip: If you choose to create a new worksheet, Excel gives the worksheet an unhelpful name (like Sheet5), and then places it before all your other worksheets. You can rename the worksheet and drag it into a better spot using the techniques described in Chapter 4.

Figure 21-6. This worksheet shows a newly created pivot table that doesn't yet contain any information. When you move to a cell inside the pivot table, Excel displays the PivotTable Field List pane on the right, which contains a list of all the columns in the data that you're summarizing.

Note: When you create an empty pivot table in compatibility mode (Section 1.4.2.1), it looks a little different. Instead of the graphical box shown in Figure 21-6, you get a series of linked white boxes, with messages inviting you to drop fields in each one. Microsoft designed this strange appearance to mimic the way that blank pivot tables appeared in Excel 2003. But don't worryonce you create a pivot table, it'll look much more similar, and the instructions in this chapter work just as well whether or not you're in compatibility mode.

21.2.2. Pivot Table Regions

To build a pivot table, simply drag columns from the PivotTable Field List pane on the right side of the Excel window, and drop them into one of the four boxes underneath. As you work, Excel generates the pivot table, updating it dynamically as you add, rearrange, or remove columns.

Note: Excel refers to all your source data's columns as fields .

To understand how to fill up a pivot table with data, you need to know how each region works. Altogether, a pivot table includes four regions:

Values . These are the fields that you want to subtotal and display in your pivot table. Usually, this is the numeric information you use to build averages and totals.
Row Labels . These fields let you break down the data into categories. The pivot table uses the Row Labels fields to create separate groups, and puts the information for each group into a single row. Using the product list in Figure 21-4, you could group products into categories by dropping the Category field here.
Column Labels . Often, you want to subdivide your data in more than one way at once. Like the Row Labels fields, the Column Labels fields are used to create groups. The difference is that these groups are displayed in separate columns. If you use Category for a row field and Ship Country for a column field, you wind up with a table that divides sales figures into rows by product category, and then divides each category row into columns by country.
Report Filter . These fields play the same role as filter conditions do in a data table (Section 14.2.4), in that they limit which data the pivot table displays. If you want to look at a breakdown of sales by category, but you want to consider only U.S. sales, you could add Ship Country as a page field and configure it accordingly.

It really doesn't matter whether you use a field for row grouping or column grouping. The pivot table shows the same data either way, but one approach may be more readable than another. If you have a field with extremely long names , it probably works better as a row field than as a column field (where it would stretch out the width of the whole column).

Also, consider how many different groups you want to create. If you want your pivot table to compare sales by category and country, and your list features five categories and 20 countries , you'll probably be best off if you use the country field as a row field and the category field as a column field. That keeps the table long and narrow, which is easier to read and print.

21.2.3. Laying Out a Pivot Table

To get a better understanding of how to create a pivot table, it helps to follow along with a basic example. These steps lead you through the process of creating a summary that compares the products and shipping locations shown in Figure 21-4.

In the PivotTable Field List pane, drag the Product field into the Row Labels box underneath .

When you drop the field, Excel fills in the names of all the products from the list from top to bottom, in alphabetical order (see Figure 21-7). When you finish this step, Excel creates one row containing the subtotals for each product.

Figure 21-7. In this example, the list of products has already been added to the row area (and it appears in column A), and the second grouping criteria (the list of countries) is being dragged into the Column Labels box. Notice that once a field is linked, its name is listed in boldface in the PivotTable Field List.

From the PivotTable Field List, drag the Ship Country field to the Column Labels box .

When you drop the field, Excel fills in the names of all the countries from the list from left to right, in alphabetical order. In other words, each country is listed in its own column.
Now you need to choose what data you want to examine. Drag the Quantity field to the Values box .

This step is designed to actually fill the table with dataspecifically, the numbers of products that were ordered by customers in various countries. It's helpful to remember what's in the table just prior to this step: a list of products in column A and, from column B on, each column is labeled with a different country.

Once you complete this step, Excel generates the pivot table using its standard calculation option, which is to perform sums on all the fields in the Values box. In this example, it adds all the values in the Quantity field for a given group, which tells you the total number of units of a specific product shipped to a specific country. Figure 21-8 shows the result.

Tip: Pivot tables also calculate row and column subtotals. If you want to find the total number of units shipped for a given product across all countries, scroll to the far right end of the chart. If you want to find the total number of units sold in a given country, scroll to the totals at the bottom of the chart.

Figure 21-8. This pivot table totals the quantity of product units in every order. For example, it tells you that 20 units of Boston Crab Meat were shipped to Argentina.

This example built a fairly sophisticated two-dimensional pivot table, which means that it compares two different groupings (one represented with rows, and the other represented with columns). Most of the pivot tables you'll see in real life are two-dimensional, but, there's no reason you can't create simpler one-dimensional pivot tables. All you need to do is leave out either the Column Labels or Row Labels. Figure 21-9 shows a pivot table that simply totals the number of units sold for particular products.

Unlike most other elements in Excel, pivot tables don't refresh themselves automatically. That means that if you change the source data, the pivot table may show out-of-date totals. To correct this problem, you can refresh the pivot table by moving to one of the cells in the pivot table and selecting PivotTable Tools Options Data Refresh (or the keyboard shortcut Alt+F5). This action tells Excel to scan the source data and regenerate the pivot table.

21.2.4. Formatting a Pivot Table

As you've probably already noticed, when you move into one of the cells of a pivot table, two new tabs spring up in the ribbon under the PivotTable Tools heading. These are similar to the tabs that appear when you select a chart, picture, or table.

You can use the PivotTable Tools Options tab to access a few advanced features that you've yet to consider, like grouping, pivot table formulas, and pivot charts . You'll consider these features in the rest of this chapter. The PivotTable Tools Design tab is more modest; you use it to format and otherwise fine-tune the appearance of your pivot table.

Figure 21-9. This pivot table is one-dimensional. It groups orders by product, but it doesn't subgroup them by country. If you want to create a pivot table like this, don't drop anything in the Column Labels box.

The PivotTable Tools Design tab is carved into three sections. At the far right, you'll find the PivotTables Styles section, with a familiar style gallery. If you choose one of the entries here, Excel adjusts your PivotTable automatically, giving it new colors and shading that can range from subtle to dramatic.

Tip: The colors that Excel uses in the pivot table styles actually come from your workbook theme (Section 6.2.4). So if you want to get a completely different set of accent colors, just choose a new theme from the Page Layout

Themes

Themes gallery.

PivotTable Style Options section of the ribbon. If you want to use a specific style but you dont want to apply its banding effects, clear the Banded Rows and Banded Columns checkboxes. Similarly, if you don't want to apply the style formatting to headers, clear the Column Headers or Row Headers checkboxes.

Finally, the PivotTable Tools Design Layout section lets you choose from various preset options that control spacing and subtotals. This section contains four submenus:

Grand Totals . Pick an option from this list to show or hide the totals at the end of each row and column.
Subtotals . Pick an option from this list to show or hide subtotals at the end of every group. This setting has an effect only if your groups are subdividedin other words, if you have more than one field in the Row Labels section or more than one field in the Column Labels section. Otherwise, the group "subtotals" are actually the grand totals. (You'll learn more about using more layers of grouping in Section 21.3.)
Report Layout . Pick an option from this list to choose how tightly packed your pivot table is. In Excel 2007, pivot tables are shown in "compact" form, which keeps columns as narrow as possible. (Typically, the column is just wide enough to fit the data inside and the column header.) Previous versions of Excel made each column as wide as the widest column, which takes considerably more room. If you still want this space-chewing display choice, then choose PivotTable Tools Design Layout Report Layout Show in Outline Form.
Blank Rows . Pick an option from this list to add a blank line between groups. This option has an effect only if there's more than one field in the Row Labels section.

Note: In previous versions of Excel, pivot tables were apt to lose their formatting every time you refreshed them to include new data. But in Excel 2007, pivot tables are much better behaved. Even if you format the pivot table by hand (by applying different fonts and colors to individual cells, for instance), the formatting sticks when you refresh the pivot table.

21.2.5. Rearranging a Pivot Table

So far, you've seen how to use a pivot table to quickly build a summary table. However, pivot tables have another key benefit: flexibility. There's no limit to how many times you can move fields or recalculate your summary so that it performs different calculations.

To change a pivot table, you can use the following techniques in the PivotTable Field List pane:

To remove a field from a pivot table, click the field header (in the appropriate box), and then drag it out of the PivotTable Field List pane. The mouse pointer changes to an X symbol to indicate you're removing the field.
To move a field from one position to another, just drag the field header from one box to another. You can reverse the example shown earlier by dragging the column field (Country) to the Row Labels region, and dragging the row field (Product) to the Column Labels region.

Figure 21-10 shows one way you could rearrange the pivot table shown in Figure 21-8.

Figure 21-10. Another way to organize the data shown in Figure 21-8 is to see how the product categories perform in each of the countries, as shown here. This summary table makes it easy to spot categories that do unusually well in certain countries.