Pivot Table Calculated Fields allow you to do mathematical calculations with your Field List.  You can use any of the Excel mathematical equations, like /*+-%.

The only limitation is that you cannot reference any cells.  Pivot Table Calculated Fields can be used to calculate percentage increases on Sales, margin calculations, or Cost of Goods sold, as I show below.


Here is our Pivot Table:


STEP 1: Click on the Pivot Table and Go to Options > Fields, Items, & Sets > Calculated Field


STEP 2: Set the Name to Cost of Goods Sold


STEP 3: Set the Formula to COSTS/SALES. This is the formula to be used for our calculated field.


STEP 4: The formatting is still not correct. Right click on the new column and select Number Format.

STEP 5: Select Percentage and click OK.

Your new calculated is now ready in percentage format!


Calculated Field

Helpful Resource:


If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Drill Down To Audit Using a Pivot Table When you are using a Pivot Table in Excel and want to know what data makes up a certain value, all you have to do is double click on that cell.This will open up a brand new Sheet with all the rows of data that make up that value.NB. This is an extraction of your data sour...
Show Report Filter Pages in a Pivot Table When you are using an Excel Pivot Table you can show the items within the Report Filter on separate sheets inside your workbook.Say that you have created an awesome Pivot Table which shows total sales and number of transactions per region.You can drop in your Customer fie...
Filter a Pivot Table by Dates There are an array of different Date filters in a Pivot Table.  You can filter by a particular date range, for example: by this week, next month, next quarter, next year, last year, year to date and the list goes on and on.  This is useful if you want to see what invoices are due...
Sort Field List In a Pivot Table from A to Z Do you ever get in a situation where your Pivot Table Field List has over 20 fields and you spend heaps of time trying to locate one within the Field List?Well you can save time by sorting the Pivot Table Field List in alphabetical order!To do this you need to Right Click...