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 Data → Refresh Data (or by clicking the exclamation mark on the PivotTable toolbar). This action tells Excel to scan the source data and regenerate the pivot table.
Figure 9. This pivot table is one-dimensional. It groups orders by product, but it doesn't subgroup them by country. When creating this pivot table, don't drop anything in the Drop Column Fields Here region.
Rearranging a Pivot Table
So far, you've seen how to use a pivot table to quickly build complex summaries. 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:
To remove a field from a pivot table, click the field header (which looks like a gray box containing the name of the field) and drag it back to the PivotTable Field List window. 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 to the correct region. For example, you can reverse the example shown earlier by dragging the column field (Country) to the row region, and dragging the row field (Product) to the column region.
Figure 10 shows one way you could rearrange the pivot table shown in Figure 8.
Figure 10. Another way to organize the data shown in Figure 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.
Editor's note: The content for this article was excerpted from Chapter 20 of O'Reilly's Excel: The Missing Manual, which covers Excel 2002 and 2003, the most recent versions for Windows at the time of this writing. If you use Excel, this book should be on your shelf.
Matthew MacDonald is a developer, author, and educator in all things Visual Basic and .NET. He's worked with Visual Basic and ASP since their initial versions, and written over a dozen books on the subject, including The Book of VB .NET (No Starch Press) and Visual Basic 2005: A Developer's Notebook (O'Reilly). His web site is http://www.prosetech.com/.
Return to the Windows DevCenter.