Excel Pivot Table Calculated Fields: Step-By-Step Tutorial To Working With Your Own Formulas

Pivot Tables allow you to calculate and analyze data in several different ways.

At the most basic level, a basic Pivot Table provides some basic (but powerful) calculation functionality to determine the displayed values. For example, in the sample Pivot Table below:

• Number of Units Sold and Total Sales are summarized by the Sum Function.
  
  
• Unit Price uses the Average Function.

Furthermore, Pivot Tables also allow you to display values based on other items or cells. You can, for example, display values as percentages or running totals.

These 2 options allow you to deal with a wide variety of situations and data analysis requirements. However, in certain cases, you may want to create your own formulas.

However, you can’t simply add a new row or column to a Pivot Table. If you try to do so, Excel usually displays a warning such as the following:

If you want to create your own formula within a Pivot Table, you usually work with either of the following:

• Calculated Fields.
  
  
• Calculated Items.

In this Pivot Table Tutorial, I focus on Calculated Fields. I provide all the information you need to quickly carry out the most common and relevant operations with Calculated Fields.

All the matters I explain in this blog post are illustrated with an easy-to-follow example. You can get immediate free access to the Excel workbook that contains the sample data and Pivot Table by clicking here.

The following Table of Contents lists the topics I cover below.

Let’s start by looking at the sample source data and Pivot Table within the workbook that accompanies this Excel Tutorial:

Sample Pivot Table And Source Data

For this Pivot Table Tutorial, I use a Pivot Table based on the following source data:

This table has 10,000 rows listing certain sales data, as follows:

• First column: Date. Period covered is from January 1 to December 31 of 2017.
  
  
• Second column: Item. The item can be one of the following Microsoft products:
  
  
  • Surface 3.
    
    
  • Surface Pro 4.
    
    
  • Surface Book.
    
    
  • Lumia 650.
    
    
  • Lumia 950.
    
    
  • Lumia 950 XL.
    
    
• Third column: Store. To keep this example simple, this example assumes that there is 1 store in each of the following cities:
  
  
  • Atlanta.
    
    
  • Boston.
    
    
  • Chicago.
    
    
  • Dallas.
    
    
  • Detroit.
    
    
  • Houston.
    
    
  • Los Angeles.
    
    
  • Miami.
    
    
  • Minneapolis.
    
    
  • New York.
    
    
  • Philadelphia.
    
    
  • Phoenix.
    
    
  • San Diego.
    
    
  • San Francisco.
    
    
  • Seattle.
    
    
  • Tampa.
    
    
  • Washington, D.C.
    
    
• Fourth column: Units sold.
  
  
• Fifth column: Unit price. The unit price of an item (for example, the Surface Pro 4) doesn’t change. In other words, every item has a single unit price regardless of (i) store location and (ii) date of sale.
  
  
• Sixth column: Total value of sales. I calculate this, for each item, as the product of (i) Units sold times (ii) Unit price. Mathematically:
  

  Units sold x Unit price

Using this source data, I create the following Pivot Table. This is the Pivot Table we’ll be working with throughout the rest of this Tutorial.

This Pivot Table displays the following data for each store and item:

• Number (Sum) of Units Sold.
  
  
• Average Unit Price.
  
  
• Total Sales (Sum of Sales Amount).

The data corresponding to each of these Fields appears in a separate column of the Pivot Table.

Let’s dive into the topic of Calculated Fields. I start by defining what a Calculated Field is and when can they help you.

What Are Calculated Fields And When To Use Them

Excel guru John Walkenbach (in the Excel 2016 Bible), defines a Calculated Field as:

A new field created from other fields in the pivot table.

You normally use Calculated Fields for purposes of adding a new Field within your Pivot Table. The newly added Field carries out certain calculations based, usually, on the values of other Fields.

In more technical terms, Calculated Fields use the sum of the underlying data of the Field(s) the Calculated Field formula uses. In other words, when you work with Calculated Fields, you’re working with all the underlying data (vs. specific Item(s)) of the relevant Field(s).

