[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Collapse]WPF Controls
 [Expand]What's Installed
 [Expand].NET Core 3 Support
 [Expand]Common Concepts
 [Expand]MVVM Framework
 [Collapse]Controls and Libraries
  [Expand]Data Grid
  [Expand]Ribbon, Bars and Menu
  [Expand]Charts Suite
  [Expand]Pivot Grid
    Product Structure
   [Expand]Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
   [Collapse]Pivot Table Overview
     Pivot Table Structure
     Pivot Table API
   [Expand]Charts and Graphics
    Operation Restrictions
    Find and Replace
   [Expand]Mail Merge
   [Expand]Visual Elements
    Themes and Templates
  [Expand]Rich Text Editor
  [Expand]Tree List
  [Expand]Gauge Controls
  [Expand]Map Control
  [Expand]Layout Management
  [Expand]Windows Modern UI
  [Expand]Data Editors
  [Expand]Navigation Controls
  [Expand]Spell Checker
  [Expand]Property Grid
  [Expand]PDF Viewer
  [Expand]TreeMap Control
  [Expand]Gantt Control
  [Expand]Diagram Control
  [Expand]Windows and Utility Controls
   Dialogs, Notifications and Panels
  [Expand]Scheduler (legacy)
 [Expand]Scaffolding Wizard
  Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Report and Dashboard Server
[Expand]eXpressApp Framework
[Expand]eXpress Persistent Objects
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

Pivot Table API

This topic provides a brief overview of the Spreadsheet Pivot Table API used to create and modify pivot tables in code.

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, the SpreadsheetControl uses 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.




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.


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.


Provides access to the display options of the pivot table.


Allows you to specify filtering criteria to show specific values in the pivot table.


Allows you to apply restrictions on different PivotTable operations.

Expanded See Also

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