Customize a pivot table

The pivot table configuration pane opens automatically when you select any cell in the pivot table and collapses automatically when you select any cell outside the pivot table.

You can also open and collapse the pivot table configuration pane manually.

To collapse the pane manually, do one of the following:

•In the upper part of the pane, click .

•On the Sidebar, click  Pivot Table.

To open the pane manually, do the following:

1.Select the entire range or one or multiple cells of the pivot table range.

2.Open the pane in one of the following ways:

•In the Command menu, select Data and click Pivot Table Settings.

•On the Toolbar, select the Pivot Table section and click  Settings.

•On the Sidebar, click  Pivot Table.

•Right-click the context menu and select Pivot Table Settings.

By default, a pivot table does not contain data because its structure is not defined. To define the structure, use the Constructor tab in the pivot table configuration pane.

The Constructor tab includes the following sections:

•Available Fields: This section includes the list of all selected columns of the initial table (hereinafter, the fields). To set the structure of the pivot table, add the fields from the Available Fields section to the Rows, Columns, Values, and Filters sections.

•Rows: Add the fields which will become the headings of the pivot table rows to this section.

•Columns: Add the fields which will become the headings of the pivot table columns to this section.

•Values: Add the fields whose values will be used for calculations to this section.

•Filters: If necessary, add the fields that will be used to filter data in the pivot table to this section. Filters allows you set the specific level of data display and change the appearance of the table.

Add fields

Fields from the Available Fields section can be added to the Rows and Values sections manually or automatically. If fields are added automatically, they are allocated between the Rows and Values areas according to the data type:

•If a field contains text values, it will be moved to the Rows section.

•If a field contains numeric values, it will be moved to the Values section.

To automatically distribute fields between the Rows and Values sections, perform the following steps for each of these fields:

1.In the Available Fields section, hover the cursor over the title of the field which needs to be moved to the Rows or Values sections.

2.Click Add.

To add fields to the Rows and Values sections manually, drag and drop them into the appropriate sections while holding down the left mouse button, or do the following for each field:

1.In the Available Fields section, hover the cursor over the field which needs to be moved to the Rows or Values sections.

2.Click  More.

3.In the menu that appears, select the section in which you want to add the field.

Adding fields from the Available Fields section to other sections is done manually, similar to adding fields to the Rows and Values sections.

Moving a field from one section to another

Moving fields from one section to another is done manually, similar to adding fields from the Available Fields section to the other section in the Constructor.

Change the field order in a section

In any section other than the Available Fields section, you can change the order of the fields in the list. The position of the field in the list determines the position of the corresponding field in the pivot table.

To change the position of a field relative to other fields in the list, drag it to the desired location in the list while holding down the left mouse button, or perform the following steps:

1.Hover the mouse cursor on the field and click  More.

2.In the drop-down menu, select the desired command:

•Move to Beginning: Move the field to the beginning of the list.

•Move Up: Swap the selected field and the previous field.

•Move Down: Swap the selected filed and the next field.

•Move to End: Move the field to the end of the list.

The drop-down menu commands depend on the position of the selected field in the list.

Edit the function in the Values section

The function for data calculation for a field added to the Values section is selected automatically.

•If a field contains numeric data, the Sum function is selected.

•If a field contains text data, the Count function is selected.

To use another function for the data in this field, do the following:

1.In the Values section, expand the drop-down list of functions available for this field.

2.Select the desired function from the drop-down list.

Delete a pivot table field

To delete a field from any section of the pivot table, do one of the following:

•Hover the mouse cursor over the title of this field and click  Remove.

•Hold down the left mouse button and drag the field from the current area to the Available Fields section.

To remove a field from all areas of the pivot table to which it is added, do the following:

1.In the Available Fields section, hover the cursor over the title of the field.

2.Click Remove from Pivot Table.

If you need to edit the pivot table data source, do the following:

1.In the pivot table configuration pane, select the Options tab.

2.Place the cursor on the Data Source boх. The tab containing the initial table will open. The value in the Data Source box will become available for editing. The specified data range will be highlighted in color in the initial table.

3.Edit the value in the Data Source box and click or the Enter on the keyboard.