Because of the above, Calculated Fields are useful when you want to use all the data from certain Field(s) in your formulas.

To see how this looks in practice, let’s look at the sample Pivot Table:

Let’s assume that you need to make a very basic calculation to estimate the Cost of Goods Sold for each store and item.

To keep the examples as simple as possible, and focus on the topic of Calculated Fields, the calculation is very basic. We’re going to assume that you can calculate the Cost of Goods Sold as a percentage of the Sales Amount for each item and store. Mathematically:

COGS = Total Sales x %

Notice that the source data for the Pivot Table doesn’t have a column with Cost of Goods Sold.

In some situations, you may be able to go back to the source data and add such a column. However, in other cases, adding a new column to the source data may not be practicable or convenient.

An alternative to adding a new column to the source data is to use a Calculated Field.

In Excel 2016 Pivot Table Data Crunching, Excel authorities Bill Jelen (Mr. Excel) and Mike Alexander list several advantages of using Calculated Fields (vs. alternatives such as adding columns to the source data). In general, most of the advantages of working with Calculated Fields boil down (mostly) to the following 2 points:

• Reduced risk of reference or calculation errors.
  
  
• Increased scalability and flexibility.

Both advantages will become clearer as you read through the rest of this Pivot Table Tutorial.

This doesn’t mean that Calculated Fields are suitable for any situation. Further below, I introduce several common problems and limitations of Calculated Fields. That introduction should help you to determine the cases in which Calculated Fields may not be the best alternative.

Before I start to explain how you work with Calculated Fields, let’s go back to the sample workbook I introduce in the previous section and look at the…

Pivot Table Calculated Field Example

In the example I use in this Tutorial, we’ll be working with a Calculated Field named “Cost of Goods Sold”. This Calculated Field:

1. Uses the values from other Field (Total Sales).
   
   
2. Carries out certain calculations (multiply Total Sales by a percentage).

Once I add the Calculated Field to the sample Pivot Table above, the Excel worksheet looks as follows. Notice the new column (Cost of Goods Sold).

Don’t confuse Calculated Fields with Calculated Items. Even though they share some similarities, these terms refer to different constructs.

The main difference is the fact that Calculated Items work with individual records. In other words, Calculated Items allow you to work with Item(s) from within a Field.

Calculated Fields, as I mention above, use all the underlying data of the Field(s) the Calculated Field formula uses.

In other words, you generally:

• Use Calculated Fields when you want to work with all the underlying data of certain Field(s).
  
  As I mention above, the rough equivalent to Calculated Fields is to add a column to your source data.
  
  
• Work with Calculated Items if your purpose is to work with specific Item(s) within a Field.
  
  Just as Calculated Fields are roughly equivalent to additional columns in the source data, you can think of Calculated Items as the rough equivalent to adding row(s) to your source data.

Now that you have a clear understanding of what a Calculated Field is, let’s see how you can insert one:

How To Add Calculated Field To A Pivot Table

You can easily add a Calculated Field to a Pivot Table in the following 6 steps:

1. Select Pivot Table.
   
   
2. Go to Ribbon > Analyze > Fields, Items & Sets > Calculated Field.
   
   
3. Excel displays the Insert Calculated Field dialog box.
   
   
4. Enter the name for the Calculated Field in the Name input box.
   
   
5. Enter the formula for the Calculated Field in the Formula input box.
   
   
6. Click Add and/or OK.

Once you complete these 6 steps, Excel adds the Calculated Field to the following sections:

• The Values Area of the Pivot Table.
  
  
• The Pivot Table Fields task pane.

Let’s take look at each of these steps, and check out how to carry them out in practice.

Step #1: Select Pivot Table

Select the Pivot Table report to which you want to add the Calculated Field to. You can do this by clicking anywhere on the Pivot Table.

Once you’ve selected the Pivot Table, Excel displays two contextual Ribbon tabs: Analyze and Design.

Step #2: Go To Ribbon > Analyze > Fields, Items & Sets > Calculated Field

One of the contextual Ribbon tabs displayed by Excel is Analyze. Do the following:

