If you’ve been following my YouTube channel, you’ll know that many of my Excel tutorials feature a fictional ice cream company, Excellent Ice Cream. In this tutorial, we’re focusing on a common scenario where you want to calculate additional data within a Pivot Table.
Scenario: Displaying Profit in a Pivot Table
Take a look at the screenshot below. The Pivot Table shows the total revenue and total cost for each product (ice cream flavour) in a given month.
But what if we want to calculate the profit for each product? You might be tempted to manually enter a formula into a new column to calculate profit, something like typing =B2-C2 in cell D2 and copying it down the column.
While this technically works, it comes with limitations. The “Profit” column isn’t part of the Pivot Table itself, which means any changes like sorting or filtering could break the layout. That’s where Calculated Fields come in handy.
What Are Calculated Fields?
A Calculated Field allows you to create a new data column using a custom formula, but unlike manually typed columns, the field becomes an integral part of the Pivot Table. This ensures that sorting, filtering, and other Pivot Table features continue to function smoothly.
Want to learn how to create Calculated Fields and see why they’re superior to manual formulas? Watch the step-by-step video tutorial below:
Download a copy of the file used in this video: https://app.box.com/s/5tjpkjv8u5m7iwh4c5btb7r1gz5ggets