[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
   [Expand]Pivot Table Overview
   [Expand]Charts and Graphics
    Operation Restrictions
    Find and Replace
   [Expand]Mail Merge
   [Collapse]Visual Elements
      Format Cells Dialog
      Paste Special Dialog
      Edit Hyperlink Dialog
      Symbol Dialog
      Create Table Dialog
      Change Chart Type Dialog
      Find and Replace Dialog
      Encrypt Document Dialog
      Password Dialog
      Protect Workbook Dialog
      Protect Sheet Dialog
      User Permissions Dialog
      Insert Function Dialog
      Function Arguments Dialog
      Name Manager
      New Name Dialog
      Create Names from Selection Dialog
      Subtotal Dialog
      Data Validation Dialog
      Properties Dialog
      Page Setup Dialog
      Header and Footer Dialog
      Create PivotTable Dialog
      Move PivotTable Dialog
      Change PivotTable Data Source Dialog
      PivotTable Field List
      PivotTable Value Field Settings Dialog
      PivotTable Field Settings Dialog
      PivotTable Sort Dialog
      Insert Calculated Field Dialog
      Insert Calculated Item Dialog
      PivotTable Solve Order Dialog
      PivotTable Options Dialog
      New Formatting Rule Dialog
      Conditional Formatting Rules Manager
     Pop-up Menus
    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)

PivotTable Options Dialog

The Pivot Table Options dialog allows end-users to rename the Pivot Table and adjust its layout, filter, display, printing and data settings.


The SpreadsheetControl does not support external data sources (ODC files, OLAP cubes, relational databases, XML files, etc.). Therefore all related options are unavailable in this dialog.

End-users can invoke this dialog by selecting the PivotTable Options... item in the context menu...

...or by clicking the PivotTable Options button on the ribbon. Add the PivotTable group to enable this button (refer to the Lesson 1 - Create a Simple Spreadsheet topic for details on how to provide a Ribbon UI for the SpreadsheetControl).

This dialog includes the following tabs:

Expanded Layout & Format

Options under this tab allow end-users to specify additional layout options for a PivotTable report: merge and center cells containing labels, set the indent for row labels, rearrange page fields and autofit column widths when updating the pivot table.

The For error values show: and For empty values show: editors allow end-users to enter the text for cells with errors and empty values.

The following API allows you to specify the Layout & Format options in code:




Gets or sets a value indicating whether to merge and center cells containing item labels for the outer row and column fields, subtotal and grand total captions.


Gets or sets the indent increment for items from different row fields when a pivot table is shown in compact form.


Gets or sets the order in which multiple page fields are displayed in the PivotTable report filter area.


Gets or sets the number of page fields to display before starting another column or row based on the PageOrder property value.



Gets or sets a value indicating whether to show custom error messages in cells.

Gets or sets the text to be displayed in cells that contain errors.



Gets or sets a value indicating whether to display a custom string in cells that contain no values.

Gets or sets the text to be displayed in cells with no values.


Gets or sets a value indicating whether column widths should be automatically resized when the pivot table is recalculated or refreshed.

Expanded Totals & Filters

This tab contains options used to specify whether to display grand totals for rows or columns, or to apply multiple filters to a single field in the row or column area.

Use the following members to set grand total and filter options in code:

API Description
PivotLayout.ShowRowGrandTotals Gets or sets a value indicating whether grand totals should be displayed for rows in the PivotTable report.
PivotLayout.ShowColumnGrandTotals Gets or sets a value indicating whether grand totals should be displayed for columns in the PivotTable report.
PivotBehaviorOptions.AllowMultipleFieldFilters Gets or sets a value indicating whether fields in the pivot table can have multiple filters applied to them at the same time.

Expanded Display

Options under this tab allow end-users to show or hide certain report elements, such as expand/collapse buttons, field headers with filter arrows, and the Values row.

Use the following members to set display options for a pivot table in code:

API Description
PivotViewOptions.ShowDrillIndicators Gets or sets a value indicating whether the expand/collapse buttons should be displayed in a pivot table.
PivotViewOptions.ShowFieldHeaders Gets or sets a value indicating whether to display the row and column field captions and filter drop-down arrows in a pivot table.
PivotViewOptions.ShowValuesRow Gets or sets a value indicating whether to display the Values row that may appear when there are multiple fields in the PivotTable data area.

Expanded Printing

End users can use this tab to specify printing options: print the expand/collapse buttons, repeat row labels on every page or enable print titles.


The Printing tab's options have no effect on the SpreadsheetControl's printing process. However, these options are saved in a file in supported formats, so that you can print the document from Microsoft® Excel®.

Expanded Data

Under this tab, end users can select or clear the Save source data with file check box to specify whether to store the PivotTable cache with the document. Other Data tab options are written to a file in supported formats, but have no effect on a pivot table loaded in the SpreadsheetControl.

Expanded Alt Text

This tab allows end-users to specify an alternative title (PivotViewOptions.AltTextTitle) and description (PivotViewOptions.AltTextDescription) for a pivot table. This text can help people with vision or cognitive impairments to understand the PivotTable report.

Expanded See Also

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