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:
- Click inside the Pivot Table and select PivotTables Tools > Analyze > Fields, Items & Sets under the Calculations menu section.
- 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.
- This new field will display in your Pivot Table. You can now format the field as required
- 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.
- Right click on any cell inside this column and from the menu, select Show Values As > Running Totals In”.
- Select OK if the default Base Field is correct.
- 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.
