Unlock Pivot Table: Master Pivot Table Calculated Fields in Excel

Introduction

A pivot table calculated fields is an extremely useful way to add calculations to a pivot table. It allows you to create a new field in your pivot table that performs calculations based on the existing data.

The key benefit of using a calculated field is that it allows you to generate summarized information very easily, without having to manually create formulas across rows or columns. Any calculation that you would normally have to do in a separate column or measure can be done directly within the pivot table.

Calculated fields are especially helpful when you want to:

Calculate percentages, indexes or KPIs based on pivot table data.
Perform ad hoc calculations that are not already captured in the source data.
Add formulas without altering the integrity of the original dataset.

By using calculated fields, you can extend the built-in calculation capabilities of pivot tables. They allow you to manipulate the data summaries on-the-fly to gain additional insights.

In this guide, we will walk through practical examples of creating calculated fields in pivot tables using Excel formulas. You will learn how to build the formulas, add the new fields, and format them appropriately.

Prerequisites

To follow along with creating calculated fields in Excel pivot tables, you’ll need:

Access to Excel
- This tutorial uses Microsoft Excel for Windows, version 2007 or newer. The steps may differ slightly for Mac or other versions of Excel.
Basic knowledge of Excel pivot tables
- You should know how to create a pivot table from a data set and add fields to the pivot table. If you need a refresher, check out this Excel pivot table tutorial.

The source data and basic pivot table setup are covered in the next sections. But having prior experience with pivot tables will help you get the most out of using calculated fields.

Create the Source Data (Pivot Table Calculated Fields)

To create a calculated field in a pivot table, you first need to have your source data organized properly in an Excel worksheet. The source data should contain the columns you wish to use in the calculated field formula.

For example, if you want to create a calculated field that multiples Quantity by Price, your source data needs columns for both Quantity and Price. The data should be organized with each row representing a record and each column representing a field.

It’s best practice to organize your source data as follows:

Put field names in the first row
One field per column
Each record on its own row
Remove any blanks rows or columns
Format fields consistently (e.g. currency, date)

Your data is then ready to be inserted into a pivot table and have a calculated field added. Having properly structured source data with the necessary columns will allow you to easily build the calculated field formula by inserting field references.

The key is ensuring all columns you want to use in your calculated field are present before creating the pivot table. This allows you to leverage the existing data fields without having to manually enter calculations.

Insert a Pivot Table

To insert a pivot table, first select the data you want to use. The data should be formatted as a table with column headers in the first row.

Select any cell within the source data. In the example below, the table contains sales data with columns for Date, Salesperson, Product, Units Sold and Sale Price.
Go to the Insert tab and click PivotTable.
In the Create PivotTable dialog box, verify the correct range is selected and click OK. This will insert a blank pivot table and pivot table field list into a new worksheet.

The pivot table canvas will consist of four areas – Report Filter, Column Labels, Row Labels, and Values. Next we’ll learn how to add fields to the pivot table.

Add Fields to the Pivot Table

A pivot table allows you to analyze and summarize data from a spreadsheet. Before creating a calculated field, you first need to add the necessary fields to the pivot table layout.

To add fields to a pivot table:

Click inside the pivot table to activate the PivotTable Fields pane. This will show a list of all the columns from your source data.
Select the fields you want to add to the pivot table layout by checking the box next to each field name. Common fields to add include:
- Row fields – The fields that will appear as rows in the pivot table. This is often a category, person, or time field.
- Column fields – The fields that will appear as columns in the pivot table.
- Value fields – The fields that will be summarized as values, like sums or counts. This is typically numeric data.
Drag the fields into the appropriate area in the PivotTable Fields pane.
- Row fields go to the Rows area
- Column fields go to the Columns area
- Value fields go to the Values area
The fields will now show up in the pivot table layout. Continue adding fields as needed.
Adjust the pivot table layout and formatting as desired.

Once you’ve added the necessary fields, you can proceed to inserting a calculated field to further analyze the data. The calculated field will be based on the fields added to the pivot table.

Insert a Calculated Field

To insert a calculated field in your pivot table:

Open the pivot table fields pane. This is usually on the right side of the Excel window.
Click the ‘Calculated Field’ button in the fields pane. This opens the calculated field dialog box.
In the name field, type a name for your new calculated field. Use a descriptive name that reflects the purpose of the calculation.

