Spreadsheet Pivot Tables

• Sep 19, 2023
• 4 minutes to read

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.

Source Data

One-dimensional PivotTable Report

Two-dimensional PivotTable Report

Multi-dimensional PivotTable Report

All pivot tables in a worksheet are stored in the PivotTableCollection object, which you can access using the Worksheet.PivotTables property. The PivotTableCollection interface provides the basic methods of working with pivot tables in code. Use the PivotTableCollection.Add method to create a pivot table based on the cell range in a source worksheet or using the data cache of the existing PivotTable report (PivotCache). For an example on how to insert a pivot table, refer to the How to: Create a Pivot Table article.

Important

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.

PivotTable Structure

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:

• row axis area - represented by the collection of row fields accessible from the PivotTable.RowFields property;
• column axis area - represented by the collection of column fields accessible from the PivotTable.ColumnFields property;
• page/report filter area - represented by the collection of page fields accessible from the PivotTable.PageFields property;
• data area - represented by the collection of data fields accessible from the PivotTable.DataFields property.

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.

In addition to fields from the source data, the pivot table may also contain calculated fields, whose values are produced based on custom formulas. To add a calculated field to the PivotTable report, use the PivotCalculatedFieldCollection.Add method of the PivotTable.CalculatedFields collection.

Each non-calculated field is made up of items - unique data entries contained in the field. All field items are stored in the PivotItemCollection collection accessible using the PivotField.Items property. You can sort items within a field (PivotField.SortType, PivotField.SortItems), filter them (PivotTable.Filters), or group them to create new subsets of data (PivotField.GroupItems).

A pivot field may also contain one or more calculated items. To add a calculated item to the PivotTable field, use the PivotCalculatedItemCollection.Add method of the PivotField.CalculatedItems collection.

PivotTable Settings and Capabilities

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.

Property	Description
PivotTable.Layout	Provides access to the layout settings of the pivot table that enable you to show the report in a compact, outline or tabular form, display or hide subtotals and grand totals, insert a blank row after each group of items, etc.
PivotTable.Style	Allows you to set the predefined or custom style to be applied to the pivot table.
PivotTable.BandedColumns PivotTable.BandedRows	Allow you to apply the banded column/row formatting to the pivot table.
PivotTable.ShowColumnHeaders PivotTable.ShowRowHeaders	Allow you to format column and row headers in the PivotTable report.
PivotTable.View	Provides access to the display options of the pivot table.
PivotTable.Filters	Allows you to specify filtering criteria to show specific values in the pivot table.
PivotTable.Behavior	Allows you to apply restrictions on different PivotTable operations.