Skip to main content

Customize pivot table

You can customize pivot tables using the special Pivot table pane. This pane opens automatically when you select any cell within the pivot table and contains two tabs: Constructor for managing the structure and Options for changing the name, layout, and other settings.

Open or close the Pivot table pane​

  1. Select any cell in the pivot table.
  2. In the sidebar, click the Pivot table button.

You can also use the Pivot table settings tool in the context menu.

Customize the pivot table structure​

  1. On the Constructor tab, hover over the desired field in the Available fields list.
  2. Drag and drop the field into the desired area on the right.

Available areas:

  • Rows: for row headers (categories).
  • Columns: for column headers.
  • Values: for data to aggregate (sum, count, etc).
  • Filters: for filtering the entire report.

You can also use:

  • The + button in the row of the desired field to add a text field to Rows, a numeric field to Values.
  • The Move to... tool in the context menu of the field in the Available fields list.

Manage pivot table fields​

  • To change the order of fields, drag the field up or down within the area.
  • To rename a field, use the Rename tool in the field context menu.
  • To delete a field, hover over the field in the area and click the Delete button, or click the – Delete button in the field row in the Available fields list to delete it from all areas.

Change the data source for a pivot table​

  1. On the Constructor tab, click the Select data source button next to the Source field.
  2. Enter a new range or select it with the mouse.
  3. Click OK.

Configure calculations for pivot table fields​

  1. On the Constructor tab, in the Values area, find the field you want.
  2. In the Calculations list, select a new function (for example, Average, Maximum).
  3. If needed, select More calculations. These calculations are reset when the data is updated.

You can also use the Calculations and More calculations tools in the context menu of a pivot table cell.

Customize the layout and totals of a pivot table​

  1. On the Options tab, select Pivot table layout: Compact (hierarchical indents), Tabular (separate columns), or Outline (staggered columns).
  2. In the Grand totals section, enable or disable the options For rows and For columns. You can also use the Show grand totals tool in the pivot table cell context menu.
  3. In the Subtotalssection, enable or disable the Display option and select Under the group or In group header. In the Tabular layout, subtotals are always displayed under the group.

Customize pivot table captions​

  1. On the Options tab, in the Captions section, click the field you want.
  2. Enter new text or delete all text.
  3. Press Enter.