Microsoft Office Tutorials and References
In Depth Information
Chapter 18: Pivot Tables
In This Chapter
• An introduction to pivot tables
• How to create a pivot table from a worksheet database or table
• How to group items in a pivot table
• How to create a calculated field or a calculated item in a pivot table
• Introducing the new Data Model feature
• How to create pivot charts
Excel's pivot table feature is perhaps the most technologically sophisticated component in Excel. This chapter
may seem a bit out of place in a book devoted to formulas. After all, a pivot table does its job without using for-
mulas. That's exactly the point. If you haven't yet discovered the power of pivot tables, this chapter demonstrates
how using a pivot table can serve as an excellent alternative to creating many complex formulas.
About Pivot Tables
A pivot table is essentially a dynamic summary report generated from a database. The database can reside in a
worksheet or in an external file. A pivot table can help transform endless rows and columns of numbers into a
meaningful presentation of the data.
For example, a pivot table can create frequency distributions and cross-tabulations of several different data di-
mensions. In addition, you can display subtotals and any level of detail that you want. Perhaps the most innovat-
ive aspect of a pivot table lies in its interactivity. After you create a pivot table, you can rearrange the information
in almost any way imaginable and also insert special formulas that perform new calculations. You can even create
post-hoc groupings of summary items: for example, combining Northern Region totals with Western Region
totals. And the icing on the cake is that with but a few mouse clicks, you can apply formatting to a pivot table to
convert it to boardroom-quality attractiveness.
Pivot tables were introduced in Excel 97, and this feature improves with every new version of Excel. In Excel
20013, you can now create pivot tables from multiple data tables. Unfortunately, many users avoid pivot tables
because they think that they are too complicated. My goal in this chapter is to dispel that myth.
One minor drawback to using a pivot table is that unlike a formula-based summary report, a pivot table does not
update automatically when you change the source data. This does not pose a serious problem, however, because a
single click of the Refresh button forces a pivot table to update itself with the latest data.
A Pivot Table Example