Excel PivotTable Training Course

Calculated Fields and Items 

PivotTables enable you to analyse your data efficiently but some questions can only be answered by performing calculations on the data used to create the PivotTable. To perform that type of analysis you can create custom fields which summarise PivotTable data using a formula. Without calculated fields, you would have to copy the data from the PivotTable and paste it onto another worksheet and create the formula there which is a pain.  

The button to create a calculated field is found on the Options contextual tab in the Calculations group, Click the “Fields, Items & Sets” drop down and the Calculated Field button.  

When you click Calculated Field the Insert Calculated Field Dialogue box appears: 

You can now give your calculation a name, such as Average Sale, and create the formula. Click into the formula box and delete the 0. Now insert fields and any mathematical operators you need. For example: 

Excel adds the average sale field to the body of the PivotTable, building a further analysis into the table. Note that even though the newly calculated field appears in the PivotTable field list, it hasn’t been added to the original data source. The new field only exists in the PivotTable’s data cache – its temporary memory. Calculated fields extend the type of analysis you can perform in Excel, making PivotTables even more useful.  

Calculated Item 

Let’s say we want to insert a new item in a Product drop-down say, Home & Electronics. This particular field, when selected from the drop-down, should show us the combined totals for both Home Décor and Electronics. Select either the particular row field or the column field in which you would like to insert a new item. Click on the particular field (where you would like to insert a calculated item) in the PivotTable and then click the “Options” tab in the ribbon. Click the “formulas” button and select “Calculate Item”. 

You can now either write your own formula or use the existing items in that field and insert them by double-clicking. In our case, we will insert a new item by writing something like =’Electronics’+'Home Décor’ 

Let’s provide it with the name that we decided earlier – “Electronics & Home”. Press OK. What this will do is to insert a new calculated field, one which would show the combined sum of Electronics and Home Décor. If you look at the PivotTable now, you will see that under the Product field, a new item has been inserted and which sums up the relevant figures both from Electronics and Home Décor. 

Another interesting way to create calculated items in a PivotTable is the use of relative references. In the above example, we clubbed two items into a single new item – “Electronics & Home” which contained the totals for both the fields. In the Calculated Field creation box, we could have written = Product[-3] + Product[-2] instead of =’Electronics’+'Home Décor’ (assuming that these two fields immediately precede the new calculated field that we are creating. The [-3] and [-2] actually tell the application to pick up and sum the preceding two fields. 

A parameter value of [-1] would have made the application pick an item 1 step backwards and a value of [5] would make it pick up an item 5 steps forward. Again like the calculated field, a calculated item can use operators like +,-, / and * and other relevant excel formulas. For example a calculated item formula of =IF(Product[-1]>23000000,1,0) +Product[-2] would add 1 to Product[-2] if the total value for Product [-1] exceeds 23,000,000. 

Managing & maintainingPivotTable calculations 

When you find yourself maintaining and managing your PivotTables through changing requirements and growing data, you may find the need to maintain and manage your calculated fields and calculated items as well.  

Editing and Deleting Your PivotTable Calculations 

Activate the Insert Calculated Field or Insert Calculated Item dialogue and select the Name drop-down. Once selected you thenhave the option of deleting or modifying the item or field.  

Changing the solve order of your calculated items 

If the value of a cell in your PivotTable is dependent on the results of two or more calculated items, you can specify the order in which the individual calculations are performed.  

To get to the Solver Order dialogue box place your cursor anywhere in the PivotTable, select Formulas on the Tools group and then select Solve Order.  

The Solve Order dialogue box lists all the calculated items that currently exist in your PivotTable. Select any of the calculated items and using the Move Up and Move Down buttons, rearrange the order of calculations as required. 

Documenting Your Formulas 

To list your PivotTable calculations, simply place your cursor anywhere in the PivotTable and select Formulas and then select List Formulas. Excel creates a new tab in your workbook listing the calculated fields and calculated items in the current PivotTable.