A newer version of this page is available. Switch to the current version.

Pivot Table

  • 2 minutes to read

This document introduces the Pivot Table functionality, which 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 allows you to divide your data into categories and subcategories, and automatically calculates subtotals and grand totals using the most suitable summary function from a predefined list. For example, the image gallery below shows a table with raw data and a PivotTable report built based on this table.

Manage Pivot Tables in the User Interface

  • Insert a Pivot Table

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

    ASPxSpreadsheet_PivotTables_Insert

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

    ASPxSpreadsheet_PivotTables_Fields

  • 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 Analyze and Design contextual tabs, which appears when any cell in the pivot table is selected.

    ASPxSpreadsheet_PivotTables_AnalyzeTab

    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, 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 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, and configure style options.

    ASPxSpreadsheet_PivotTables_DesignTab

  • Explore the PivotTable Data

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