This document introduces the Pivot Table functionality that allows you to perform complex analysis of raw data in a worksheet using pivot tables.
A pivot table represents a summary table used to explore, analyze and aggregate huge amounts of data in a worksheet. It helps break your data into categories and subcategories, and automatically calculates subtotals and grand totals using the most suitable summary function from a predefined list.
Currently, you can use only worksheet data as a data source for a pivot table. External data sources (such as ODC files, OLAP cubes, relational databases, XML files, etc.) are not supported.
An individual pivot table is represented by the PivotTable object and can be accessed by its name in the pivot table collection. You can change the source data for your pivot table or move it to a new location in a worksheet by using the PivotTable.ChangeDataSource and PivotTable.MoveTo methods, respectively.
After you insert and position a pivot table in a worksheet, you should fill it with data by adding necessary fields (columns of the source range) to the report. All fields are stored in the PivotFieldCollection accessible from the PivotTable.Fields property. To add a field to the PivotTable report, access this field by its name in the collection (by default, the label of the corresponding column is used as the field name) and move it to the required PivotTable area:
Subsequently, you can move the desired field to another area of the pivot table to change the report layout, or you can re-order fields in a specific area using the MoveDown, MoveUp, MoveToBeginning or MoveToEnd method called for the field whose position you wish to change. To remove a field from the pivot table, use the Remove or RemoveAt method of the collection containing this field.
After you create a pivot table and populate it with data, you can modify its settings to improve the readability and comprehension of your report. You can adjust the report layout, apply a built-in or custom style to the pivot table, filter field items to display only significant values, and more. To do this, use properties of the PivotTable object listed in the table below.