Pivot Chart Grand Totals: Why Excel Leaves Them Out

No. In Microsoft Excel for Microsoft 365, a PivotChart does not plot a PivotTable’s grand total, even when that total is visible in the table. To show it, use a regular chart or a separate total display linked to the PivotTable.

What a grand total means in a PivotTable

A PivotTable summarizes records by the fields you place in its Rows, Columns, and Values areas. For example, a sales PivotTable might show revenue by product, with a final Grand Total row that adds the revenue for all listed products. If the same report has months across columns, it may also have a Grand Total column that adds each product’s revenue across the displayed months.

That total is a summary of the PivotTable’s current results, not another product, month, or record in the source data. Excel treats it differently from the category values that a chart uses to form its axes and series. This distinction is the reason the number can appear in the table but not as a plotted bar, point, or series in its associated PivotChart.

Why Excel leaves grand totals out of PivotCharts

A PivotChart is designed to visualize the summarized categories and series in a PivotTable. A grand total is an aggregate across those categories, rather than a peer category. Plotting it alongside the values that make up the total can also create a misleading comparison: if the product bars are 120, 180, 90, and 110, a separate bar of 500 is not another product’s result. It is the sum of all four.

For that reason, enabling grand totals in the PivotTable does not add them to the PivotChart. There is no PivotChart setting that makes the grand total appear as an ordinary plotted item. The chart may change when you filter the PivotTable, but that does not change this rule: the displayed total remains a table summary, not a chart category.

What the PivotChart does display

A PivotChart displays the values associated with the PivotTable’s row and column fields. In a chart of revenue by product, product names normally supply the categories and the summarized revenue supplies the values. If months are also included, they may appear as separate series or categories, depending on the chart layout. Filters and slicers can narrow which items are shown.

When you compare the PivotChart with its PivotTable, expect the ordinary category values to match, subject to the same filters and chart choices. Do not expect a separate bar for Grand Total. The chart may also omit other table-only presentation details, such as blank rows or a subtotal line, because its purpose is to plot the underlying summarized items rather than reproduce the PivotTable’s layout cell for cell.

Visible in the table: the Grand Total row or column, if totals are enabled.
Plotted in the PivotChart: the summarized values for chart categories and series.
Controlled separately: whether totals are displayed in the PivotTable and how the chart is arranged.

Check the PivotTable total and chart separately

If a total seems to be missing, first establish whether the issue is only in the chart. Click the PivotTable and look for its Grand Total row or column. On the Windows desktop version of Excel, PivotTable grand totals can be controlled from PivotTable Design > Grand Totals, where options include showing totals for rows and columns, rows only, columns only, or turning them off. The exact visibility option you choose affects the table, not the PivotChart’s ability to plot a grand total.

Then check what the chart is connected to. A PivotChart has field buttons and chart filters that let you change which categories or series it shows. Filtering the chart can make some values disappear, but it does not convert the table’s grand total into a category. If the table total is also absent, review the PivotTable’s Grand Totals setting and the fields or filters in the report.

On Excel for Mac, the relevant PivotTable and chart controls are also on the ribbon, although their arrangement and labels can differ by release. Excel for the web has a more limited editing interface than the Windows desktop app, so use the desktop version if a ribbon option you need is not available in the browser.

Ways to show the total beside a PivotChart

The best workaround depends on whether the total needs to be a plotted value or simply easy to see. For most reports, displaying it as a number next to the chart is clearer than adding a much larger total bar to a chart of individual categories.

Add a linked total label: Put the PivotTable’s grand total in a cell near the chart, then add a text box or callout showing that value. In desktop Excel, a text box can be linked to a worksheet cell through the formula bar. The number can then update when the referenced cell updates. Format it as a KPI, such as “Total revenue: $500,” rather than presenting it as another product.
Build a regular chart from a helper range: Create a separate worksheet range containing the categories, values, and a Grand Total entry, then make a standard chart from that range. A regular chart can plot the total because it uses a normal cell range, not PivotChart rules. Keep the helper range aligned with the PivotTable when its categories or size change.
Use a separate chart or visual: A small card, separate total chart, or clearly labeled text display can show the overall figure without putting it on the same scale as smaller category values.

