Exceljet

Quick, clean, and to the point

Pivot table calculated field example

Standard Pivot Tables have a simple feature for creating calculated fields. You can think of a calculated field as a virtual column in the source data. A calculated field will appear in the field list window, but will not take up space in the source data. In the example shown, a calculated field called "Unit Price" has been created with a formula that divides Sales by Quantity. The pivot table displays the calculated unit price for each product in the source data.

Note: data ends on row 18, so the calculation is as follows: $1,006.75 / 739 = $1.36

Fields

The source data contains three fields, Product, Quantity, and Sales. A forth field called "Unit Price" is a calculated field.

Field list after adding calculated field

The calculated field was created by selecting "Insert Calculated Field" in the "Fields, Items, and Sets" menu on the ribbon:

Select "Insert Calculated Field" from this menu

The calculated field is named "Unit Price" and defined with the formula "=Sales/Quantity" as seen below:

The Insert Calculated Field window

Note: Field names with spaces must be wrapped in single quotes ('). Excel will add these automatically when you click the Insert Field button, or double-click a field in the list.

The Unit Price field is renamed "Unit Price " (note the extra space) after it has been added to the Values area:

Unit Price value field settings

Steps

  1. Create a pivot table
  2. Create the Calculated field "Unit Price"
  3. Add Unit Price to field to Values area
    1. Rename field "Unit Price "
    2. Set number format as desired

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.