Excel Pivot Table Report – Summary Functions & Custom Calculations, Insert Calculated Fields or Calculated Items
As applicable to Excel 2007
Excel Pivot Tables Tutorial:
2. ‘PivotTable Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing & Data.
5. Group Items in a Pivot Table report, Group Data, Group Date and Time Values, Grouped Field.
6. Pivot Table report: Summary Functions & Custom Calculations, Value Field Settings, Summarize Pivot Table Data
Refer complete Tutorial on working with Pivot Tables using VBA:
Create and Customize Pivot Table reports, using vba
Calculating the Value Fields in a Pivot Table report
Calculations are a great way to do data analysis and comparisons in a Pivot Table report. For calculating the Value Fields, you can choose any of the Summary Functions or use any Custom Calculations in the Value Field Settings of a Pivot Table, or create your own formulas by inserting Calculated Fields or Calculated Items.
Value Field Settings in a Pivot Table Report
You can choose the type of calculation that you want to use to summarize the data from the selected field. A Pivot Table report created initially from the source data, shows the value field as ‘Sum of Sales’ (‘Sum’ being the default calculation for a value field with numbers) for the field name of ‘Sales’. You can choose calculation options for a value field in the ‘Value Field Settings’ dialog box. To open the dialog box, click the down arrow of a value field in the ‘Values’ area in Areas Section of the ‘PivotTable Field List’ Pane, and then click on ‘Value Field Settings’ in the list of commands. Another way to open the dialog box is to right click a cell in the values field of the Pivot Table report, and click on ‘Value Field Settings’ in the list of commands. In the dialog box, the relevant value field name appears at the top as ‘Source Name’. In the ‘Custom Name’ box, you can enter or edit the name you want to display in the report.
1. SUMMARY FUNCTIONS:
In the ‘Value Field Settings’ dialog box, under the ‘Summarize by’ tab, the ‘Summarize value field by’ section has a list of functions from which you can choose the type of calculation with which you want the Value Field to be summarized. Following is the list of functions that can be used:
Sum: Calculates SUM of values viz. to display the sum of sales revenues for past 3 years, in re of a particular car model. For numeric data, this is the default calculation. Refer Image 1a for Source Data and Image 1b for Pivot Table report.
—————————————————————————————————————————
Count: Counts the number of records in a particular category (viz. to display the number of records/items in a field for a particular item, say the number of ‘Red’ appearing in the Color Field for a particular car model). For non-numeric data (like Text), this is the default calculation. Refer Image 2a for Source Data and Image 2b for Pivot Table report.
—————————————————————————————————————————
Average: Calculates AVERAGE (ie. mean) of values viz. to calculate the average of past 3 years’ sales revenues, in re of a particular car model. Refer Image 1a for Source Data and Image 3 for Pivot Table report.
—————————————————————————————————————————
Max: Displays the largest numeric value in the Value Field. Refer Image 1a for Source Data and Image 4 for Pivot Table report.
—————————————————————————————————————————
Min: Displays the smallest numeric value in the Value Field. Refer Image 1a for Source Data and Image 5 for Pivot Table report.
—————————————————————————————————————————
Product: Calculates the product of the values. Note that this function multiplies all values in the value area and hence needs to be used carefully in certain instances only. Refer Image 6a for Data Source and Image 6b for Pivot Table Report.
—————————————————————————————————————————
Count Numbers: Counts the number of numeric values in the Values field. Omits Text values. The ‘Count’ Function counts both text and numeric values. In the above example of Count, refer Image 2a & 2b. If you use the ‘Count Numbers’ for source data in Image 2a, the Pivot Table report will display zeros in the value field, because of all text values. For Source Data per Image 7a, refer Image 7b for ‘Count Numbers’ function Pivot Table report and Image 7c for ‘Count’ function Report.
—————————————————————————————————————————
StdDev: Standard Deviation is a measure of variation from the average value. It shows how widely values are dispersed from their mean value. If data represents a sample of the population, then compute the standard deviation with the StdDev function; if data represents the entire population, then compute the standard deviation with the StdDevp function. Standard Deduction is calculated as ‘the square root of Variance’ (see Var below to see how Variance is calculated). Note that a minimum of 2 items are required to calculate Standard Deviation, else a #DIV/0! error is returned. Refer Image 8a wherein Source Data is range “A3:D10” and columns F & G show the respective Standard Deductions using Excel function (STDEV). Refer Image 8b for Pivot Table report.
—————————————————————————————————————————
StdDevp: Refer Image 9a wherein Source Data is range “A3:D10” and columns F & G show the respective Standard Deductions using Excel function (STDEVP), and columns H, I & J show Standard Deduction calculations done manually. Refer Image 9b for Pivot Table report.
—————————————————————————————————————————
Var: Both Standard Deviation and Variance are measures of variability. Both are measures as to what extent are a set of numbers spread out. Variance is computed as ‘the average of the squared differences from the average’. Standard Deduction is the square root of Variance. If data represents a sample of the population, then compute the variance with the Var function; if data represents the entire population, then compute the standard deviation with the Varp function. Note that a minimum of 2 items are required to calculate Variance, else a #DIV/0! error is returned. Refer Image 10a wherein Data Source is range “A3:D10” and columns F & G show the respective Variance using Excel function (VAR). Refer Image 10b for Pivot Table report.
—————————————————————————————————————————
Varp: Refer Image 11a wherein Source Data is range “A3:D10”, and columns F & G show the respective Variance using Excel function (VARP), and columns H & I show Variance calculations done manually. Refer Image 11b for Pivot Table report.
—————————————————————————————————————————
2. CUSTOM CALCULATIONS:
Pivot Table Data is summarized by functions like Sum, Count, Average, …. and there are different methods (referred to as custom calculations) as to how the Values can be shown. In a custom calculation, values are calculated as based on other items. After selecting a function to summarize the value field (viz. Sum, Count, Average, …) in the ‘Summarize by’ tab, click the ‘Show values as’ tab which has a drop-down list of custom calculations which can be used (viz. Normal, Difference From, % of, …), and on selecting an option, you will be able to select a ‘Base field’ and then a ‘Base item’. Following is a list of Custom Calculations which can be applied:
Normal: There is no custom calculation.
—————————————————————————————————————————
Difference From: Custom calculation of values as difference from the value of the selected Base Item in the Base Field. We show an example wherein data has been summarized by using the ‘Sum’ function viz. ‘Sum of Sales’ – Refer Image 12a wherein Source Data is in range “A3:D12”, and manual calculations show: (i) Difference From: Base Field ‘Car Models’; Base Item ‘Compact’ (in column F); and (ii) Difference From: Base Field ‘Car Models’; Base Item ‘(next)’ (in Column G). Refer Image 12b which shows the Pivot Table Report with custom calculation per (i) and Image 12c which shows the Pivot Table report with custom calculation per (ii).
—————————————————————————————————————————
% Of: Custom calculation of values as percentage of the value of the selected Base Item in the Base Field. We show an example wherein data has been summarized by using the ‘Sum’ function viz. ‘Sum of Sales’ – Refer Image 13a wherein Source Data is in range “A3:D12”, and manual calculations show % Of: Base Field ‘Car Models’; Base Item ‘MidSize’ (in column F). Refer Image 13b which shows the Pivot Table report with custom calculation % Of: Base Field ‘Car Models’; Base Item ‘MidSize’.
—————————————————————————————————————————
% Difference From: Custom calculation of values as percentage difference from of the value of the selected Base Item in the Base Field. We show an example wherein data has been summarized by using the ‘Sum’ function viz. ‘Sum of Sales’ – Refer Image 14a wherein Source Data is in range “A3:D12”, and manual calculations show % Difference From: Base Field ‘Region’; Base Item ‘North America’ (in column F). Refer Image 14b which shows the Pivot Table report with custom calculation % Difference From: Base Field ‘Region’; Base Item ‘North America’.
—————————————————————————————————————————
Running Total In: Custom calculation of values as a running total in the selected Base Field. We show an example wherein data has been summarized by using the ‘Sum’ function viz. ‘Sum of Sales’ – Refer Image 15a wherein Source Data is in range “A3:D12”, and manual calculations show Running Total In: Base Field ‘Region’ (in column F). Refer Image 15b which shows the Pivot Table report with custom calculation Running Total In: Base Field ‘Region’.
—————————————————————————————————————————
% of Row: Custom calculation of values as a percentage of row total – shows values as a percentage of the total of a row item. We show an example wherein data has been summarized by using the ‘Sum’ function viz. ‘Sum of Sales’ – Refer Image 16a wherein Source Data is in range “A3:D12”, and manual calculations show % of Row (in column F). Refer Image 16b which shows the Pivot Table report with custom calculation % of Row.
—————————————————————————————————————————
% of Column: Custom calculation of values as a percentage of column total – shows values as a percentage of the total of a column item. We show an example wherein data has been summarized by using the ‘Sum’ function viz. ‘Sum of Sales’ – Refer Image 17a wherein Source Data is in range “A3:D12”, and manual calculations show % of Column (in column F). Refer Image 17b which shows the Pivot Table report with custom calculation % of Column.
—————————————————————————————————————————
% of Total: Custom calculation of values as a percentage of grand total of all values in Data Area. We show an example wherein data has been summarized by using the ‘Sum’ function viz. ‘Sum of Sales’ – Refer Image 18a wherein Source Data is in range “A3:D12”, and manual calculations show % of Total (in column F). Refer Image 18b which shows the Pivot Table report with custom calculation % of Total.
—————————————————————————————————————————
Index: Custom calculation of values as: (Cell Value * Grand Total) /(Row Total * Column Total). We show an example wherein data has been summarized by using the ‘Sum’ function viz. ‘Sum of Sales’ – Refer Image 19a wherein Source Data is in range “A3:D12”, and manual calculations show Index (in column F). Refer Image 19b which shows the Pivot Table report with custom calculation Index.
—————————————————————————————————————————