A standard chart made directly from PivotTable cells can be a quick solution, but its source range may not expand correctly when a refresh adds categories or changes the report’s size. A helper range is more deliberate: it gives you control over which rows the chart uses and where the grand total appears. The trade-off is that you must keep that range connected to the current PivotTable results.

Use GETPIVOTDATA for a filter-aware total

A helper cell can retrieve the total from a PivotTable with GETPIVOTDATA. This is useful when a separate label or standard chart should reflect the PivotTable’s current filters. The formula refers to a cell inside the PivotTable and names the value field as it appears in the PivotTable.

Assume the PivotTable starts at A3, A3 is inside the PivotTable, its displayed Values field is named Revenue, and the current product revenues are 120, 180, 90, and 110. With those conditions, enter this formula in a helper cell:

=GETPIVOTDATA("Revenue",$A$3)

The formula returns 500, because 120 + 180 + 90 + 110 = 500. If the PivotTable’s value caption is “Sum of Revenue” rather than “Revenue,” use the exact displayed caption in the formula. A cell outside the PivotTable will not work as the PivotTable reference; use an anchor cell within it. With no field-and-item pairs specified, the formula requests the overall value for that data field, reflecting the PivotTable’s current report filters and slicer selections.

You can link a text box to the helper cell to present the total beside the chart. Alternatively, use the helper cell in a standard chart range. If you build a chart with both category values and the grand total, label the total clearly and consider whether it belongs on a separate chart: a total that is the sum of all categories will often dwarf each individual bar and make the category differences harder to read.

Choose a chart design that does not mislead

Adding a grand total as a plotted category is technically possible in a standard chart, but it is not always a useful comparison. If the categories are components of the total, the total is mathematically larger than any one component. In a column chart, the individual bars may become visually small beside it. Readers may also mistake Grand Total for a genuine category unless the chart explains what it represents.

Consider the question the graphic is meant to answer. If the goal is to compare products, plot the products and show the overall total in a nearby KPI. If the goal is to compare actual results with an overall benchmark, a reference line or a separate comparison may be more suitable, provided the chart’s scale and labels make the meaning clear. Avoid adding a total as though it were an additional observation when it is actually calculated from the observations already plotted.

Also be careful with summing a PivotTable range that already contains a Grand Total row. If you include the individual categories and the total in another sum, you count the same underlying values twice. The total row is useful as a display value, but it is not an extra amount to add to the categories.

Refreshes, filters, and keeping workarounds current

PivotTables can change size after a refresh, when a new category appears, or when a filter selection changes. A regular chart tied to a fixed block of PivotTable cells may then omit new items, include an unwanted blank row, or point at the wrong cells. Before relying on such a chart, refresh the PivotTable and confirm that the chart’s source data still covers the intended values.

A helper range offers more control, but it must also be designed for the report’s expected changes. GETPIVOTDATA is useful for retrieving a known value or total, while a list of category values may need additional formulas or a maintained set of references if the categories vary. Test the display after changing a slicer or report filter, and after refreshing the PivotTable. A linked total that updates correctly does not guarantee that every category in a separate chart does too.

Finally, distinguish a refresh from a filter change. A filter or slicer changes which existing records contribute to the displayed PivotTable result. A refresh updates the PivotTable from its source data. The grand total shown by the table can change in either case, but the PivotChart still does not plot that total as a category.

Frequently Asked Questions

Can I turn on grand totals in a PivotChart?
No. Excel PivotCharts do not plot PivotTable grand totals, and there is no setting to enable them as chart points or categories. Show the total in a linked cell, text box, or separate visual instead.

Does showing Grand Total in the PivotTable make it appear in the chart?
No. The PivotTable’s Grand Totals control changes what is displayed in the table. The PivotChart continues to plot its categories and series, not the table’s overall total.

Will a GETPIVOTDATA total change when I use a slicer?
It normally reflects the current filtered PivotTable result when the formula references that PivotTable and requests its overall value. Check the helper cell after changing a slicer, especially if the workbook contains multiple PivotTables or the formula refers to a different one.

Can I include Grand Total in an ordinary Excel chart?
Yes. A regular chart can use a worksheet range that includes a Grand Total row or a helper range containing that value. Make the label explicit, and remember that the total may overwhelm the smaller category values on the same axis.

Why does my total look doubled?
A common cause is summing a range that includes both the category values and their Grand Total row. The total already contains the category amounts, so adding it to those amounts counts them again.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.