How to add a variance and running total in a Pivot Table

In our example workbook, we’re working with Date, Value In and Value Out fields. We need to create a Pivot Table that will also display the variance per month, as well as a running total of the variance for the year. In this tip, we show you how to do this.

First up; select your data and create a Pivot Table as normal.

To create the additional fields:

  1. Click inside the Pivot Table and select PivotTables Tools > Analyze > Fields, Items & Sets under the Calculations menu section.

  1. An Insert Calculated Field window will pop-up. Give the field a name, enter your variance formula and select Add.  In our example we want to know the variance between our Value In and Value Out values.

  1. This new field will display in your Pivot Table. You can now format the field as required

  1. To calculate a Running Total of the Variance field, drag the Monthly Variance field under the existing Monthly Variance field in the Value section of the PivotTable. You should now have a duplicate in your PivotTable, which can be renamed.

  1. Right click on any cell inside this column and from the menu, select Show Values As > Running Totals In”.

  

  1. Select OK if the default Base Field is correct.

  1. The column has now been converted and your report displays the Variance per month as well as a Running Total for the year

And that’s it! A quick and easy way to create a Pivot Table that will also display the variance per month, as well as a running total of the variance for the year.