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 box, expand the drop-down list of functions corresponding to this box.
3.Select the desired function from the drop-down list.
To select a function for the 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.Right-click to open the context menu and run the More calculations > <Function name> command.
If the function selected for additional calculation requires additional parameters, the More calculations window opens, in which:
•The Type box 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 boxes will become inactive.
•The Base field box 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 on the Constructor tab of the pivot table settings pane can be selected as the base field.
•In the Base value box, 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.
The functions used for additional calculations are described in the table below.
Additional calculation |
Description |
|---|---|
% of grand total |
Calculates the value in the cell as a percentage of the total of all values in the pivot table. |
% of column total |
Calculates the value in the cell as a percentage of the total for the corresponding column. |
% of row total |
Calculates the value in the cell as a percentage of the total for the corresponding row. |
% of |
Calculates the value in the cell as a percentage of the selected base value in the corresponding base field. |
% of parent row total |
Calculates the value in the child cell as a percentage of the value in the parent cell in the same column. This can be useful if more than one field is added to the row area of the pivot table to create a parent-child hierarchy. |
% of parent column total |
Calculates the value in the child cell as a percentage of the value in the parent cell in the same row. This can be useful if more than one field has been added to the column area of the pivot table to create a parent-child hierarchy. |
% of parent total |
Calculates the value in the child cell as a percentage of the value in the parent cell in the corresponding base field. |
Difference |
Calculates the difference between the value in the cell and the selected base value in the corresponding base field. The result of this type of calculation is affected by the sorting applied on the totals for columns and rows. |
Difference in % |
Calculates the percentage difference between the value in the cell and the selected base value in the corresponding base field The result of this type of calculation is affected by the sorting applied on the totals for columns and rows. |
To edit the additional parameters for the function being used, open the More calculations window by clicking in the Values area of the pivot table constructor on the green highlighted text below the More box drop-down list .
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.