How can I add a calculated field to a pivot table that shows the difference between two series of data?

Question

My underlying pivot table has the following columns - ProjectName, Type, Year, Budget. The data shows information for 2009 and 2010 for the same ProjectName and Type. I can pivot this to get a table of the data but how can I add some calculated columns to show the difference between 2009 and 2010 for each entry?

Answer 1

Hopefully this can get you started, of course you can use different comparison fields than December 2002/2003 in the example:

To add data fields that calculate the difference between two data fields:

Add first data field:

1. Select a cell in the PivotTable report, and from the PivotTable toolbar, click the PivotTable icon and select Formulas, and then Calculated Field.
2. In the Insert Calculated Field dialog box, type the field name in the Name box.
3. In the Fields box, select December 2003.
4. Click Insert Field, and then type the minus (-) sign in the Formula box.
5. In the Fields box, select December 2002, click Insert Field, and then click OK.

Select a cell in the new calculated field (cell H7 for example) and click the Field Setting icon on the PivotTable toolbar, Click Number, and then change the field formatting.

Add second data field:

1. Select a cell in the PivotTable report, and from the PivotTable toolbar, click the PivotTable icon and select Formulas, and then Calculated Field.
2. In the Insert Calculated Field dialog box, type the field name in the Name box.
3. In the Fields box, select December 2003.
4. Click Insert Field, and then type the devide (/) sign in the Formula box.
5. In the Fields box, select December 2002, click Insert Field, and then click OK.

Select cell I7, repeat step 6, select Custom, and in the Type box change the field formatting to "0.00% ;Red".

Adding a Data Field That Calculates the Difference Between Two Data Fields.

Answer 2

I had bookmarked the this link on your topic. The technique is slightly cryptic, I have never tried it, but I know it is a common situation. Hopefully it may help you, me, or someone, sometime.

Answer 3

There's no easy way of doing this AFAIK.

You could set Year as a column field, and then right-click on the data field > Field Settings and change it from a normal Sum to 'Difference from' / Year / (previous). This is a fairly awful solution, as you'll notice when you try it, but it's all I've found so far.

The only other way I know is to add a simple spreadsheet formula to the column to the right of the table. An equally poor solution but it does work and might be suitable for your needs.

Answer 4

When wanting to find the difference between values in two different columns, it's best if your column header does not contain any spaces. If it does, the difference will not work because your Calculated field will look like

= 'Spaced Name'-'Name WithSpace'

Excel will then spit out junk.