Excel Pivot Table Question 1

[dianemarie]

I have a fairly simple pivot table that compares quantities from two different sources to see if there is any discrepancies between the two databases. So the completed pivot table looks like this:

Database A Database B Total
Order A $1000 $1000 $2000
Order B $500 $500 $1000
Order C $450 $432 $882

But I would like to see the Total column, which the pivot table generated, calculated as a difference instead of a total (Database A - Database B instead of Database A + Database B). Right now I'm copying and pasting values and then inserting my own formula to check for any difference not equal to 0 (thereby pointing me to the orders where there is a discrepancy between the two databases). Is this possible using the pivot table wizard? Thank you.

 

[GeekGirlau]

This is known as a calculated item in the pivot table. The first step is to ungroup your Database column. Click on the heading "Database B", click the right mouse button over your pivot table and select Group and Outline, Ungroup.

Leaving "Database B" selected, click the right mouse button again and select Formulas, Calculated Item. Type a name for your item (eg "Difference&quot. Under Fields, select "Database A" then Insert Item. Type the minus sign, then under Fields select "Database B", Insert Item, OK.

If you also want to remove the Grand Total column, click the right mouse button over your pivot table and select Options, then remove Grand total for rows.

Good question - I hadn't tried this one before, so I've learnt at least one new thing today! :->

 

[dianemarie]

Wow!! I love it! Thank you GeekGirl (you are totally NOT a geek!). This site rocks. (I hang out with my 11 year old son way too much.)