1. Go to the Analyze tab.
   
   
2. Select Fields, Items & Sets > Calculated Field.

As an alternative, you can use the keyboard shortcut “Alt, JT, J, F”.

Step #3: Excel Displays The Insert Calculated Field Dialog box

After you complete step #2 above, Excel launches the Insert Calculate Fields dialog box. In the example we’re working with, this dialog looks as follows:

Step #4: Enter Calculated Field Name

The first input box in the Insert Calculated Field dialog is Name.

Excel automatically enters a basic name (Field1 in the example above). However, generally, it’s better if you enter a more descriptive and useful name.

For purposes of this example, I enter “Cost of Goods Sold”.

Step #5: Enter Formula

As I explain above, one of the main purposes of Calculated Fields is to allow you to create your own formulas using data from other Fields within the same Pivot Table.

The Formula input box is where you enter that formula you want to create.

For our basic example, I assume that the Cost of Goods Sold is 65% of the actual sales price. Therefore, I can calculate the total Cost of Goods Sold with the following formula:

=’Sales Amount’*65%

Notice that the syntax you use to specify a formula for a Calculated Field isn’t the same that you use when working with worksheet formulas.

This change may look scary at first. You shouldn’t worry too much about this difference.

In a section below, I provide a more detailed introduction to Calculated Field formula syntax and behavior. I also explain how you can easily insert a Field reference. Hint: you don’t have to type the Field reference.

Step #6: Click Add And/Or OK

Once you’ve entered a name and formula for your Calculated Field, you can do either of the following to confirm the addition:

1. Click the Add button.
   
   This is helpful if you want to add more Calculated Fields. In such a case, repeat steps #4 through #6 as many times as necessary.
   
   
   
   
2. Click the OK button. This option is suitable if you’re:
   
   
   • Only adding 1 Calculated Field; or
     
     
   • Have finished adding multiple Calculated Fields.
     
     

   

How To Create Calculated Field Formulas

As I explain above, step #5 of the process to add a Calculated Field to a Pivot Table is specifying the formula you want to use.

The formulas you use in Calculated Fields have a different syntax and behavior to regular worksheet formulas. The purpose of this section is to give you a basic overview of both topics.

Let’s start by identifying the elements you can use when creating Calculated Fields:

Calculated Field Formula Elements

Generally, you can use the following elements build formulas for Calculated Fields:

1. Operators.
   
   
2. Expressions.
   
   
3. Certain Functions, subject to the main limitations I explain below.
   
   
4. Constants.
   
   
5. References to Fields. This includes both (i) Fields within the Pivot Table report and (ii) Fields that aren’t currently displayed in the Pivot Table.

On the other hand, you generally can’t include any of the following:

1. Cell references.
   
   
2. Defined names.
   
   
3. The following types of worksheet Functions:
   
   
   • Functions that require cell references or defined names. Examples of this type of function are the Lookups (LOOKUP, HLOOKUP and VLOOKUP) and INDEX.
     
     
   • Functions that return variable results. Examples of this include RAND, RANDBETWEEN, NOW, and TODAY.
     
     
   • Database Functions.
     
     
   • GETPIVOTDATA Function.
     
     
4. Array functions.
   
   
5. References to Subtotals or Grand Totals of the Pivot Table.

Calculated Field Formula Syntax

When creating a Calculated Field formula, you can generally use the following elements in the same way you’d use them when creating a worksheet formula:

1. Operators.
   
   
2. Expressions.
   
   
3. Functions, subject to the applicable restrictions I explain above.
   
   
4. Constants.

These are items #1, #2, #3 and #4 from the list of elements you can use to build Calculated Field formulas (above). In this section, I explain the basic rules you can apply to create references to data within the Pivot Table report (item #5 of the list).

Let’s start by recalling that Calculated Fields work with the sum of the underlying data for a Field. In other words, you use Calculated Fields when you want your formulas to work with the data from certain Field(s).

To understand what the above means, let’s go back to the formula I enter for the Cost of Goods Sold Calculated Field in step #5 of the process to add a Calculated Field above:

