Calculated Fields In PivotTables
If you need to display calculations in a PivotTable, you don't have to
create additional columns in your spreadsheet. You can do the
calculations within the PivotTable itself.
Consider a series of transactions showing the date of a transaction, the
cost of the product sold and the price that was charged. What we would
like to see is the cost, price, profit and gross margin percent broken
down by month. This sounds like a PivotTable report but we don't have a
column showing profit or one showing gross margin percent.
Create
a PivotTable from the data that already exists. If you're not sure how,
view the video of a
simple PivotTable.
Drag the data field so that Sum of Cost and Sum of Price show up side by
side. Then go to the PivotTable toolbar. Oddly, there is no right-click
combination or main menu item to get to the PivotTable formulas. You must go
to the PivotTable toolbar, click on Formulas, then Calculated Field
Create a new Field called Profit and use =Price-Cost for the formula.
Then just click OK to go back to your PivotTable.
View the video to see
these steps in action, as well as how to insert Gross Margin percentage.