[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
  Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Expand]MVVM Framework
 [Collapse]Controls and Libraries
   Reporting
  [Expand]Data Grid
  [Expand]Ribbon, Bars and Menu
  [Expand]Charts Suite
  [Expand]Pivot Grid
  [Expand]Scheduler
  [Collapse]Spreadsheet
    Product Structure
   [Expand]Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
   [Collapse]Pivot Table Overview
     Pivot Table Structure
     Pivot Table API
   [Expand]Charting Overview
    Shapes
    Printing
    Protection
    Find and Replace
   [Expand]Mail Merge
   [Expand]Visual Elements
    Services
    Themes and Templates
   [Expand]Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Tree List
  [Expand]Gauge Controls
  [Expand]Map Control
  [Expand]Layout Management
  [Expand]Windows Modern UI
  [Expand]Printing-Exporting
  [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
 [Expand]Localization
  Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]eXpress Persistent Objects
[Expand]CodeRush
[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 Overview

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

    Important

    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.

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 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, 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 See Also

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