PivotTables are linked to the data from which they’re derived. If the PivotTable is based on external data (data stored outside Excel), you can choose to have it refreshed at regular time intervals, or you can refresh it whenever you want.

Figure 23-1 shows Books.xlsx, a list of sales figures for a small publishing firm. The list is organized by year, quarter, category, distribution channel, units sold, and sales receipts. The data spans a period of eight quarters (2009 and 2010). The firm publishes six categories of fiction (Mystery, Western, Romance, Sci Fi, Young Adult, and Children) and uses three distribution channels—domestic, international, and mail order. It’s difficult to get useful summary information by looking at a list like this, even though the list itself is well organized.

Figure 23-1. It’s difficult to see the bottom line in a flat list like this; turning the list into a PivotTable will help.

Figures Figure 23-2 through Figure 23-4 show several ways you can transform this flat table into PivotTables that show summary information at a glance.

The example on the left in Figure 23-2 breaks the data down first by category, second by distribution channel, and finally by year, with the total sales at each level displayed in column B. Looking at this table, you can see (among many other details) that the Children category generated domestic sales of $363,222, with more revenue in 2010 than in 2009.

In the example on the right in Figure 23-2, the per-category data is broken out first by year and then by distribution channel. The data is the same; only the perspective is different.

Figure 23-2. These two PivotTables provide summary views of the information in Figure 23-1.

Both the PivotTables shown in Figure 23-2 are single-axis tables. That is, we generated a set of row labels (Children, Mystery, Romance, and so on) and set up outline entries below these labels. (And, by default, Excel displays outline controls beside all the headings, so we can collapse or expand the headings to suit our needs.)

Figure 23-3 shows a more elaborate PivotTable that uses two axes. Along the row axis, we have categories broken out by distribution channel. Along the column axis, we have years (2009 and 2010), and we added the quarterly detail (not included in the Figure 23-2 examples) so we can see how each category in each channel did each quarter of each year. With four dimensions (category, distribution channel, year, and quarter) and two axes (row and column), we have a lot of choices about how to arrange the furniture. Figure 23-3 shows only one of many possible permutations.

Figure 23-3. In this PivotTable, we arranged the data along two axes—rows and columns.

Figure 23-4 presents a different view. Now the distribution channels are arrayed by themselves along the column axis, while the row axis offers years broken out by quarters. The category, meanwhile, has been moved to what you might think of as a page axis. The data has been filtered to show the numbers for a single category, Mystery, but by using the filter control at the right edge of cell B2, we could switch the table to a different category (or combination of categories). Filtering the Category dimension by one category after another is like flipping through a stack of index cards.

None of these tables required more than a few clicks to generate.

Figure 23-4. This PivotTable presents a filtered view, confining the report to a single category.