Daljit Dhadwal

PivotTables are one of the most useful tools in Excel. One feature that can be confusing to use is calculated fields. In this example I show how to use calculated fields to calculate the percentage of sales arising from a specific product by city and have the percentage of sales automatically aggregate to the provincial level. The screenshots are from Excel 2007.

Here’s what the raw data looks like (Sample_Data)

The pivot table is setup as follows:

Next we add a calculated field:

We then define how the new field will be calculated. In this case we’ll call the new field “Percent Sales Product A” and define it as sales of Product A/Total Sales.

Finally we format the calculated field, and we can also drop in the province field to demonstrate that the aggregation works properly.