Calculate values in a PivotTable

To calculate values in a PivotTable in Excel — such as totals, averages, percentages, or custom formulas — you can use built-in summary functions, custom calculations, or calculated fields. Here’s how to do it:

✅ 1. Use Built-In Calculations (Sum, Average, Count, etc.)

Steps:

1. Click inside your PivotTable.
2. In the PivotTable Fields pane, drag a numeric field (like Sales or Quantity) to the Values area.
3. By default, Excel will use Sum.

To change the calculation:

• Click the dropdown next to the field name in the Values area → choose Value Field Settings.
• Select a different function, such as:
  • Average
  • Count
  • Max/Min
  • Product
  • Standard Deviation, etc.
• Click OK.

✅ 2. Show Values As (Percentages, Differences, etc.)

You can display values in a different format without changing the underlying calculation.

Steps:

1. Right-click a value in the PivotTable → select Show Values As.
2. Choose an option like:
   • % of Grand Total
   • % of Column/Row Total
   • Difference From
   • Running Total In
   • % of Parent Row/Column

This is great for comparisons and trends.

✅ 3. Create a Calculated Field (Custom Formula)

To add your own formula based on existing fields:

1. Click anywhere in the PivotTable.
2. Go to the PivotTable Analyze tab → click Fields, Items & Sets → choose Calculated Field.
3. In the dialog:
   • Give your field a name (e.g., Profit).
   • Enter a formula using existing fields (e.g., =Revenue - Cost).
4. Click Add, then OK.

Excel adds the new calculated field to your PivotTable.

🧠 Example:

Suppose your PivotTable has:

• Field: Sales
• Field: Cost

You could add a calculated field:

= Sales - Cost

To show Profit for each item or category.