Customize a pivot table

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, follow these 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 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.

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. The arrangement of fields in the Columns and Rows areas also determines the parent-child hierarchy of fields in the table: a field lower in the list is a child of the parent field above it. The hierarchy determines how fields are grouped in the 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 do the following:

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.

You can rename any field in the pivot table if needed.

To rename a field, follow the steps below:

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

2.From the drop-down menu, select Rename.

3.In the Rename dialog box, specify the name of the field and click OK.

The name of the field in the Constructor tab will change. When you hover the mouse cursor over a field, a tooltip with its current and original name will be displayed.

For a field that is added to the Values area, you can select a function for the main calculation and a function for an additional calculation.

The functions for the main calculation use data from the source table. Aggregating functions (summary functions in Excel) are used as such functions. Aggregating functions are used in statistics and data analytics to analyze and summarize information. Such functions include, for example, the sum, average, minimum and maximum values, and the number of elements for the selected data group.

When adding a field to the Values area, the function for the main calculation is selected automatically as follows:

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

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

The additional calculation functions use data from the value area of the pivot table. These functions allow to reduce time and effort when analyzing data and building reports, without the need for the third-party spreadsheet editors or manual methods for applying additional calculations.

When adding a field to the Values area, the function for additional calculation is not used by default. It can be selected manually as described below.

There are several ways to select a calculation function:

•Through the settings pane of the pivot table

•Through the context menu of the pivot table cells

To select a function for the main calculation via the settings pane, follow these steps:

1.Open the Constructor tab of the pivot table settings pane.

2.In the Values area, in the Calculations field, expand the drop-down list of functions corresponding to this field.

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

To select a function for the main calculation via the context menu, follow these steps:

1.Select a cell or range of cells with calculated values in the pivot table.

2.Open the context menu by right-clicking and run the Calculations > desired function.

To select a function for additional calculation via the settings pane, follow these steps:

1.Open the Constructor tab of the pivot table settings pane.

2.In the Values area, in the More field, expand the drop-down list of functions corresponding to this field.

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

To select a function for additional calculation via the context menu, follow these steps:

1.Select a cell or range of cells with calculated values in the pivot table.

2.Open the context menu by right-clicking and select More calculations > desired function.

If the function selected for additional calculation requires additional parameters, the More calculations window opens, in which:

•The Type field will indicate the function selected in the previous step. You can change it by selecting another function from the drop-down list. At the same time, if no additional parameters are required for the selected function, the Base field and Base value fields will become inactive.

•The Base field field specifies a field (column) in the table that contains the initial values used for grouping and analyzing data and relative to which the calculation will be performed. Any field that is located in the Rows or Columns area in the Constructor tab of the pivot table settings pane can be selected as the base field.

•In the Base value field, select the value that contains the Base field and relative to which the calculation will be performed. When selecting Next value or Previous value, each value is compared with the one following it or preceding it, respectively.

To edit additional parameters of the function being used, open the More calculations window by clicking on the green text highlighted under the More calculations field drop-down list in the Values area of the pivot table constructor.

To disable the function that is used for additional calculation, select the option No calculations when selecting a function, as described above.

When working with pivot tables created in third-party editors, the following additional calculations are view only:

•Index

•Rank Largest to Smallest

•Rank Smallest to Largest

•Running Total in %

•Running total

When updating the pivot table, such additional calculations will be reset.

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