Excel® 2007 VBA Programmer's Reference

Chapter 7. PivotTables

PivotTables are an extension of the cross tabulation tables used in presenting statistics, and can be used to summarize complex data in a table format. An example of cross tabulation is a table showing the number of employees in an organization, broken down by age and sex. PivotTables are more powerful and can show more than two variables, so they could be used to show employees broken down by age, sex, and alcohol, to quote an old statistician's joke.

The input data for a can come from an Excel worksheet, a text file, an Access database, or a wide range of external database applications. PivotTables can handle up to 256 column or row fields, if you can interpret the results. They can perform many types of standard calculations, such as summing, counting, and averaging. They can produce subtotals and grand totals.

Data can be grouped as in Excel's outline feature, and you can hide unwanted rows and columns. You can also define calculations within the body of the table. PivotTables are also very flexible if you want to change the layout of the data and add or delete variables. You can create PivotCharts that are linked to your PivotTable results in such a way that you can manipulate the data layout from within the chart.

PivotTables are designed so that you can easily generate and manipulate them manually. If you want to create many of them, or provide a higher level of automation to users, you can tap into the Excel object model. This chapter examines the following ...