Pivot Table Issues

How to Show an Average in an Excel PivotTable Grand Total

Tauseeq Magsi Sep 27, 2026 869 views

Question details

The user wants the grand total row of an Excel PivotTable to display the average of selected columns, rather than the default sum.

How to Show an Average in an Excel PivotTable Grand Total

Product: Excel
Device & OS: not provided
Scenario: Customizing PivotTable calculations for data analysis and reporting.
Observed behavior: The PivotTable defaults to summing the grand total when the detail rows are set to sum, and standard settings do not allow switching only the grand-total row to an average.

Before you start

Before proceeding, ensure your source data contains clean numerical values and no blank rows, as missing data can skew your average calculations.

Solution 1Recommended

Add a Second Value Field Configured as Average

The simplest and most direct workaround is to duplicate the value field in your PivotTable and change its summary calculation to Average.

Since a standard PivotTable cannot calculate a sum for detail rows and an average for the grand total within the exact same column, adding a secondary column dedicated to the average solves the problem without requiring complex formulas.

Duplicate the Value Field

Click and drag the desired numeric field from the PivotTable Fields pane into the 'Values' area a second time.

Open Value Field Settings

Click the drop-down arrow on the newly added field in the 'Values' area and select 'Value Field Settings'.

Change Calculation to Average

In the 'Summarize value field by' tab, select 'Average' from the list of options and click 'OK'.

Review the PivotTable

Your PivotTable will now display both the sum and the average for your data sets, including the grand total row.

Add a Second Value Field Configured as Average

Display Note: This method will add an average column next to your sum column for every detail row, rather than altering just the bottom grand total cell.

Solution 2

Use the Data Model and DAX Measures

For advanced users, loading the data into the Data Model allows you to write a DAX measure that dynamically calculates the average specifically at the grand total level.

Efficient Data Analysis

Analyze Data Seamlessly with WPS Spreadsheet

WPS Office offers powerful and intuitive PivotTable features, allowing you to easily summarize your data by sum, average, or count without complex configurations.

1. Open Your Data: Open your dataset in WPS Spreadsheet and select the data range you want to analyze.
2. Insert a PivotTable: Navigate to the 'Insert' tab on the top ribbon and click 'PivotTable'.
3. Configure Fields: Drag your desired fields into the Rows and Values areas in the side pane.
4. Set to Average: Click on the field in the Values area, select 'Value Field Settings', and choose 'Average' to instantly update your calculations.

100% compatible with Microsoft Excel (.xlsx, .xls) files and formats.Easy-to-use PivotTable interface for quick and accurate data summarization.Lightweight software that processes large datasets and complex calculations quickly.Free alternative with comprehensive data analysis and visualization tools.

microsoft office alternative - wps office

Frequently Asked Questions

Can I format just the grand total cell to show an average without adding a new column?

Standard Excel PivotTables do not support different calculation types (like Sum and Average) for detail rows and the grand total row within the exact same field. You must either use a DAX measure in the Data Model or add a second value field.

Why is my PivotTable average calculation showing the wrong number?

This typically occurs if there are blank cells, zeros, or text values in your source data column. These anomalies can affect the denominator used in the average calculation. Ensure all cells in your data range contain valid numbers.

How do I hide the extra Sum grand total if I only want to see the Average?

You cannot easily hide a specific grand total column without hiding the entire field. A simple visual workaround is to right-click the specific grand total cell you want to hide, select 'Format Cells', and change the font color to match the cell background (e.g., white), making the number invisible.

Excel & Data Problems