PivotTables: Add a Calculated Field

PivotTables are analytics machines. Think of all the ways you can slice and dice this data.

Let’s take it even further — profit margin. Click inside your PivotTable. The home ribbon will have a couple of tabs in purple: PivotTable tools. Click on Analyze, then on “Fields, Items, and Sets.” An Insert Calculated Field box will appear.

What is our profit margin formula? Pretty simple — profit divided by sales. But we are not your average analyst. Think about the problems with assuming this will work — think about about a store with sales 0 sales. What will this return? 0 / 0 — that’s an error. How can we avoid this?

Remember our IFERROR formula? Use this here to return a 0 in any instance where the profit margin formula returns an error.

Awesome. Is your formatting wrong? You know how to fix it. We want percentage. Change that.

Notice something a little strange. “Sum of Profit Margin”? That doesn’t really make sense, a profit margin is not something we add. Let’s change the name. Do this by double-clicking on your column label again and changing the name.

Note that you cannot change the name to Profit Margin — technically, that name has already been used because you named your calculated field that. So just call it “Margin.”

Remember to format your field as a percentage. Go out to one decimal place.

Here is the finished product.

This is a beautiful — and powerful — PivotTable. You can easily view the quarter’s sales by region, along with the margins.