If you open the dropdown menu 
to the right of a field in the Values area in the lower areas section of the field list, you will find the option Value settings. Alternatively, you will find this option via the context menu by right-clicking directly in the Pivot table report (select a cell below the header title Sum of...).
The value settings dialog box contains the tabs Summarize values and Show values as, which provide you with various summary functions and calculation options.
Value settings, "Summarize values" tab
The results in the pivot table report are automatically displayed as a sum if a number field was identified in the Values area. If the field has values other than numbers, the results are calculated as a count.
You can customize the display of the results. The specified selection applies to the subtotals of the higher-level field and also to the subgrouped fields.
To do so, click on the dropdown menu to the right of the desired field in the Values area (in the areas section) and select the option Value settings. In the dialog box that opens, choose the Summarize values tab.
In the upper section of the tab, you will find "Summarize values by" with the following options for displaying the values:
▪Sum: The results of the individual values are displayed as a sum.
▪Count: This displays the number of individual values that occur. This works in the same manner as the COUNTA function.
▪Average: This displays the average value of the individual values that occur.
▪Maximum: This displays the highest individual value that occurs.
▪Minimum: This displays the lowest individual value that occurs.
▪Product: This displays the multiplied value of the individual values that occur.
▪Count numbers: This displays the number of values that are numbers. This works in the same manner as the COUNT function.
▪Standard deviation (sample): This provides an estimate of the standard deviation of a population based on a sample. This works in the same manner as the STDEV.S function.
▪Standard deviation (population): This returns the standard deviation of a population. This works in the same manner as the STDEV.P function.
▪Variance (sample): This provides an estimate of the variance of a population based on a sample. This works in the same manner as the VAR.S function.
▪Variance (population): This provides the variance of a population. This works in the same manner as the VAR.P function.
Tip 1: If you only want to change the subtotals to a specific display, you can make the required changes in the Row/Column labels area with the option Field settings. For more information, see Field settings (Row/Column labels area).
Tip 2: If you want to show or hide the grand total for columns/rows, you will find the options available under Pivot table settings.
Furthermore, you will find the option Custom name in the tab. Here, you can rename the field so that it appears in the pivot table with a different name. For more information, see also Working with the field list: Areas section.
Value settings, "Show values as" tab
In the Show values as tab, you will find several options for displaying the results of the pivot table, such as percentage values.
Tip: You can also drag the same field several times into the Values area. If you then apply different output settings to the copy of the field using Show values as (for example, standard values for one field and percentage values for the other field), you can clearly compare the different output values in the pivot table report.
In the upper section of the tab, you will find the section Show values as with options for calculating the values. Depending on the calculation type, additional selectable entries appear in the Base field and Base item lists below.
▪Standard (no calculation): This displays the value as usual.
▪% of grand total: Each item is displayed as a percentage of the grand total.
▪% of column total: For each column, the total is set as a 100% reference, and each item in the column is shown as a percentage of that total.
▪% of row total: For each row, the total is set as a 100% reference, and each item in the row is shown as a percentage of that total.
▪% of: A column or row field is set as a 100% reference, and all other values are shown as a percentage of this reference value. The desired entries must be selected from the Base field list and Base item list.
▪% of parent row total: This displays the percentage of an item relative to the higher-level item (parent) in the row structure.
▪% of parent column total: This displays the percentage of an item relative to the higher-level item (parent) in the column structure.
▪% of parent total: This displays the percentage of an item relative to any (selectable) higher-level item in the structure. The desired entry must be selected from the Base field list.
▪Difference from: This displays the absolute difference of the values from the selected reference value. The desired entries must be selected from the Base field list and Base item list.
▪% difference from: This displays the percentage difference of the values from the selected reference value. The desired entries must be selected from the Base field list and Base item list.
▪Running total in: This displays the cumulative absolute values of the successive items. The desired entry must be selected from the Base field list.
▪% running total in: This displays the cumulative percentage values of the successive items. The desired entry must be selected from the Base field list.
▪Rank smallest to largest: This displays the rank of selected values in a specific field. The smallest item in the field is ranked 1. The desired entry must be selected from the Base field list.
▪Rank largest to smallest: As above, but the largest item in the field is ranked 1.
▪Index: This displays the weighting of the values in relation to the grand row total and grand column total. The higher the index value, the greater the weighting of the value on the grand row/column total. The following formula applies to the index value:
| value in cell x grand total of the pivot table grand row total x grand column total |
You can use the Number format button on this tab to permanently adjust the display of your desired number format. For information on the various number formats, see List of number formats.
Note: Changes to the Number format will only be permanently retained if you do so here. If you change the number format using the cell formatting only, the changes are discarded when the data is refreshed. The number formatting of the source range is also not adopted in the pivot table.