[Expand]General Information
[Collapse]WinForms Controls
  .NET Core Support
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications, and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Property Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Gantt Control
  [Expand]Chart Control
  [Expand]Map Control
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Collapse]Pivot Tables
     Pivot Table Structure
     Pivot Table API
   [Expand]Charts and Graphics
   [Expand]Mail Merge
    Operation Restrictions
    Find and Replace
   [Expand]Visual Elements
   [Expand]Keyboard Shortcuts
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]PDF Viewer
  [Expand]TreeMap Control
  [Expand]Sunburst Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[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
[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

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.

Pivot tables in the SpreadsheetControl are interactive. That means you can easily change the layout of the existing report to create a new summary table that will fully meet your analysis requirements. For example, the image gallery below shows three PivotTable reports that allow you to analyze the same business data in different forms.

Expanded Pivot Table Functionality

The SpreadsheetControl provides a comprehensive and straightforward Pivot Table API that enables you to create and modify pivot tables in code.

In particular, you can perform the following actions.

  • Create a pivot table using a cell range as a data source or base your report on the data cache of the existing pivot table.


    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.

  • Organize the structure of a pivot table by adding and arranging its fields.
  • Calculate summaries against data fields using a wide range of preset aggregate functions.
  • Change source data for a pivot table.
  • Refresh a pivot table to obtain the latest data from the source.
  • Move a pivot table to a new location.
  • Display a pivot table in a compact, outline, or tabular form.
  • Format a pivot table by applying a built-in or custom style.
  • Display or hide subtotals and grand totals for rows and columns.
  • Insert a blank row after each group of items.
  • Sort field items in descending or ascending order.
  • Apply a filter to a pivot table to show or hide specific items, construct the filter expression to display only items that meet the specified condition, or filter report data based on calculated values.
  • Create calculated fields and items.
  • Group items in a PivotTable report in a custom way to create new subsets of data.

For details, refer to the Pivot Tables section of examples.

Expanded Manage Pivot Tables in the User Interface

Insert a Pivot Table

End-users can create a pivot table using the Create PivotTable dialog, which can be invoked by clicking the PivotTable button on the Insert tab, in the Tables group.

Organize the PivotTable Structure

End-users can use the Field List pane to add fields to the pivot table and arrange them as required using drag and drop.

Adjust the PivotTable Options

After end-users create a pivot table and lay out its fields, they can fine-tune their report using various commands located on the PivotTable Tools contextual tab, which appears when any cell in the pivot table is selected.

Options on the Analyze tab allow end-users to change the underlying settings of a pivot table. They can specify various options affecting the table layout, display and print settings, select the aggregate function used to summarize values in the data field, change the data source for the report, refresh the pivot table, change its location, display or hide the Field List, etc.

The Design tab of the PivotTable Tools contextual tab provides a wide range of formatting options enabling end-users to change the pivot table appearance. In particular, end-users can adjust the report layout by displaying the pivot table in compact, outline, or tabular form, show or hide subtotals and grand totals, apply a style to the pivot table and configure style options.

Explore the PivotTable Data

After a pivot table is completely organized and adjusted, end-users can easily explore the resulting report: sort field items, expand or collapse individual categories of data, and apply a filter to the pivot table to display only significant values.

Expanded Online Video

To learn the basics of working with pivot tables in the SpreadsheetControl, watch the following introductory video.

Expanded See Also

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