How to Add Calculated Field to Pivot Table in Excel

This guide will explain how you can add a calculated field to a Pivot Table in Excel.

When working with Pivot Tables, you may want to perform additional calculations on the data found in the included fields. Excel allows you to specify your own formula to generate a new calculated field.

Let’s take a look at a sample scenario where we might need to create a calculated field in an Excel Pivot Table.

Suppose you have a Pivot Table of a dataset detailing business expenses and earnings. For each month, you can find the total amount spent on expenses. You’re also able to find the sum of sales in a given month. How can we find our monthly profits?

Our original dataset does not include a Profit field. We can, however, create a new Profit field from the existing fields that we have in our Pivot Table. In this basic example, we can assume that Total Sales – Total Expenses = Total Profits.

Excel gives users the ability to insert a calculated field into their pivot tables. The user simply has to provide a name for the new field and the formula to derive it.

This use case is just one way to use calculated fields in Excel. For example, we can create a new sales field with a percentage of tax deducted. Or we could create a new field that compares the total sales to some constant goal.

Now that we know when to add calculated fields to pivot tables in Excel, let’s take a look at how they work on an actual sample spreadsheet.

 

 

A Real Example of a Pivot Table with a Calculated Field

Let’s take a look at a real example of a calculated field function being used in an Excel Pivot Table.

In the example below, we have a dataset of companies and their total sales in dollars for a particular day. Let’s say we want to aggregate the data by country of origin. For each country we should see the sum of all total sales as well as that amount after tax.

initial dataset of company sales

 

The Pivot Table below uses the Country field as a row label. In column C, we’ve added a calculated field that deducts a certain percentage from the sum of all sales in a particular country.

add calculated field to pivot table

 

To get the values in Column C, we just need to use the following formula:

= total_sales - (total_sales * 12%)

You can make your own copy of the spreadsheet above using the link attached below. 

If you want to try creating your calculated fields in Excel, simply follow our guide in the next section.

 

 

How to Add a Calculated Field to Pivot Table in Excel 

This section will explain how you can start adding a calculated field to your Pivot Table. You’ll learn how to create a new Pivot Table from a dataset and define a new field.

Follow these steps to start adding calculated fields:

  1. In the Insert tab, click on the icon labeled ‘PivotTable’.
    add PivotTable
  2. In the new PivotTable dialog box, you can select a table or range to get data from. In this example, we’ll use the company sales data shown in the previous section. Click on the OK button.
    select a table and range to use as the source of the pivot table
  3. Your spreadsheet should now have a Pivot Table and a Pivot Table Fields panel on the right-hand side of the screen. The panel should list down all available fields.
    pivot table fields
  4. For this example, we’ll first add the country field under the Rows area to group sales data later by region.
    add country to row area
  5. Drag the total_sales field to the Values area. By default, the field should now be renamed Sum of total_sales. If this is not the case, users can manually change it by accessing the field’s settings.
    add total sales to values area
  6. The Pivot Table should now have the sum of total sales per country.
    sum of total_sales in pivot table
  7. To add our new calculated field, go to the PivotTable Analyze tab and click on the Fields, Items, & Sets icon.
    add calculated field to pivot table
  8. From the dropdown menu, select Calculate Field.
    select Calculated Field option
  9. A new dialog box will appear. Users must name their new calculated field and define it with a formula that uses other fields. In the example below, we’ve created a new calculated field named Sales After Tax.
    define formula for calculated field
  10. Click on the Add button to add the calculated field to the Pivot Table. Once all necessary calculated fields have been added, click on OK.
    add calculated field to pivot table
  11. You can now add your new calculated field to your Pivot Table.
    final pivot table with calculated field

 

 

This step-by-step guide should be all you need to begin adding calculated fields to Pivot Tables in Excel. It should now be easy for you to start defining new fields using formulas through Excel’s Pivot Table Analyze tab.

Excel’s Pivot Table is just one example of a powerful tool you can use to explore your data. With so many other Excel functions available, you can surely find one that suits your use case. 

Are you interested in learning more about what Excel can do? Subscribe to our newsletter to find out about the latest Excel guides and tutorials from us. 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like