Excel Pivot Charts: Can They Show Grand Totals?

Excel PivotCharts do not plot a PivotTable’s grand-total row or column as chart data. You can show the grand total in the PivotTable, place it beside the chart with a linked cell or text box, or build a regular chart from a separate range that includes the total.

What a PivotChart does with totals

A PivotChart visualizes the categories and value series in its associated PivotTable. For example, a PivotTable might summarize monthly sales by region, with months in rows and regions in columns. A PivotChart can plot the monthly sales series for each region, but it does not add the PivotTable’s Grand Total row or Grand Total column as another plotted category or series.

This is generally intentional, not a setting that has been switched off. A grand total summarizes the values already represented in the chart. Plotting it as if it were another month or another region can make a chart misleading: the total may be much larger than an individual value, and including it can distort the scale. Excel therefore treats PivotTable totals as summary information rather than ordinary chart points.

It helps to distinguish three things that people often call “the total”: a PivotTable grand total, a chart data point that represents a total, and a number displayed near the chart. The first is calculated and shown in the PivotTable; the second is not automatically included in a PivotChart; the third can be added with a linked cell, text box, or separate chart.

Show the grand total in the PivotTable

If you only need the total visible near the chart, first make sure the PivotTable itself is configured to show it. Select a cell in the PivotTable, then choose PivotTable Design > Grand Totals. Choose On for Rows and Columns to show both, or select the option that suits your layout. The row grand total commonly appears beneath the listed row items; the column grand total commonly appears to the right of the listed columns.

This changes the PivotTable display, not the PivotChart’s plotted series. The total may be visible in the worksheet while still being absent from the chart. If you do not see the Design tab, click inside the PivotTable first; the PivotTable contextual tabs appear when the table is selected. On Mac, the contextual PivotTable ribbon tabs are available as well, although their placement can vary with the Excel window and ribbon layout.

Grand totals depend on the value field’s summary calculation. A Sales field may be summarized by Sum, while a field containing transaction counts may use Count. To check or change the calculation, right-click a value in the PivotTable, choose Summarize Values By, and select an appropriate option. A displayed grand total is only meaningful if the underlying summary matches what you intend to report.

Display a total beside the chart

For a dashboard, a common solution is to show the total in a worksheet cell and link a text box on the chart to that cell. This preserves the PivotChart’s normal category series while keeping the overall figure in view. It also allows the displayed value to update when the PivotTable recalculates, provided the linked cell continues to refer to the correct total.

Turn on the PivotTable grand total and identify the cell containing the figure you want to show.
Select the chart and use Insert > Text Box to draw a text box on or near it.
Select the text box border, click the formula bar, type a reference to the total cell, such as =Sheet1!$E$14, and press Enter. Replace the sheet name and address with the actual location in your workbook.
Format the linked cell with a number format that makes the amount clear, then position and style the text box to suit the chart.

A linked text box displays the contents of its referenced cell; it does not add the total as a plotted point. If the worksheet is renamed or the PivotTable layout changes, check the reference. A simpler alternative is to place the total in a nearby cell and format it as a prominent KPI, without putting it inside the chart area. That can be easier to maintain in a dashboard with several charts.

Build a regular chart that includes a total

If the total must appear as a data point, create a standard chart from a separate helper range rather than trying to force it into the PivotChart. Copy or reference the categories and values you need in that range, then include the grand total there as its own row or series. Select the helper range and choose Insert followed by a suitable chart type. Unlike a PivotChart, a regular chart is not directly controlled by the PivotTable’s fields and filters.

Think carefully about what the extra point means. Suppose monthly sales are $20,000, $25,000, and $30,000. A fourth category called “Grand Total” at $75,000 is mathematically correct, but it is not a fourth month. On a column chart it can dominate the scale and invite readers to compare unlike things. If the point is to show performance over time, keep the monthly chart separate and display the $75,000 as a KPI. If comparing each region’s overall total is the goal, a separate bar chart of regional totals may be clearer.