For example, if you are calculating total sales minus costs, you could name the calculated field “Profit.”

Click OK. This will insert a new blank calculated field into the fields pane. We’ll add the formula next.

Build the Calculated Field Formula

A calculated field formula allows you to create new data in your pivot table by using existing fields. The power is that you can perform mathematical operations on these fields to generate custom calculations.

To build the formula, reference fields from your pivot table by surrounding them in square brackets. For example, [Sales] and [Cost].

You can then use mathematical operators like +, -, *, and / to relate the fields. Some examples:

[Sales] - [Cost] – Calculates profit by subtracting cost from sales
[Sales] * 0.1 – Calculates a 10% sales tax
[Cost] / [Units] – Calculates the unit cost by dividing cost by units sold

The formula can contain numbers, cell references, math operators,Excel functions, or other spreadsheet formulas. Make sure the fields used in the formula exist in the source data or are already present in the pivot table.

By leveraging existing fields in mathematical formulas, you can create powerful calculated fields such as profit margins, costs per unit, sales tax, and more. This adds immense flexibility for analytics directly within your pivot table.

Add the Calculated Field to the Pivot Table

Once you’ve created the calculated field formula, you need to add it to the pivot table layout.

Here’s how to add a calculated field to your pivot table:

In the PivotTable Fields pane, locate the new calculated field. It will be listed with the other Value fields.
Check the box next to the calculated field name to add it to the pivot table. This will insert the calculated field into the pivot table report.
By default, the calculated field will be summed as a total value. You can change the calculation as needed by clicking the dropdown next to the field name.
Position the calculated field where desired in the pivot table layout along with the other fields. Typically calculated fields are best positioned in the Values area.
If you drag the calculated field to the Rows or Columns area, it will display the formula result for each row/column item instead of a total.

Once added, the calculated field will display its formula result in the pivot table like any other value field. You can format or sort on this new calculated column just like regular pivot table fields.

The key is checking that box next to the field name to insert it into the report layout. Then drag it where needed to build the desired pivot table view.

Format the Calculated Field

After you’ve created a calculated field in your Excel pivot table, you may want to customize its appearance and formatting. Here are some tips for formatting calculated fields:

Apply Number Formatting

Since calculated fields often perform numerical calculations, applying proper number formatting is important. Some common options include:

Currency – Format numbers as currency values with a currency symbol and decimal places. Useful for monetary values.
Percentage – Display numbers as percentages with a % symbol. Useful for calculations involving percentages.
Increase/Decrease Arrows – Add increase/decrease arrows to show if a value is higher or lower compared to another calculation.
Comma Style – Add commas to large numbers to improve readability.
Decimal Places – Adjust the number of decimal places displayed.

To change number formatting, right-click the calculated field in the PivotTable Fields list and select “Value Field Settings”. Then adjust the formatting as needed.

Rename the Calculated Field

By default, Excel assigns a generic name like “CalculatedField1” to new calculated fields. It’s helpful to rename it to something more descriptive.

To rename a calculated field:

Right-click the field name in the PivotTable Fields list.
Select “Value Field Settings”.
Type a new name into the “Custom Name” box.

Giving calculated fields descriptive names makes it easier to understand what they reference, especially if you use many calculations in one PivotTable.

Proper formatting and naming helps highlight the purpose of your calculated fields. Making them easy to comprehend allows you to better analyze and present the data in your PivotTable.

Examples and Use Cases

Pivot tables can be incredibly useful for calculating metrics like profit margins and sales per employee. Here are some examples of calculated fields in action:

Profit Margin Calculation

Let’s say our source data contains sales numbers in one column and cost data in another column. We can create a calculated field to automatically determine the profit margin percentage:

= 'Sales' - 'Cost' / 'Sales'

By inserting this formula as a calculated field, the pivot table will display the profit margin for each row or grouping. This enables easy analysis of profitability by product, customer, region etc.

Sales Per Employee

If the source data has a list of employees and their corresponding sales amounts, we can insert a calculated field to calculate productivity:

= 'Sales' / 'Number of Employees'

The pivot table will divide the sales by the number of employees. By adding filters or drill-downs, we can quickly view the sales performance per employee.

Calculated fields allow pivots to display important business metrics not contained in the original data source. They enable deeper data analysis and reporting without complex or rigid formulas.