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?

Solution 1

In Excel 2010,

• Put your cursor in one of the Year headings, either 2009 or 2010.
• Options > Fields, Items, Sets > Calculated Item

It's not a calculated field, it's a calculated Item within the Year field - that's the key point!

Solution 2

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:

6. Select a cell in the PivotTable report, and from the PivotTable toolbar, click the PivotTable icon and select Formulas, and then Calculated Field.
7. In the Insert Calculated Field dialog box, type the field name in the Name box.
8. In the Fields box, select December 2003.
9. Click Insert Field, and then type the devide (/) sign in the Formula box.
10. 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.

Comments

[Andrew Doran]

I'm afraid that doesn't help as the only fields I have are ProjectName, Type, Year, Budget. I want to subtract the budget for the same projectname and type combination for 2009 from the equivalent value for 2010.

[John T]

use 2010 - 2009?

[Andrew Doran]

What do you mean? I just have a field called 'Year'.

[Andrew Doran]

If I select one of the Year headings in Excel 2007 and then choose 'Calculated Item' from the ribbon I get an error message saying that "the item cannot be added or modified".

[Admin]

Adding a simple spreadsheet formula is the only 'solution' I've found so far. This isn't what I'm really looking for as these cells will not react well to any changes in the structure of the pivot table.