A regular chart based on a helper range may need extra care when the PivotTable is filtered or refreshed. The helper range must still point to the correct categories and totals. You can keep the PivotChart for interactive exploration and use a separate chart or linked display for the total, but be explicit about which filters affect each view so the figures do not appear to disagree.

Choose the right workaround

The best approach depends on what the reader needs to learn from the chart. A total displayed as a number is often the clearest choice when it provides context for the chart rather than a value to compare with its categories. A separate chart is more appropriate when the total itself is part of a meaningful comparison.

Need a visible overall figure: show the PivotTable grand total and link a cell or text box to it.
Need to compare totals across groups: make a separate chart whose categories are the groups and whose values are their totals.
Need the total to appear as a point on the same visual: use a regular chart from a helper range, and label the total clearly so it is not mistaken for an ordinary category.
Need an interactive PivotChart: retain the PivotChart for its field-based filtering and display the overall figure separately.

A common misconception is that turning on Grand Totals in PivotTable Design should add a new chart series. That command controls the PivotTable’s displayed totals; it does not change the PivotChart’s rule for what it plots. Likewise, refreshing the PivotTable does not convert the PivotChart into a standard chart. Choose the workaround based on the message you want the visual to communicate, not just on whether a total can be forced into the same plot.

Why a total may seem to be missing

When the PivotTable shows a total but the chart does not, that is normally expected behavior. If the total is also missing from the PivotTable, check whether grand totals are turned off, whether the PivotTable has been filtered, and whether the relevant value field is configured as expected. Filters can change the numbers being summarized, so a grand total may legitimately change even though the chart continues to show the same set of series.

Also check whether the chart is actually a PivotChart. A standard chart may have been created from a range that includes a PivotTable, but it is not necessarily governed by PivotChart behavior. Select the chart and inspect the available chart and PivotChart contextual tabs. If it is a regular chart, its source range determines what is plotted; if that range includes a total row, Excel may chart it as another category.

Finally, consider whether the total is appropriate for the chart type. A grand total is an aggregate, while the other points may be individual dates, products, or regions. Even when a standard chart can plot both, that does not mean the comparison is useful. A separate label or KPI often communicates the overall figure with less risk of confusing readers.

Keep the displayed total reliable

PivotTables can change shape when fields are added, removed, filtered, or rearranged. A linked text box that points to a fixed address may then point to a different cell or to a blank. After changing the PivotTable layout, verify both the grand-total cell and any linked display. If the chart is used for recurring reports, include this check in the refresh process.

Check the scope of the number as well. A grand total may reflect current report filters, slicers, and field selections rather than every record in the source data. A total that changes after selecting a region or date range may be behaving correctly: it now summarizes the visible selection. Label the figure clearly, for example “Filtered sales total,” if readers might otherwise assume it covers the entire dataset.

For a polished report, use consistent number formats and labels. Currency totals should use the same currency and decimal conventions as the chart; counts should not be formatted as currency. If a text box is linked to a formatted cell, check how the displayed value appears after the source cell’s number format changes. Clear wording and a visible filter context help readers understand exactly what the total includes.

Frequently Asked Questions

Can I add a grand total line to an Excel PivotChart?

Not directly as a PivotChart series sourced from the PivotTable grand total. Use a regular chart with a helper range if the total must be plotted, or display the total separately if the goal is simply to make it visible.

Why does my PivotTable show a grand total but my PivotChart does not?

PivotCharts do not plot the PivotTable’s grand-total row or column. The total can still be enabled and displayed in the PivotTable; showing it there does not add it to the chart.

Can I show the total as a number on the chart?

Yes. Put the total in a worksheet cell and link a chart text box to that cell, or place a formatted cell beside the chart. This displays the number without treating it as another chart value.

Will the displayed total update when I filter or refresh the PivotTable?

The PivotTable grand total recalculates based on its current data and filters. A linked display updates when its referenced cell changes, but check the reference after rearranging the PivotTable because the total may move.

Should I include a grand total as another category?

Usually not when the other categories are individual periods or groups: the total is an aggregate and can distort the scale or imply a false comparison. Use a separate KPI or a clearly labeled chart designed specifically to compare totals.

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.