'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Display Totals on a Stacked Bar Chart & Export to PowerPoint
Question details
The user wants to display overall total quantities at the top of each stacked bar in a chart and needs a method to move or export the chart into a PowerPoint presentation.
- Product
- Spreadsheet and Presentation Software
- Device & OS
- not provided
- Scenario
- Creating detailed data visualizations and automating the export of these charts to a presentation slide.
- Observed behavior
- Stacked bar charts typically display values for individual segments rather than the overall total, and exporting charts from a database or spreadsheet to a presentation often requires manual copy-pasting or VBA workarounds.
Ensure your chart data is organized properly, and calculate the overall totals for each stacked bar in a new column or row before modifying your chart.
Use a Helper Series to Display Totals
Add an invisible data series to your stacked bar chart that contains the yearly or category totals and displays them as data labels.
Since most spreadsheet programs do not have a built-in 'show total' button for stacked charts, you can use a helper series. This involves adding the totals as an extra layer on your chart, displaying its labels, and hiding the actual bars or lines of that series.
In your data source, add a new column or row named 'Total' and use the SUM function to calculate the total for each stacked category.
Right-click your stacked bar chart and select 'Select Data'. Add a new data series using the 'Total' column you just created.
Right-click the newly added total series on the chart, select 'Change Series Chart Type', and change it to a Line chart or a Scatter plot so it sits at the top of your stacked bars.
Select the new helper series, click the 'Chart Elements' plus icon, and check 'Data Labels' to display the total numbers.
Right-click the helper series, select 'Format Data Series', and set both the Fill color and Line color to 'No Fill' and 'No Line'. Now, only the total labels will remain visible.
Export Chart to PowerPoint via VBA
Automate the process of moving an Access or Excel chart into PowerPoint by using a VBA macro.
Create and Export Charts Easily with WPS Office
WPS Office provides a highly integrated suite where creating stacked bar charts with totals and exporting them to a presentation is fast and straightforward, completely eliminating the need for complex VBA code.
- 1. Create Your Chart in WPS Spreadsheet: Open your data in WPS Spreadsheet, highlight your data including a totals column, and insert a stacked bar chart.
- 2. Format the Totals: Change the total series to a line chart, add data labels, and set the line to 'No Line' to cleanly display your totals at the top.
- 3. Copy the Chart: Right-click the finished chart in WPS Spreadsheet and select 'Copy', or simply press Ctrl+C.
- 4. Paste into WPS Presentation: Open WPS Presentation, navigate to your desired slide, right-click, and choose 'Paste' (or press Ctrl+V) to insert your fully formatted chart.
Frequently Asked Questions
Can I display totals on a stacked bar chart without using a helper series?
By default, spreadsheet applications display data labels for individual segments of a stacked chart rather than the cumulative total. Using a helper series is the standard and most reliable method to display overall totals at the top of the bars.
How do I make the helper series completely invisible?
Click on the helper series in your chart to select it. Then, open the Format Data Series pane and set both the 'Fill' and 'Border' (or 'Line') options to 'No Fill' and 'No Line'. Only the data labels you added will remain visible.
Why does my VBA code fail to copy the chart to PowerPoint?
This usually happens if the chart name in the code does not match the actual chart object name on your sheet, or if the PowerPoint Object Library reference is not enabled in your VBA editor. Double-check that 'CHART_NAME' is replaced with the correct identifier.
Will my copied chart update automatically in the presentation if the original data changes?
If you use a simple Copy and Paste, the chart is usually pasted as a static object or embedded workbook. To make it update automatically, use 'Paste Special' and select 'Paste Link' when moving the chart into your presentation.