=’Sales Amount’*65%

This formula is composed of the following 3 items:

1. ‘Sales Amount’: Reference to a Field (Sales Amount) within the Pivot Table report.
   
   
   
   
2. *: The multiplication operator.
   
   
3. 65%: A constant.

The process followed by Excel to calculate the product returned by this formula is roughly as follows:

1. The Sales Amount for each Item (Product) and Store is added.
   
   In the example we’re looking at, this value corresponds to that displayed in the Sum of Sales Amount.
   
   
   
   
2. The sum obtained in step #1 above is multiplied by 65%.

Notice that, as I mention above, I enter Items #2 (operator) and #3 (constant) as I would for a regular worksheet formula.

Item #1 (‘Sales Amount’) is a reference to a Field within the Pivot Table (Sales Amount). This helps us illustrate the basic guidelines you follow when creating such a Field reference within a Calculated Field formula:

• You refer to a Field by using its Field Name.
  
  In the case above, this is “Sales Amount”.
  
  
• If a Field Name contains (i) more than 1 word, (ii) numbers, or (iii) symbols, wrap the name with single quotations (‘).
  
  In the example above, the name has 2 words (“Sales” and “Amount”). Therefore, I enter it within single quotes (‘Sales Amount’).

In most cases, however, you don’t even need to worry too much about the syntax for Field references. The Insert Calculated Field dialog box has a Fields list box that is quite helpful for these purposes. The Fields list box includes all the Fields that are available for you to use in your formula.

Therefore, you can insert a Field reference in the Formula input box in either of the following 2 ways:

1. Alternative #1:
   
   
   1. Select Field you want to refer to within the Fields list box.
      
      
   2. Click the Insert Field button.
      
      In the sample image below, I select the Sales Amount Field.
      
      
      
      
2. Alternative #2: Double-click on the Field you want to refer to within the Fields list box.
   
   Just as in alternative #1 above, I select the Sales Amount Field.
   
   

The result of the above process is a reference to the chosen Field. In the example we’re working with, this looks as follows:

How To See Or List All Calculated Fields

In this Tutorial, I work with a sample Pivot Table that has only 1 Calculated Field (Cost of Goods Sold).

Depending on the complexity of your Pivot Tables, you may have to deal with several Calculated Fields and Calculated Items at the same time. In such situations, it may be helpful if you can view all these elements at the same time.

You can see all Calculated Fields within a Pivot Table by following these 2 easy steps:

1. Select Pivot Table.
   
   
   
   
2. Go to Ribbon > Analyze > Fields, Items & Sets > List Formulas. Alternatively, use the keyboard shortcut “Alt, JT, J, L”.
   
   

Excel creates a new worksheet. The worksheet has the following 2 tables:

• Calculated Fields.
  
  
• Calculated Items.

Each of these tables displays the Solve Order, (Field or Item) Name, and Formula for each Calculated Field or Item.

How To Modify A Calculated Field Formula

From time to time, you may have to go back to your Pivot Table reports and edit previously entered Calculated Field formulas.

To modify a Calculated Field formula, follow these 6 easy steps:

1. Select Pivot Table.
   
   
2. Go to Ribbon > Analyze > Fields, Items & Sets > Calculated Field.
   
   
3. Excel displays the Insert Calculated Field dialog box.
   
   
4. Select relevant Calculated Field using the Name drop-down list.
   
   
5. Edit the Calculated Field formula in the Formula input box.
   
   
6. Click Modify and/or OK.

Other than steps #4 and #5, the process is substantially the same as that which you follow to create a Calculated Field. For clarity purposes, let’s go through each of these steps using an example:

Step #1: Select Pivot Table

Click anywhere on the Pivot Table report you want to work with. Excel displays the Analyze and Design contextual tabs.

Step #2: Go to Ribbon > Analyze > Fields, Items & Sets > Calculated Field

Once Excel displays the contextual tabs, go to the Analyze tab. Select Fields, Items & Sets > Calculated Field.

If you like using keyboard shortcuts, use “Alt, JT, J, F”.

