You have a pivot table in Excel, but the numbers are showing as sums when you need averages or counts. This happens because Excel automatically uses the Sum function for numeric fields and Count for text fields. This article shows you how to change the calculation from Sum to Average, Count, or other methods in a few clicks.
Key Takeaways: Changing Pivot Table Calculations
- Right-click a value > Summarize Values By: The fastest way to switch between Sum, Average, Count, and other standard functions.
- Value Field Settings dialog: Provides access to all calculation options, including custom number formatting and renaming the field.
- PivotTable Analyze > Field Settings: An alternative ribbon path to open the Value Field Settings dialog for precise control.
Understanding Pivot Table Value Calculations
A pivot table summarizes your source data by grouping it into categories. The way it calculates the numbers in the Values area is called the aggregation or summary function. By default, if you drag a numeric field like Sales or Quantity into the Values area, Excel assumes you want to see the total and applies the Sum function. If you drag a text field into Values, Excel uses the Count function because text cannot be added.
You are not limited to these defaults. You can change the function for any value field. Common alternatives include Average to find the mean, Count Numbers to ignore blank cells, Max, Min, and Product. You can also apply multiple different functions to the same source field, creating separate value columns for Sum, Average, and Count side-by-side.
Steps to Change the Summary Function
You can modify the calculation using the right-click menu or the ribbon. The result is the same.
Method 1: Using the Right-Click Menu
- Right-click any number inside the pivot table’s Values area
Click directly on a cell that contains a summed total, average, or count. Do not click on row or column labels. - Hover over Summarize Values By in the context menu
A submenu will appear listing the most common aggregation functions. - Select your new function
Click Average, Count, Max, Min, or another function. The pivot table updates immediately.
Method 2: Using the Value Field Settings Dialog
This method gives you more options, like custom names for your value field.
- Right-click a value and choose Value Field Settings
Alternatively, click any value, then go to the PivotTable Analyze tab on the ribbon and click Field Settings. - Choose the Summarize Values By tab
This tab is selected by default when you open the dialog. - Select the calculation from the list
Scroll through the list which includes Sum, Count, Average, Max, Min, Product, and more. - Rename the field and click OK
In the Custom Name box at the top, type a clear name like Average Sales instead of Sum of Sales. Click OK to apply.
Common Mistakes and Limitations
Pivot Table Shows Count Instead of Sum for Numbers
If your numeric data has any blank cells or text entries, Excel might default to the Count function. To fix this, ensure your source data column contains only numbers. You can also force the Sum function by right-clicking the value, choosing Summarize Values By, and selecting Sum.
Cannot Calculate Average Because Field is Set to Count
You cannot directly change a text field’s calculation to Average. The Average function requires numeric data. First, check your source data to confirm the field contains numbers. If it does, the column might be formatted as text. Convert the text to numbers in the source sheet, then refresh the pivot table.
Need to Show Both Sum and Average for the Same Data
Drag the same source field from the PivotTable Fields list into the Values area a second time. Excel will create a second value column. Then, change the calculation for one column to Sum and the other to Average using the methods above. Rename each field clearly to avoid confusion.
Summary Function Comparison
| Item | Sum | Average | Count |
|---|---|---|---|
| Primary Use | Calculate total of all values | Find the mean value | Count all items, including blanks and text |
| Data Type | Numbers only | Numbers only | Any data type |
| Ignores Blank Cells | Yes | Yes | No |
| Alternative Function | Product (multiply all values) | Median (middle value) | Count Numbers (ignores text and blanks) |
| Best For | Financial totals, quantities | Performance metrics, grades | Taking attendance, list items |
You can now change any pivot table value from Sum to Average, Count, or other functions. Use the right-click method for speed or the Value Field Settings dialog for full control. Try adding the same field to Values twice to display different calculations side-by-side. For advanced analysis, explore the Show Values As tab in Value Field Settings to display calculations as percentages of row or column totals.