Log In
[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Collapse]Office File API
  Demo Application
  Redistribution and Deployment
 [Collapse]Spreadsheet Document API
   Product Structure
   Getting Started
  [Expand]Spreadsheet Document
   Supported Formats
  [Expand]Cell Basics
  [Expand]Spreadsheet Formulas
   Defined Names
   Data Binding
  [Collapse]Pivot Tables
    Pivot Table Structure
  [Expand]Mail Merge
 [Expand]Word Processing Document API
 [Expand]PDF Document API
 [Expand]Excel Export Library
 [Expand]Snap Report API
 [Expand]Zip Compression and Archive API
 [Expand]Barcode Generation API
 [Expand]Unit Conversion API
 [Expand]API Reference
[Expand]Report and Dashboard Server
[Expand]eXpressApp Framework
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Pivot Tables

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.

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.


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.

Expanded 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:

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.

Expanded 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.



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.
Allow you to apply the banded column/row formatting to the pivot table.
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.

Expanded See Also

Is this topic helpful?​​​​​​​