Step #3: Excel Displays The Insert Calculated Field Dialog Box

You modify a Calculated Field formula from within the Insert Calculated Field dialog box. This is the same dialog box that you use when you originally create the Calculated Field formula.

Step #4: Select Calculated Field

Click on the drop-down arrow on the right side of the Name box within the Insert Calculated Field dialog box. Excel displays a drop-down list that includes all the Calculated Fields you’ve added to the Pivot Table report.

From within this list, select the Calculated Field whose formula you want to amend.

The sample Pivot Table that I use in this Tutorial has a single Calculated Field: Cost of Goods Sold. I select it.

Step #5: Modify Calculated Field Formula

Once you’ve selected the Calculated Field you want to work with, Excel displays the current formula in the Formula box.

In the example we’re working with, this looks as follows:

You use that Formula input box to edit the Calculated Field formula.

For example, the formula that I entered when originally creating the Cost of Goods Sold Calculated Field is as follows:

=’Sales Amount’*65%

Let’s assume that your assumption for calculating Cost of Goods Sold changes. The Cost of Goods Sold decreases from 65% of the Sales Amount to 55%. The new Calculated Field formula is as follows:

=’Sales Amount’*55%

Step #6: Click Modify And/Or OK

Once you’ve modified the Calculated Field formula, you can confirm your changes in either of the following ways:

1. Click the Modify button.
   
   In this case, the Insert Calculated Field dialog box continues open. This is useful if you want to continue working with this dialog. For example, to modify other Calculated Fields, go back to step #4 as required.
   
   
   
   
2. Click the OK button.
   
   Clicking OK confirms all the changes and closes the Insert Calculated Field dialog. This option is suitable if you don’t have to do anything else from within this dialog box.
   
   

How To Delete Or Hide A Calculated Field

In some cases, you may not need to display a Calculated Field within your Pivot Table report. In such situations, you have the following 2 options:

1. Hide the Calculated Field.
   
   
2. Delete the Calculated Field.

The basic difference between hiding and deleting a Calculated Field is that, if you delete a Calculated Field, Excel removes it permanently. That isn’t the case when you just hide the Field. When hiding the Calculated Field, it continues to appear within the Pivot Table Fields task pane.

Let’s look at how you can implement each of these solutions:

Hide A Calculated Field

When you add a Calculated Field to a Pivot Table, Excel adds it to the Field List. More precisely, the Calculated Field usually appears within the following Areas:

• The Pivot Table Field list.
  
  
• The Values Area of the Pivot Table.
  
  In fact, Calculated Fields can only be in the Values Area of the Pivot Table report.

The screenshot below shows how the Field List looks like in the example I use throughout this Pivot Table Tutorial. Notice how the Cost of Goods Sold Calculated Field:

• Appears at the end of the Pivot Table Fields task pane.
  
  
• Is added to the Pivot Table, as evidenced by the marked checkbox to its left. More precisely, the Calculated Field is added to the Values area.

Because of the above, you can hide a Calculated Field by simply removing it from the Pivot Table Areas. In other words, you can quickly hide a Calculated Field in either of the following 3 ways:

1. Alternative #1: Drag the Calculated Field and drop it outside the Pivot Table Areas.
   
   
   
   As I mention above, Calculated Fields can only appear within the Values Area. If you try dropping the Calculated Field within another Area, Excel displays the following dialog box:
   
   
   
   
2. Alternative #2: Uncheck the box to the left of the Calculated Field in the Pivot Table Fields task pane.
   
   
   
   
3. Alternative #3:
   
   
   1. Right-click on a cell within the Calculated Field.
      
      
   2. Select the option to remove the Calculated Field.
      
      

Delete A Calculated Field

You can easily delete a Calculated Field in the following 6 quick steps:

1. Select Pivot Table.
   
   
2. Go to Ribbon > Analyze > Fields, Items & Sets > Calculated Fields.
   
   
3. Excel displays the Insert Calculated Field dialog box.
   
   
4. Select the relevant Calculated Field using the Name drop-down list.
   
   
5. Click Delete.
   
   
6. Click OK.

