Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

5 Answers 5

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!

share|improve this answer
1  
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". –  Andrew Doran Sep 4 '12 at 22:46

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.

share|improve this answer

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.

share|improve this answer
    
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. –  Andrew Doran Sep 4 '12 at 22:48

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.

share|improve this answer

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.

share|improve this answer
    
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. –  Andrew Doran Sep 30 '09 at 12:02
    
use 2010 - 2009? –  John T Sep 30 '09 at 12:17
2  
What do you mean? I just have a field called 'Year'. –  Andrew Doran Sep 30 '09 at 12:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.