Chapter 18: Pivot Tables

 Download CD Content

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 formulas. That's exactly the point. If you haven't yet discovered the power of pivot tables, this chapter will demonstrate 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 dimensions. In addition, you can display subtotals and any level of detail that you want. Perhaps the most innovative 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 even can create post hoc groupings of summary items: for example, combine 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-attractive.

Pivot tables have been around since Excel 97. Unfortunately, many users ignore this feature because they that think creating a pivot table is too complicated. The pivot table feature in Excel 2007 is vastly improved, and creating and working with pivot tables is easier than ever. 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 use the latest data.