PivotTable Calculations

1. Calculated Fields vs. Calculated Items: The Core Difference

Before we start building, it's essential to understand the distinction between a calculated field and a calculated item. They sound similar, but they serve fundamentally different purposes.

• A Calculated Field creates a new column in your PivotTable. It performs calculations using the values from other fields (e.g., columns in your source data). Think of it as virtually adding a new column to your data source.
  • Example: Profit = Sales - Expenses
• A Calculated Item creates a new row within an existing field in your PivotTable. It performs calculations using other items (e.g., rows or categories) within that same field.
  • Example: Creating a new item called 'East Coast' by summing the values for 'New York', 'Boston', and 'Philadelphia'.

The article below provides a concise explanation of this key difference.

2. Creating Calculated Fields

Calculated fields are the more common of the two and are incredibly useful for adding business metrics to your analysis. Let's say your data has Revenue and Cost fields, but not Profit. Instead of adding a Profit column to your source data, you can create it directly in the PivotTable.

To create a calculated field, you select a cell in your PivotTable, navigate to the PivotTable Analyze tab, and choose Fields, Items, & Sets > Calculated Field.

The following video provides an excellent, practical walkthrough of creating two calculated fields: one for 'Profit' and another for 'Profit Percentage'.

A key thing to remember, as noted in the How to Create Excel Pivot Table Calculated Field Examples article, is that calculated field formulas always operate on the sum of the underlying data for any field you reference, regardless of how that field is summarized in the PivotTable (e.g., as a Count or Average).

3. Creating Calculated Items

Calculated items are used when you want to perform math on the items within a single category. The most common use is to group several items into a new, single item. For instance, if you have sales data for individual countries, you could create a calculated item to show total sales for a continent.

To create one, you select an item in the field you want to modify, then go to PivotTable Analyze > Fields, Items, & Sets > Calculated Item.

This video demonstrates how to combine several order statuses into a single 'Sold' category.

While useful, calculated items have some restrictions. For example, once you add a calculated item to a field, you may not be able to use certain other PivotTable features on that field, like creating other custom groups. For this reason, calculated fields are generally preferred unless the calculation must be done across items.

4. Managing and Auditing PivotTable Formulas

As you build more complex reports, or when you inherit a workbook from someone else, you'll need a way to see all the custom formulas in a PivotTable. Excel provides a simple way to do this.

By selecting your PivotTable and navigating to PivotTable Analyze > Fields, Items, & Sets > List Formulas, Excel will instantly create a new worksheet that documents every calculated field and item in your PivotTable, showing their names and formulas.

To modify or delete a calculation, you simply return to the Calculated Field or Calculated Item dialog box, select the formula from the name dropdown, and click the Modify or Delete button.

5. Hands-On Practice

Let's apply what you've learned. Create a new Excel workbook and enter the following data into a worksheet.

Step 1: Create a PivotTable

1. Click inside your data range and go to Insert > PivotTable.
2. Click OK to place the PivotTable on a new sheet.
3. Set up the PivotTable as follows:
   • Rows: Region
   • Columns: Product
   • Values: Sum of Sales, Sum of Cost of Goods

Step 2: Create a Calculated Field

1. Select a cell inside your PivotTable.
2. Go to PivotTable Analyze > Fields, Items, & Sets > Calculated Field.
3. In the Name box, type Profit.
4. In the Formula box, enter the formula: = Sales - 'Cost of Goods' (You can type it or double-click the field names from the list).
5. Click Add, then OK. A Sum of Profit column should now appear in your PivotTable.

Step 3: Create a Calculated Item

1. Click on one of the region names (e.g., 'East') in the first column of your PivotTable.
2. Go to PivotTable Analyze > Fields, Items, & Sets > Calculated Item.
3. In the Name box, type Coastal.
4. In the Formula box, enter the formula: = East + West.
5. Click Add, then OK.
6. You will now see a new 'Coastal' row. To avoid double-counting, right-click on the 'East' label, go to Filter > Hide Selected Items. Do the same for the 'West' label.

Your final PivotTable should now show data for North, South, and the new Coastal region, with columns for Sales, Cost of Goods, and the Profit you calculated.

Conclusion

You have now added a critical layer of analytical capability to your PivotTable skills. By using calculated fields and items, you can create new metrics and groupings on-the-fly, making your reports more insightful without cluttering your source data.

Key Takeaways:

• Calculated Fields create new columns and perform calculations between different fields (e.g., Price * Quantity).
• Calculated Items create new rows and perform calculations between items in the same field (e.g., East + West).
• Always be mindful of double-counting when using calculated items and hide the original items if necessary.
• Use the List Formulas command to quickly audit all custom calculations in a PivotTable.

In our next lesson, we will explore how to make your reports interactive. You'll learn how to add Slicers and Timelines, which are user-friendly filters that allow you or your clients to dynamically explore the data in your PivotTables with just a few clicks.