How to Add a Grand Total Bar to an Excel Pivot Chart in WPS Spreadsheets
Question details
The user wants to display a Grand Total bar alongside individual region values in an Excel pivot chart, using a calculated item so the original source data remains unchanged.
- Product
- WPS Spreadsheets / Microsoft Excel pivot chart
- Device & OS
- not provided
- Scenario
- The user has a stacked column or bar chart built from a pivot table with region categories and wants a fourth total category without manually adding rows to the source data.
- Observed behavior
- The chart currently shows only the individual region series; the pivot table Grand Total is not displayed as a separate bar.
Open the workbook in WPS Spreadsheets and make sure the pivot table contains the Region field you want to total. Verify that the chart is linked to that pivot table so a new calculated item is included automatically after a refresh.
Add the Grand Total as a calculated item in the Region field
Use this if you want the total to appear as a normal category in the pivot chart without altering the source data.
A calculated item creates an extra Region category inside the pivot table. Because it is stored in the pivot cache, the underlying data range stays unchanged, and the chart can display the new item as a fourth stacked column or bar.
Click any cell inside the pivot table that is feeding the chart.
Go to PivotTable Analyze > Fields, Items & Sets > Calculated Item. In some WPS Spreadsheets versions this appears under the pivot table's Options or Analyze tab.
In the Insert Calculated Item dialog, set Name to "Grand Total" or "All Regions", enter a formula that adds each region item, such as =North+South+East+West, and click Add, then OK.
Keep the pivot table selected, open PivotTable Options > Totals & Filters, and clear "Show grand totals for rows" or "Show grand totals for columns" so only the calculated total remains.
Refresh the pivot table, confirm the new "Grand Total" category appears in the chart, then right-click the series and use Format Data Series to give it a distinct color.
Add a Grand Total Bar to an Excel Pivot Chart with WPS Spreadsheets
WPS Spreadsheets can open Excel workbooks, edit the pivot table, add a calculated Region item, and link it to the chart so the Grand Total appears as a distinct bar without modifying the source data.
- 1. Open the workbook: Launch WPS Spreadsheets, click Menu > Open, and select the .xlsx file that contains the pivot table and pivot chart.
- 2. Select the pivot table: Click any cell inside the pivot table and open the PivotTable Analyze tab.
- 3. Add a calculated Region item: Choose Fields, Items & Sets > Calculated Item, name the item "Grand Total", and enter a formula that sums the region items.
- 4. Hide the built-in Grand Total: Open PivotTable Options > Totals & Filters and turn off the automatic row/column grand total so only the calculated item remains.
- 5. Refresh and save: Refresh the pivot table, confirm the new bar appears in the chart, and save the file in Excel-compatible .xlsx format.
Frequently Asked Questions
Why is the automatic Grand Total not shown as a separate bar in my pivot chart?
Pivot charts normally omit the pivot table's automatic Grand Total as a separate series. You need to add a calculated item in the Region field so the total becomes a normal category in the chart.
Can I add a grand total bar without changing the source data?
Yes. A calculated item works inside the pivot table cache, so the underlying worksheet data is not changed.
Where is Calculated Item in WPS Spreadsheets?
Select a cell in the pivot table, go to PivotTable Analyze > Fields, Items & Sets > Calculated Item. If it is grayed out, make sure you selected the field or heading area rather than a value cell.
How do I remove the duplicate Grand Total after adding my calculated total?
Open PivotTable Options > Totals & Filters and turn off Show grand totals for rows or columns, leaving only your calculated item visible.
Will the chart update when pivot data changes?
Yes. After you refresh the pivot table, the calculated item and the chart series recalculate automatically.