Let’s go through these steps in more detail:

Step #1: Select Pivot Table

Begin by selecting the Pivot Table you’re working with. This results in Excel displaying the contextual Ribbon tabs (Analyze and Design).

Step #2: Go to Ribbon > Analyze > Fields, Items & Sets > Calculated Fields

Go to the Analyze contextual Ribbon tab. Within that tab, select Fields, Items & Sets > Calculated Fields.

You can replace the above by using the keyboard shortcut “Alt, JT, J, F”.

Step #3: Excel Displays The Insert Calculated Field Dialog Box

After you complete step #2 above, Excel displays the Insert Calculated Field dialog box.

Step #4: Select Calculated Field To Delete

The first box in the Insert Calculated Field dialog box is Name. Click on the drop-down arrow on its right.

Excel displays a drop-down list with all the Calculated Fields within the Pivot Table report. Select the Calculated Field you want to delete.

In the example I use throughout this Tutorial, the only Calculated Field is Cost of Goods Sold. I select it.

Step #5: Click Delete

Once you’ve selected the Calculated Field to delete, confirm by clicking the Delete button.

Step #6: Click OK

You can repeat steps #4 and #5 above to delete as many Calculated Fields as you need.

Once you’re done, confirm everything by clicking the OK button.

Common Issues With Calculated Fields

Calculated Fields can be very useful in some situations. There are, however, a few characteristics that limit their usefulness or cause problems in some situations. I introduce a few of these below.

I also mention certain limitations in other sections of this Tutorial. The following are examples of this:

• The restrictions regarding the elements you can use within your Calculated Field formulas.
  
  
• The fact that Calculated Fields can only be within the Values Area of the Pivot Table.

Some limitations have certain workarounds that you can implement. This topic exceeds the scope of this blog post. I may write about it in the future. If you want to receive an email whenever I publish new content in Power Spreadsheets, please feel free to register for our Newsletter by entering your email address below.

Common Problem #1: Calculated Fields Can Only Be Summarized By Sum

Generally, you can summarize the values displayed in the Values Area by using 1 out of the 11 following functions:

1. Sum.
   
   
2. Count.
   
   
3. Average.
   
   
4. Max.
   
   
5. Min.
   
   
6. Product.
   
   
7. Count Numbers.
   
   
8. StdDevP.
   
   
9. StDev.
   
   
10. VarP.
    
    
11. Var.

In the case of Calculated Fields, you can’t use this option. Values of Calculated Fields are always summarized by calculating a Sum.

This is connected to the fact that Calculated Fields work with the sum of the underlying data for a Field. As I explain above, Excel proceeds as follows:

1. Takes that data; and
   
   
2. Carries out the calculations required by your Calculated Field formula.

Notice the following:

• If you right-click on a Calculated Field, Excel displays a contextual menu where the Summarize Values By feature is disabled.
  
  
  
  
• If you select a different calculation within the Value Field Settings dialog box, Excel doesn’t change the values that appear in the Pivot Table. In other words, your selection has no effect other than changing the displayed Field Name (if applicable). The image below shows how this looks like in practice:
  
  

Common Problem #2: Calculated Fields Aren’t Available When Working With OLAP Data Sources Or Using The Data Model

You can only use Calculated Fields or Calculated Items when you’re working with Pivot Table reports that aren’t based on an OLAP (Online Analytical Processing) database. Neither of these features is available if your Pivot Table report uses OLAP source data.

More generally, as explained by Bill Jelen (Mr. Excel) in Excel 2016 in Depth:

The Data Model does not support traditional calculated fields or calculated items.

Common Problem #3: Calculation Of Subtotals And Grand Totals Of Calculated Fields

When you’re working with Pivot Tables, you have the option of displaying Subtotals and Grand Totals. If you’re working with Calculated Fields, Excel also displays Subtotals or Grand Totals for them.

You may expect Excel to calculate those Subtotals or Grand Totals by adding up the values of the relevant Items in the Calculated Field.

This isn’t the case. When calculating the Subtotals or Grand Totals for a Calculated Field, Excel proceeds as follows:

