Home > Solution Categories > Microsoft Office > MS Excel > Calculating Data in an Excel pivot table
Question
No of visitors who read this post: 312
Category: MS Excel
Type: Question
Author: Susan1962
No votes yet

Recently I set up a Pivot Table in Excel which shows inventory purchased and inventory sold for my business. I would like to add a simple calculation in the Pivot Table showing the difference between the amount purchased and amount sold, giving me my on hand inventory.

Can a calculation be added to a Pivot Table once it has been set up?




Item Data Total  
Blue Candle Sum of Purchased 50  
  Sum of Sold 12  
Blue Votive Sum of Purchased 75  
  Sum of Sold 42  
Cream Votive Sum of Purchased 75  
  Sum of Sold 27  
Gold Candle Sum of Purchased 50  
  Sum of Sold 2  
Green Candle Sum of Purchased 50  
  Sum of Sold 11  
Red Candle Sum of Purchased 50  
  Sum of Sold 27  
White Votive Sum of Purchased 75  
  Sum of Sold 38  
Total Sum of Purchased 425  
Total Sum of Sold   159  

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

#

Submitted by Kellybrooke on Sun, 2011-09-25 21:10. Comment Id: 30053

Even though the Pivot Table has already been set-up, through drag-n-drop, criteria can be manipulated. The process are as follows:

  • To change the “field formula” you have to click on the PIVOT TABLE on the PIVOT TABLE “toolbar”, and in the FORMULAS menu, hit CALCULATED FIELD. 
  • Select the category or group of data that needs changing, a FORMULA box will appear after clicking the name of the group or field.
  • Change the formula as you desire and hit MODIFY. And then hit OK.

If you want to change the formula of an item you have to:

  • Click or choose the group or field where the item that you want to change is.
  • Then hit PIVOT TABLE on the PIVOT TABLE toolbar and instead of CALCULATED FIELD choose CALCULATED ITEM in the formula menu.
  • Highlight the name of the item that needs to be changed, change the formula in the FORMULA BOX.
  • Click MODIFY, then OK.

Hope this helps.

# (Solution Accepted)

Submitted by Sherin Viluel on Sun, 2012-06-17 22:59. Comment Id: 64603

In excel it is impossible to add calculation or formula directly to a pivot table. You can add a calculation in to a cell which is out of the pivot table as shown in the below image but you will not be able to copy that formula to the other cell.

If you copied it to other cells, it will show the same result which is in the first cell.

However, you can add a calculation to the data source which you can get the on hand inventory and then it is possible to add that field to pivot table as follows.

First get the on hand inventory to the data source by adding a new column as on hand inventory and type a formula to get the balance inventory (purchase quantity – sold quantity).

Then go to the “options” tab and click on the “change data source” on Data section (Step 1).

Then select the entire data source including new on hand inventory column and click ok (step 2).

Then go to the “options” tab and click on the “Field list” in show/hide section.

Drag the “on hand inventory” field and put it to Values field.

Make the “on hand inventory “field to sum format.

Now you will be able to see the “on hand inventory” column in pivot table as follows.

Previous Previous Page
688 of 862
Next Page Next