Pivot Tables

Posted by Denise on January 07, 2002 10:51 AM

I have a pivot table with two columns of data such as years 2000 and 2001. I want a column to the right of those two that tells me the difference between them. It seems like such a simple thing yet I can't figure out hwo to do it. Help!!

Posted by Chris D on January 07, 2002 1:46 PM

Hi Denise,

within your pivot table, right-click on the column heading (ie YEAR) and click on "insert"

this should bring up an "inserted calculated item" window rather than an error message if you just try to insert a normal column...

This new inserted item will probably default to a title of "formula one", to reflect that it recognises this as being the first new formula that is being inserted - you can change this at will when you get familiar with the function.

You will see that you have "fields" and "items" to choose from, so in your example, you will be deducting item 2001 from 2000 in the "year" field

so, where it says "formula = 0", backspace over the "0" up to the "=" then double click on "2001" then type a minus "-" then double click on "2000" then click on "add" then click on "okay"

now check out your pivot table and it should have a new column appear in it which deducts the value within 2000 from the value in 2001, ie the difference.

Basically all you've done is instruct the pivot table to include a user-defined column, equal to the value of 2001 less 2000

Hope this helps
Chris