Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas
As applicable to Excel 2007
Excel Pivot Table Tutorial:
2. ‘PivotTable Options’ dialog box – Layout & Format, Totals & Filters, Display, Printing & Data.
5. Group Items in a PivotTable report, Group Data, Group Date and Time Values, Grouped Field.
7. PivotTable report: Insert Calculated Fields, Calculated Items, Create Formulas, Use Index Numbers, Solve Order of Calculated Items
Refer complete Tutorial on working with Pivot Tables using VBA:
Create and Customize Pivot Table reports, using vba
Calculating the Value Fields in a PivotTable report:
Calculations are a great way to do data analysis and comparisons in a PivotTable 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’.
Create Formulas – Insert Calculated Fields or Calculated Items in the PivotTable
To manage calculations which are beyond ‘Summary Functions’ and ‘Custom Calculations’, you can create your own formulas by inserting ‘Calculated Fields’ or ‘Calculated Items’. Say, for calculating Difference or %age Difference, custom calculations may be based on values of other items within the same field, but to compare different fields, you can create your own formula by inserting ‘Calculated Fields’.
Formula Rules: Operators and Expressions (mathematical, comparison, logical and reference) can be used like those used in worksheet formulas but cell references or named ranges cannot be used. Constants, like a number (viz. 55) or a percentage (viz. 10%), can be used. Array functions are not allowed. You can use the mathematical operators such as +, –, /, *; comparison operators like =, >, <, >=, <=, <>; excel functions like OR, AND, NOT, IF, SUM, AVERAGE, ….
Insert Calculated Field – Calculate difference (both $ and %) between estimated sales and actual sales:
To Insert a Calculated Field, click ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon, then click ‘Formulas’ in the ‘Tools’ group, then click ‘Calculated Field’ in the list of commands which opens the dialog box of ‘Insert Calculated Field’. Enter Name and Formula here. For example, consider 2 fields of ‘Sum of Actual Sales ($)’ and ‘Sum of Estimated Sales ($)’. Enter the Name as ‘Variation’ and enter Formula as =’Actual Sales ($)’ -‘Estimated Sales ($)’. While entering the Formula, begin with an equal sign, select a Field(s) from the pulled-down list, double-click on the field name or click on ‘Insert Field’. In the Pivot Table report, a new column is inserted with the label ‘Sum of Variation’. This label should appear in the Values Area of the Areas Section of the right-hand panel wherein you can click the down arrow and open the ‘Value Field Settings’ dialog box. The Source Name will show as ‘Variation’, and in the ‘Custom Name’ box, we change the display name to ‘Variation ($)’. Similarly, we insert another Calculated Field with the name ‘%age Variation’, Formula: =(‘Actual Sales ($)’-‘Estimated Sales ($)’)/’Estimated Sales ($)’, and the Custom Name as ‘Variation (%)’. Refer Image 20a wherein Source Data is in range “A3:E12”, and manual calculations show ‘Variation ($)’ & ‘Variation (%)’ (in columns G & H). Refer Image 20b which shows the PivotTable report with the calculated fields inserted in columns ‘Variation ($)’ & ‘Variation (%)’, and also shows the ‘Insert Calculated Field’ dialog box .
Insert Calculated Item – separate calculation/formula for different items in a field:
For calculations between Items within a Field, and have a separate calculation for different items, you can create your own formulas by inserting ‘Calculated Items’. A custom calculation will remain standard across all items. Note: A calculated item is inserted in a particular field, and the ‘PivotTable formulas can only refer to items in the same field as the calculated item’. To Insert a Calculated Item, select a row/column label or a row/column label item in the Pivot Table Report and click ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon, then click ‘Formulas’ in the ‘Tools’ group, then click Calculated Item. This will open the dialog box ‘Insert Calculated Item’. Subsequent steps are similar to inserting a calculated field.
Each cell of a calculated item can have a different formula. You can change the formula for a specific cell(s) of a calculated item, by selecting the particular cell and then entering the new formula in the formula bar. Refer Image 22, wherein the calculated item ‘E-Profit’ has the formula =Europe*7%. By selecting the cell G6 and changing its formula to =Europe*9%, the value of this cell will change to 171 from 133.
Example 1: Suppose you want to do 3 simultaneous comparisons: compare Sales in North America vis-a-vis South America, Sales in North America vis-a-vis Europe, and Sales in Europe vis-a-vis South America, you can Insert Calculated Items in the Pivot Table. Refer Image 21, which shows the PivotTable report (with calculated items inserted) and the ‘Insert Calculated Item’ dialog box.
Example 2: To calculate Profit from car sales in each Region – North America Profit is 10% of Sales, South America Profit is 8% of Sales and Europe Profit is 7% of Sales, refer Image 22 which shows the PivotTable report (with calculated items inserted) and the ‘Insert Calculated Item’ dialog box.
Example 3: Refer Image 23 which shows the PivotTable report and also shows ‘Insert Calculated Item’ dialog box. The image shows the inserted calculated item of “Commission” with the formula =IF(‘Actual Sales’-‘Estimated Sales’>5000,’Actual Sales’*20%,IF(‘Actual Sales’ -‘Estimated Sales’ >0,’Actual Sales’ *10%, 0)). If Actual Sales exceed Estimated Sales by over $5,000, then commission paid is 20% of Actual Sales, if Actual Sales exceed Estimated Sales by less than $5,000, then commission is 10% of Actual Sales, if Actual Sales do not exceed Estimated Sales, then no Commission.
Ref Example 3, some valid formulas which could be used include:
=IF(AND(‘Actual Sales’-‘Estimated Sales’>1000,’Actual Sales’ >=20000), ‘Actual Sales’*20%,0)
=IF(NOT(‘Actual Sales’-‘Estimated Sales'<0), ‘Actual Sales’*20%,0)
Use Index Numbers to refer to Items while inserting calculated fields:
We have thus far been referring to items by their names, while inserting calculated fields. You can also refer to items by index numbers which determine an item in 2 ways: (i) either by its position in the PivotTable; or (ii) by its relative position in the PivotTable. To determine an item by its position using an index number, use the format Year[1] or Year[2] where Year is the name of the field and the numbers 1 or 2 represent the column number (or row number) in the Pivot Table, where the item to be included in the formula is positioned (Note: the index number does not count hidden items). To determine an item by its relative position, using an index number, use the format Year[+1] or Year[+2] where Year is the name of the field and the numbers 1 or 2 represent the number of columns to the right (or number of rows below) of where the item to be included in the formula is positioned, relative to the inserted calculated field. Index numbers are used ideally when the item name (viz. year, month, etc) change often but the calculations are based on a constant item order. See below example which explains this explicitly.
Refer Images 24a and 24b. Four calculated items have been inserted in the PivotTable: (i) item 2010 (E) with formula =Year[+1]*108% – this calculates the 2010 estimated sales as 108% of 2009 sales which is one column to its right; (ii) item 2011 (E) with formula =Year[+1]*109.5% – this calculates the 2011 estimated sales as 109.50% of 2010 estimated sales which is one column to its right; (iii) item 2010/2009 with formula =Year[4]/Year[5] – this calculates the growth percentage of 2010 sales (in column 4 of the Pivot Table) over 2009 sales (in column 5 of the PivotTable); and (iv) item 2011/2010 with formula =Year[3]/Year[4] – this calculates the growth percentage of 2011 sales (in column 3 of the PivotTable) over 2010 sales (in column 4 of the Pivot Table). Note: The Grand Total column for the calculated items 2010/2009 & 2011/2010 do not show correctly – these values should be 108% and 109.5% respectively – this can be corrected as explained in the heading of ‘Solve Order of Calculated Items’.
Solve Order of Calculated Items:
If your Pivot Table has multiple ‘Calculated Items’, you can modify the order in which calculations will be done. This is particularly applicable in case of different type of calculations for a calculated item in a column field and for a calculated item in a row field, in which case the cell at the intersection of both these calculations could be shown as a result of either of the calculations. In this case the value of the cell is determined by the last formula in the solve order. To adjust the order of the calculation, click ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon, then click ‘Formulas’ in the ‘Tools’ group, then click ‘Solve Order’. This will open the dialog box ‘Calculated Item Solve Order’. Select a formula, and then click Move Up or Move Down buttons.
We take an example where two calculated items (in the column field) have been iserted in the PivotTable: (i) item “2011-2009 (%)” with the formula =’2011′ /’2009′; and (ii) item “MEDIAN” with the formula =MEDIAN(‘2009′,’2010′,’2011’). Refer Image 25a which shows the Grand Total, but this is wrong in case of the 2 inserted items – the Grand Total should show 111.70% for the calculated item “2011-2009 (%)” and 4,700 for the calculated item “MEDIAN”. In the next step (refer Image 25b) we delete “Grand Total” and insert the calculated item ‘Total’ in the row field, with the formula =SUM(SubCompact,Compact,MidSize). The values shown in the ‘Total’ item (which appears last in the solve order) are still in error for the calculated items of ‘2011-2009 (%)’ & ‘MEDIAN’. In the last step (refer Image 25c), we change the solve order so that the ‘Total’ calculated item appears first and the %age and Median calculations are done thereafter. Now the values in the “Total” item show correctly – 111.70% for the calculated item ‘2011-2009 (%)’ and 4,700 for the calculated item ‘MEDIAN’.
Edit or Delete a Formula in a ‘Calculated Field’ or ‘Calculated Item’:
You can edit a formula for a calculated field or calculated item by opening the dialog box ‘Insert Calculated Field’ or ‘Insert Calculated Item’. Select the formula name, enter the new formula and click on Modify, and then click OK which will close the dialog box. To Delete a formula, click on ‘Delete’ after selecting the formula name, instead of ‘Modify’, other steps remaining the same.
A Calculated Item can have different formulas for specific cells:
As already explained above, each cell of a calculated item can have a different formula. You can edit the formula for a specific cell(s) of a calculated item, by selecting the particular cell and then entering the new formula in the formula bar. Refer Image 22, wherein the calculated item ‘E-Profit’ has the formula =Europe*7%. By selecting the cell G6 and changing its formula to =Europe*9%, the value of this cell will change to 171 from 133.
Display a List of all Formulas used in a Pivot Table:
To get a List of all formulas used in the PivotTable, click ‘Options’ tab under the ‘PivotTable Tools’ tab on the ribbon, then click ‘Formulas’ in the ‘Tools’ group, then click ‘List Formulas’. This will open a new worksheet, displaying a list of all formulas used.