1. It takes the values of the Subtotals or Grand Totals for the Fields you refer to within the Calculated Field Formula.
   
   
   
   
2. It carries out the actual operations required by the applicable Calculated Field formula.

Let’s see how this looks like in the example we’re working with:

The screenshot below shows the section of the Pivot Table report corresponding to the Atlanta store. The Calculated Field formula is as follows:

=’Sales Amount’*65%

In this case, the way in which Excel calculates the Atlanta Total for the Cost of Goods Sold Calculated Field causes no problem. This is because both following processes yield the same result:

1. Adding the values within the Cost of Goods Sold Calculated Field.
   
   
   
   
2. Multiplying the Total Sales Amount by 65%.
   
   This is the calculation that Excel carries out.
   
   

Let’s modify the Calculated Field formula to see a situation where Excel’s calculation process may become problematic:

Let’s assume that the Cost of Goods Sold for a product varies depending on the actual amount of sales as follows:

• If the Sales Amount is larger than $200,000, the Cost of Goods Sold is 55% of the Sales Amount.
  
  
• Otherwise, the Cost of Goods Sold is 65% of the Sales Amount.

The resulting Calculated Field formula is as follows:

=IF(‘Sales Amount’>200000,’Sales Amount’*55%,’Sales Amount’*65%)

The image below shows the resulting Pivot Table report. For the images below, I add a helper column to the right of the Pivot Table showing the results of dividing Cost of Goods Sold by Sales Amount. Notice the following:

1. The Atlanta Total for Sales Amount is larger than $200,000.
   
   
   
   
2. Because of #1 above, Excel calculates the Atlanta Total for Cost of Goods Sold as 55% of the Atlanta Total for Sales Amount.
   
   
   
   
3. Due to the way Excel calculates the Atlanta Total for Cost of Goods Sold, the value displayed as Atlanta Total for Cost of Goods Sold ($699,646.20) isn’t equal to the sum of the individual values within the Calculated Field ($749,457.6).
   
   
   
   The reason for this is that, for some of those products, the Sales Amount is below $200,000. Therefore, the Cost of Goods Sold is 65% of the Sales Amount (not 55%).
   
   

Common Problem #4: Working With Calculated Fields Generally Clears The Undo Stack

When working with Calculated Fields in Pivot Tables, you’ll constantly deal with the Insert Calculated Field dialog box.

Generally, when you confirm changes by clicking the OK button of the Insert Calculated Field, Excel clears the Undo stack. Therefore, you can’t undo previous actions by clicking the Undo button or using the “Ctrl + Z” keyboard shortcut.

Conclusion

Due to their scalability, flexibility and ease of management, Calculated Fields are a great alternative for purposes of easily adding your own formulas and carrying out calculations within Pivot Tables.

Despite the above, Calculated Fields have certain limitations and restrictions. Therefore, they’re not the most suitable tool for every situation you may face. In the words of Excel gurus Bill Jelen (Mr. Excel) and Mike Alexander (in Excel 2016 Pivot Table Data Crunching):

It’s important you understand what goes on behind the scenes when you use pivot table calculations, and it’s even more important to be aware of the boundaries and limitations of calculated fields and calculated items to avoid potential errors in your data analysis.

After reading this Pivot Table Tutorial, you have the knowledge you need to appropriately work with Calculated Fields in Pivot Tables. In addition to knowing their advantages and limitations, you know:

• How to insert a Calculated Field, and create appropriate Calculated Field formulas.
  
  
• See all Calculated Fields at once.
  
  
• Modify Calculated Field formulas.
  
  
• Hide or delete Calculated Fields.

Remember that you can get immediate free access to the workbook that accompanies this Pivot Table Tutorial by clicking here.

Now:

Please take a few seconds to leave a comment below letting us know:

Do you use Calculated Fields when working with Pivot Tables?

Books Referenced In This Excel Pivot Table Tutorial

• Excel 2016 Bible.
  
  
• Excel 2016 In Depth.
  
  
• Excel 2016 Pivot Table Data Crunching.