To change a PivotTable’s grand-total calculation in Excel for Microsoft 365 on Windows, right-click a value in the field, choose Summarize Values By, and select the aggregation you want, such as Sum, Count, or Average. The grand total follows that field’s summary setting; Excel does not let you type an arbitrary formula directly into the PivotTable’s grand-total cell.
What the Grand Total Setting Controls
A PivotTable summarizes source records into groups, then applies a calculation to each value field. If a field contains sales amounts, for example, Excel may sum them for each region and show a grand total for all included regions. That grand total is calculated by the PivotTable from its source data; it is not an ordinary worksheet cell with a formula you can replace.
This distinction matters because “change the grand total formula” can mean two different things. You might want to change the kind of summary Excel uses, such as changing a sum to an average. Or you might want a custom calculation, such as profit margin or a weighted rate. The first is a built-in value-field setting. The second usually calls for a calculated field, a Data Model measure, or a separate worksheet formula.
Editing the displayed total cell directly is not a reliable workaround. PivotTable cells are managed by the report, so the edit may be rejected, overwritten when the PivotTable refreshes, or lost when its layout changes.
Change the Summary Used for the Grand Total
In a standard PivotTable, change the summary for the value field itself. Excel then uses that summary for the grouped values and the grand total.
- Click a number in the value field you want to change. Make sure it is a value in the body of the PivotTable, not a label or the grand-total cell.
- Right-click and choose Summarize Values By. Alternatively, open the value field’s menu in the PivotTable Fields pane and choose Value Field Settings.
- On the Summarize Values By tab, select a calculation such as Sum, Count, or Average.
- Select OK. Check both the group results and the grand total to confirm the new summary makes sense.
You can also select a value and use PivotTable Analyze > Field Settings or open Value Field Settings from the field list; the exact route depends on which field or control you select. If Excel labels a field Sum of Amount, that name is a useful clue about its current aggregation.
Choose the Right Calculation
The right setting depends on what the source values represent. A total is not automatically the most meaningful summary just because Excel offers one. Consider the question the report is meant to answer before changing the calculation.
- Sum adds numeric values. It is appropriate for additive measures such as units sold, costs, and revenue when each source row contributes an amount to the total.
- Count counts nonblank entries, including text. Use it to count records with a populated value, not to add numbers.
- Count Numbers counts numeric entries only. It can help when the field contains a mixture of numbers, text, and blanks.
- Average calculates the mean of the underlying values. It is useful for measures such as time per case when the individual records are the observations you intend to average.
- Maximum and Minimum show the largest and smallest underlying values. These can answer questions about peak or lowest recorded amounts, but they are not additive totals.
- Product, standard deviation, and variance are available for suitable numeric data. They are specialized statistics, so confirm that their meaning fits the question before using them.
For example, suppose one region has nine orders averaging $10 each and another has one order averaging $20. The overall average across the ten orders is $11, not $15. A PivotTable’s Average grand total reflects the underlying records, rather than taking a simple average of the two displayed regional averages. This is one reason the total may differ from an average calculated manually from the visible group results.
Grand Totals Are Different from Show Values As
Show Values As is a separate feature from Summarize Values By. It can display values as a percentage of a total, a running total, a difference from another item, or another comparison. It changes how values are presented relative to other PivotTable results; it does not provide a box for entering a custom formula for the grand-total cell.
To find it, right-click a value and choose Show Values As, or open Value Field Settings and select the Show Values As tab. The available choices depend on the PivotTable’s fields and arrangement. For example, a percentage-of-column-total view can help compare each product’s contribution within its category. If the displayed result is unexpected, check the base field, base item, and row or column layout as well as the chosen calculation.
There is also a separate display control for whether grand totals appear at all. Select the PivotTable, then choose PivotTable Design > Grand Totals and select an option such as On for Rows and Columns or Off for Rows and Columns. This shows or hides totals; it does not change their calculation. Subtotals are controlled separately through PivotTable Design > Subtotals.
When You Need a Custom Calculation
If the built-in summaries do not answer the question, choose a method that fits the type of PivotTable and the calculation. A custom calculation is not simply a different label for the grand total: it may need to be evaluated from the source rows or recalculated from aggregated values.
For a traditional PivotTable that is not based on the Data Model, a calculated field can create a value from other source fields. Select the PivotTable and look for PivotTable Analyze > Fields, Items & Sets > Calculated Field. In the dialog, name the new field, build its expression from the listed source fields, and add it to the Values area. This feature is not available for every kind of PivotTable, including many PivotTables based on the Data Model or an OLAP source.
Be especially careful with ratios. A row-level calculation such as profit divided by sales can produce a different grand total from total profit divided by total sales. Summing or averaging individual row-level percentages does not generally produce a weighted overall percentage. If the report needs an overall rate based on aggregated amounts, a measure in the Data Model is often the better approach: a DAX measure is evaluated in the PivotTable’s current filter context, including at the grand-total level. Creating or editing measures requires a Data Model PivotTable and familiarity with its source columns and business definition.
For a one-off result, calculate outside the PivotTable instead. Put the result in a normal worksheet cell beside the report, using source data or PivotTable results as appropriate. If the PivotTable may move or change size, a reference based on GETPIVOTDATA can retrieve a particular value from it more reliably than a hard-coded address. Keep the outside calculation visibly separate, and check it again after refreshing or changing filters.
Why a Grand Total May Look Wrong
Before changing settings, check the source data and the report’s layout. A surprising total often comes from an unintended field type, an unsuitable aggregation, or a mismatch between the total being shown and the question being asked.
- Numbers stored as text: A field that looks numeric may not be treated as numeric if its source values are stored as text. Check the source column for inconsistent entries, correct the data, and refresh the PivotTable.
- Count instead of Sum: If the field contains text or mixed data, Excel may default to Count. Check the field’s summary setting rather than assuming the displayed label describes the source data correctly.
- Filters and hidden items: Report filters, slicers, and field selections affect which records are included. A grand total usually represents the records currently included in the PivotTable, not necessarily every row in the source.
- Averages of different group sizes: The total average is based on the records, not necessarily on the visible group averages. Comparing those numbers as though each group had equal weight can be misleading.
- Stale source data: After editing the source, select the PivotTable and choose PivotTable Analyze > Refresh. If new rows fall outside the source range, update the data source or use an Excel table as the source so added rows are easier to include.
- Distinct counts: A distinct count is different from a regular count. It is available for PivotTables using the Data Model, and its total can differ from adding the distinct counts shown for individual groups because the same item may appear in more than one group.
Windows, Mac, and Excel for the Web
The steps above use Excel for Microsoft 365 on Windows. On Windows, the PivotTable Analyze and PivotTable Design tabs appear when a PivotTable is selected. On Mac, select the PivotTable and use the PivotTable ribbon tabs; names or placement can vary by Excel release. Look for the value field’s settings to change its summary, and for the Design tab’s Grand Totals control to show or hide totals.
Excel for the web supports common PivotTable interactions, but some authoring features and menu choices may not match the Windows desktop version. If a calculated field, Data Model measure, or advanced setting is unavailable in the web interface, open the workbook in the desktop app. Changing the summary setting is different from editing a custom calculation, so verify which feature you need before looking for a particular command.
Frequently Asked Questions
Can I type a formula directly into a PivotTable grand total?
No. Excel manages that cell as part of the PivotTable. Change the value field’s summary setting, create a supported calculated field or measure, or put a separate calculation outside the PivotTable.
How do I change a grand total from Sum to Average?
Right-click a value in the relevant field, choose Summarize Values By > Average, and confirm. The PivotTable updates the group values and the grand total using the average of the underlying records.
Why is my grand total not the sum of the visible rows?
The total may use a different aggregation, or the visible rows may be averages, percentages, distinct counts, or other non-additive values. Check Value Field Settings, the Show Values As option, and any filters before comparing the figures.
Can I hide the grand total but keep subtotals?
Yes. Select the PivotTable and use PivotTable Design > Grand Totals > Off for Rows and Columns. Subtotals have their own control under PivotTable Design > Subtotals, so they can remain visible.
How do I calculate a custom percentage total?
First define what the percentage means. For an overall rate based on total amounts, use an appropriate Data Model measure or calculate the rate outside the PivotTable from the relevant totals. Avoid assuming that adding or averaging row-level percentages will give the correct overall result.
Related
- Excel Keyboard Shortcuts for Selecting Cells Quickly
- Excel Number Format to Show Values in Thousands
- Excel Number Format: Display Zero Values as Dashes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Google Sheets Distribution Charts: Types and SetupA Google Sheets distribution chart is usually a histogram: it groups numerical values into ranges, or bins, and…
- Excel Pivot Chart Scatter Plot: Limits and WorkaroundsNo—Excel for Microsoft 365 on Windows does not offer an XY (scatter) chart as a PivotChart type. You…
- Combo Chart Icon in Excel: Where to Find and Use ItIn Excel for Microsoft 365 on Windows, find the combo chart icon on the Insert tab, in the…
- Excel Waterfall Chart Inputs and SetupExcel waterfall chart inputs are a list of categories and one matching list of numeric changes or balances:…
- How to Make a Dashboard in ExcelTo make a dashboard in Excel, organize your data in a clean Excel Table, summarize it with PivotTables,…
- Filter in Power Pivot: Ways to Narrow PivotTable DataTo filter Power Pivot data, add the Data Model to a PivotTable and use its field drop-downs, report…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.