How to add live total labels to graphs and charts in Excel and PowerPoint
- Categories: PowerPoint design
- Comments: 1
PowerPoint has a wealth of options for graphs and charts. It offers great ways to display your data visually. For example, a stacked column chart is a way of showing a part-to-whole relationship in the data it represents, whilst also indicating total values of each category. However, if you want the data you are sharing to be both easily understood and editable, you need to know how to add different types of labels to your charts.
As PowerPoint leverages the functionality of Excel, if you know how to add labels to an Excel graph, you can do it in PowerPoint and vice versa. Though most people can easily create the chart they need, how to add live total labels remains a mystery for many. Lucky for you, we have a great hack to share!
What labels are available?
In PowerPoint and Excel you can add a range of data labels, as well as axis labels, to a chart. These can help your audience easily interpret your data. If you create a stacked column chart in PowerPoint, each segment of the chart will be labelled automatically. To add labels in Excel, click the add Chart Elements icon and select the elements you want to add.
When adding axis labels, you need to consider text size – what size screen will the chart be displayed on, will your audience be able to see the labels clearly? Similarly, you should consider the orientation of your labels; though y-axis labels are vertical by default, horizontal labels are easier to read. To change the orientation of a label right click on the label, select Format Axis Title , then Text Options , then Text Box , then chose the Text Direction .
Now you have axis labels and some data labels, but if you want the totals at the top you have to manually add text boxes and format them by hand. The downside is that when you need to update the data, you have to go back and reformat all of those labels so that they are still aligned properly.
However, if you want to produce something like this where every data point label is live, with a little bit of up-front finagling, there is a way to generate total values automatically using the Combination Chart option.
Step 1: Edit your data
In PowerPoint, get your stacked column chart and right click to Edit Data . This will open up a spreadsheet with your data in it. Each row is a category, and each column is a subcategory.
You need to create a new column for the totals: name it Total for simplicity. Then, write a short piece of script to generate an automatic sum of the values in that row: =sum(first cell:lastcell) . For the first row this looks like: = sum(b2:c2) .
Do the same on each row, then drag the bottom corner of the blue box so that the Total column is included in the data selection, then close the data editor.
Step 2: Update your chart type
Exit the data editor, or click away from your table in Excel, and right click on your chart again. Select Change Chart Type and select Combo from the very bottom of the list. Change the “Total” series from a Stacked Column to a Line chart. Press OK.
Step 3: Format your totals
Now all the totals are represented on a line. To make it appear as if these totals are just connected to the top of each column, first select the line and change the colour to No outline . Then select all the total labels and right click to select Format Data Label . Change the label position to Above . You can follow the same steps in both Excel and PowerPoint.
Done and dusted! When you need to update the data in the stacked columns, the total labels will automatically update in both the spreadsheet view and in the live chart view, and automatically move to the correct position. You can use this technique on other chart and graph types, such as a grouped bar chart instead of a stacked column.
Now you know how to add labels in an Excel graph, check out our video resource on PowerPoint graphs and charts and our blog post on how to tell a data story through animation . And keep an eye on our Events page for our regular masterclass on the topic!
Related articles
3 powerpoint valentine’s day cards to download and share.
- PowerPoint design
- Comments: 5
February 14th is around the corner, so the BrightCarbon team have crafted three bespoke Valentine's day cards: all built in PowerPoint!
Updating graphics easily using Slide Zooms in PowerPoint
- PowerPoint design / PowerPoint productivity
- Comments: 3
Some presentations require the exact same chart, graphic, or image on multiple slides. A new feature of PowerPoint 2016 is zoom links. With this feature, you can work around the issues of having multiple versions of a graphic throughout a presentation: PowerPoint will do the updating legwork for you! Read on to find how to make the most of this new feature.
Three PowerPoint plug-ins to try today
- PowerPoint design / Presentation technology
I often find myself thinking about PowerPoint, and there's nothing I like more than finding out about a feature that helps me improve my PowerPoint productivity, or helps me to make something really cool. I've had a quick nosey around the Office Store and I've found three pretty nifty PowerPoint plug-ins that claim to do just that: Shutterstock Images, Mentimeter, and Symbols and Characters. I’ve given them a test drive so you don’t have to! Let the testing begin!
Very clever, thank you so much
Leave a Reply Cancel reply
Save my name and email in this browser for the next time I comment.
Join the BrightCarbon mailing list for monthly invites and resources
I absolutely love this, thank you so much. I have shared your fabulous resources with many folks. Thanks for all the brilliant work you do! Michaela Butterworth State of Kansas
- BUSINESS ANALYTICS
- INVESTMENT ANALYSIS
- EXCEL CHARTS
- FINANCIAL MODELING
- DATA VISUALIZATION
- BUSINESS MODELING
Views > Excel charts > How to add labels to show totals in stacked column charts in excel
Stacked Column Charts show how the components contributing to the total vary over time. For example, the chart below shows how the yearly sales of three products add up to the total sales of the company.
One drawback of Stacked Column Charts is that since only one segment of the components is measured against a common baseline , it becomes difficult to compare how all the other floating segments change over time.
The reason why the chart below works as a stacked Column Chart is because the primary purpose of the chart is to compare the sales of Product C versus the sum of sales of Product A and B. And since the sum of sales of products A and B is relatively stable over time, it formed a common base line for Product C sales.
Adding labels to show sales by product is very easy to do in Excel. However, adding labels to show total sales requires some additional effort.
Let’s build this chart in Excel.
The dataset for the graphic above is in the range A2:D10 as shown in the screenshot below.
3. Highlight the range A2:E10 and then, on the Insert tab, in the Charts group, click Insert Column or Bar Chart > Clustered Column . The chart should look like this:
HOW TO ADD LABELS TO SHOW TOTALS IN STACKED COLUMN CHARTS IN EXCEL
Or alternatively buy product on Gumroad
CREATE AWSOME CHARTS
4. While the chart is selected, click the Change Chart Type icon in the Type group under the Chart Design tab. 5. In the Change Chart Type dialog box, select Combo under the All Charts tab. 6. Next, under the Choose the chart type and axis for your data series set the Chart Type to Line for the Total data series and keep Stacked Column for the Product A , Product B and Product C data series as per the below screenshot:
7. Press the Ok button to close the Change Chart Type dialog box. The chart should look like this:
8. In the chart, right-click the " Total " series and then, on the shortcut menu, select Add Data Labels . 9. Next, select the labels and then, in the Format Data Labels pane, under Label Options , set the Label Position to Above . 10. While the labels are still selected set their font to Bold . 11. Next, select the " Total " data series and set its Shape Outline to No Outline in the Shape Styles group under the Format tab. The chart should look like this:
9. Lets do some cleaning. Delete the chart title, the legend, the vertical scale and horizontal gridline. 10. Turn off the border of the Chart Area by setting its Shape Outline to No Outline . 11. Change the chart Font to Trebuchet MS , its color to Black and size to 9. The chart should look like this:
12. Lets do some formatting. Select the "Product C" data series in the chart and then set its Shape Fill , under the Format tab, to light orange and its Shape Outline to white. 13. Repeat step 12 for Product A and Product B data series by choosing light blue and light green for Fill color. 14. Next, select the horizontal axis and set its Shape Outline , under the Format tab, to black and its Weight to 11/2 pts. 15. In the chart, right-click the " Product C " series and, on the shortcut menu, select Format Data Series . In the Format Data Series pane, under Series Options , set the Gap Width to 50%.The chart should look like this:
16. In the chart, right-click the " Product C " data series and, on the shortcut menu, select Add Data Labels . 17. Repeat step 16 for Product A and Product B data series. 18. Next, in the chart select the Legend and then click on Total to select it. Press the Delete key. The chart should look like this:
19. Finally, add a chart title, data source and annotations. 20. Adjust the size of the chart and the chart should look like this:
Thank you for reading, and I hope you found this useful. If you have any questions, find me on Twitter and ask me anything.
/simplexct/images/Fig1-jccc2.png "powerpoint show total on stacked bar chart")
1. First, we need to add the total sales by year. For that enter the following formula in cell E3: = SUM(B3:D3) 2. Copy/Paste Cell E3 into the range E4:E10. The worksheet should look this:
/simplexct/images/Fig2-obfab.png "powerpoint show total on stacked bar chart")
Download Excel Model
- View sidebar
Smoak Signals | Tableau, Excel, SQL
Sharing knowledge: data, visualization & business.
- Dashboard Portfolio
- About Anthony B. Smoak
- YouTube (2.5 Million Views!!)
- Strategy / Organizations
- Videos / Visualizations
- Buy Me A Coffee
Add Total Values for Stacked Column and Stacked Bar Charts in Excel
- Excel , Information Visualization , Video
- Data Visualization , Excel , Microsoft
This is the only video you need to learn how to add add totals to stacked bar charts and stacked column charts in Excel. Make sure to watch the video because I have you covered both ways.
I’m more of a video explanation person, so make sure to watch the video so the steps are clearer. Then refer to the bulleted steps as reference once you have the initial basic understanding.
The Keys to Adding Totals to the Stacked Column Chart Above:
- Add a “Grand Total” column to your data
- Highlight your data not including the “Grand Total” column
- On the “Insert” menu select a “2-D Stacked Column” chart
- Select “Switch Row/Column” as necessary so your first data column is listed on the X axis
- Add “Data Labels” to the Grand Total series on the chart
- Right click on the Grand Total and “Change Series Chart Type”, the viz becomes a “Combo” chart
- Change the “Grand Total” series chart type to a “Line”, while leaving all others as a “Stacked Column”
- Format the line’s data label, changing the Label Position to “Above”
- Select the line, format the data series and change the “Line” option to “No Line” in order to leave only the totals and hide the line.
The Keys to Adding Totals to the Stacked Bar Chart Above:
- The “Spacing” column should have a value of 0 at this point
- On the “Insert” menu select a “2-D Stacked Bar Chart”
- Select “Switch Row/Column” as necessary so the “Spacing” values are not listed as an option on the Y axis
- Change the “Spacing” column values to a number (e.g., 1000) big enough to make a new category visible on the stacked bar chart
- In the “Select Data Label Range” pop up box, highlight the values from the “Grand Total” column
- Change the “Label Position” to “Inside Base”
- Change the “Fill” to “No Fill”
- Change the values in the “Spacing” column to 0
- Delete the “Grand Total” entry from the Legend
You can thank me by liking and subscribing to my YouTube Channel
All views and opinions are solely my own and do NOT necessarily reflect those of my employer.
Do Great Things with Your Data – Anthony B. Smoak
► shop.spreadshirt.com/AnthonySmoak
I NCREASE YOUR FOCUS TRY BRAIN.FM
When I am focusing hard during the day at work or developing visualizations on the weekend. I use Brain.fm to help me focus when it matters. It’s Science-driven and research-backed functional music designed from the ground up to help you you focus, relax, meditate and sleep . If you’d like to try a free trial check out https://brain.fm/anthonyb
Please use coupon code anthonyb for a 20% discount upon checkout. It helps support this blog and my YouTube channel as I receive a small commission for purchases made through links in this post.
Share this:
- Click to share on Twitter (Opens in new window)
- Click to share on Facebook (Opens in new window)
- Click to email a link to a friend (Opens in new window)
Thank you, worked like a charm!
Glad to hear it!
Thank you very much.
Thanks you very much
Leave a Reply Cancel reply
Fill in your details below or click an icon to log in:
You are commenting using your WordPress.com account. ( Log Out / Change )
You are commenting using your Twitter account. ( Log Out / Change )
You are commenting using your Facebook account. ( Log Out / Change )
Connecting to %s
Notify me of new comments via email.
Notify me of new posts via email.
- Already have a WordPress.com account? Log in now.
- Follow Following
- Copy shortlink
- Report this content
- View post in Reader
- Manage subscriptions
- Collapse this bar
be the expert in your office
An Excel Blog For The Real World
A blog focused primarily on Microsoft Excel, PowerPoint, & Word with articles aimed to take your data analysis and spreadsheet skills to the next level. Learn anything from creating dashboards to automating tasks with VBA code!
Add Total Value Labels to Stacked Bar Chart in Excel (Easy)
Stacked Bar Charts Missing Total Labels In Excel
Unfortunately, Microsoft does not have the ability to insert data labels at the top of vertical or horizontal stacked bar charts. It’s very interesting Microsoft decided not to incorporate this capability as it is a seemingly basic charting need while using stacked bar charts.
While resolving this missing feature has been asked of Microsoft (please upvote!) by the customer base, there does not appear to be any momentum yet to add it to Microsoft Excel by the developers.
Luckily, we can use some creativity to add total chart series and make it look like the overall totals chart labels.
In this article, I’m going to show you how to build total labels for your stacked bar charts similar to the image above. Let’s walk through the steps to do this in Excel.
Example Data
Before we go further, I just wanted to show you the raw chart data being used in this Stacked Bar Chart example. The data is breaking up the company’s revenue into three categories with an overall total at the very bottom.
I do provide a download link to the exact Excel spreadsheet file I created for this tutorial. Feel free to download the file and follow along if that helps you learn.
Add Data Series For Total Label
To get the appearance of total labels for our chart, we are actually going to create an invisible chart series. We want the values of this chart series to be very small in relation to your actual chart data so the vertical height of your bars do not get impacted (reduced in size).
Since my data is in whole numbers, I’m going to use a 0.1 value for each month.
After you have setup the data for your chart series, you want to link the data up to your chart.
Right-click on your chart and in the menu, select the Select Data menu item.
In the Select Data Source dialog box , click the Add button to create a new chart series.
Once you see the Edit Series range selector appear, select the data for your label series.
I would also recommend naming your chart series “ Total Label ” so you know the purpose of the additional chart series.
You’ll also want to ensure that the chart series is the very last series in the Legend Entries list box . This means it will be the last data point to be chart and will reside at the top of the bars.
Hit the OK button and you should see the new chart series appear at the top of the bars.
Link And Format The Total Data Labels
For the Total Labels series , we want to override the values of the data labels.
To do this, you will need to go into the Format Data Labels Pane by right-clicking one of the chart data labels associated with the Total Labels series and selecting Format Data Labels from the menu.
First, we will reposition the labels by changing the Label Position to Inside Base .
Next, uncheck all the Label Contains options except for Value From Cells .
Click the Select Range button and select the cell range that contains the total values for your stacked bar chart.
After you have confirmed your selection, you should see the label values change to the total bar values in the Excel chart.
Format Changes To Your Stacked Bar Chart
Remove the chart series fill color.
You want to make the Total Label chart series appear invisible as it really isn’t holding data that should be charted.
To do this, click on the chart series and ensure the fill color is set to No Fill . You may need to zoom in on your Excel spreadsheet to select it due to its small size.
Remove The Legend Item
If you have a legend displayed for your chart, you will want to ensure the legend item for your Total Label series does not appear. To remove it, simply select the individual legend item and hit your Delete key on your keyboard.
My Chart Formatting Recommendations
De-emphasize Labels - Your stacked bar data labels should be a gray or white color depending on the color of each bar in your chart. I also like to ensure the labels are not bold and italicize them. Utilizing a thinner/skinner version of the same font you are using is also something you could incorporate.
Emphasize Total Labels - Your total labels should be black or dark grey and also bolded. I like to ensure my total labels are at least 3pts larger than the other data labels.
Bar Width - Make sure your bars are not skinner than the width of your data labels.
Control Vertical Spacing Of The Total Labels
If you want to increase the separation between your total labels and the stacked bars, you can make a modification to your chart by adding a Label Spacer chart series before the Total Label series .
You’ll notice from the above graphic, that the Label Spacer chart series sits right below the Total Label series and is also invisible (no fill color).
You might be thinking, why not just increase the Total Label series value and skip the creation of an extra chart series? Well, I originally tried that and it end up compressing the chart quite a bit due to how the labels are positioned within each bar segment.
Creating a separate chart series, allows you to nudge the labels up further without compressing the chart bars within the Chart Area boundary.
Be sure to also remove the legend item associated with the Label Spacer chart series if you choose to add this feature to your chart.
Download Example Excel File
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
I Hope This Helped!
Hopefully, I was able to explain how you can add total chart labels to a stacked bar graph inside Microsoft Excel. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
About The Author
Hey there! I’m Chris and I run TheSpreadsheetGuru website in my spare time. By day, I’m actually a finance professional who relies on Microsoft Excel quite heavily in the corporate world. I love taking the things I learn in the “real world” and sharing them with everyone here on this site so that you too can become a spreadsheet guru at your company.
Through my years in the corporate world, I’ve been able to pick up on opportunities to make working with Excel better and have built a variety of Excel add-ins , from inserting tickmark symbols to automating copy/pasting from Excel to PowerPoint. If you’d like to keep up to date with the latest Excel news and directly get emailed the most meaningful Excel tips I’ve learned over the years, you can sign up for my free newsletters . I hope I was able to provide you some value today and hope to see you back here soon! - Chris
TheSpreadsheetGuru
Become an expert in Excel, PowerPoint, Word, and the rest of the Microsoft Office Suite. Check out all the free tutorials and VBA code snippets!
Latest Blog Posts
Affiliate Program | About | Privacy Policy | Example Files
Guru Solutions, LLC | © 2015-2022 | ALL RIGHTS RESERVED Excel, PowerPoint, Word, & the rest of the Office Suite are registered trademarks of the Microsoft Corporation This site is not affiliated with Microsoft Corporation.
- Kutools for Excel
- Kutools for Word
- Kutools for Outlook
- How to Install or Uninstall
- End User License Agreement
- Tips & Tricks for Excel
- Tips & Tricks for Word
- Tips & Tricks for Outlook
- Excel Functions
- Excel Formulas
- Excel Charts
- Outlook Tutorials
- Search Search more
- About Us Our Team
- User Testimonials Users Say
- Retrieve License Lost License?
- Report a Bug Bug Report
- Forum Post in Forum
- Contact Us Talk to us via email
- Forgot Username?
- Forgot Password?
- Outlook Tips
How to add total labels to stacked column chart in Excel?
For stacked bar charts, you can add data labels to the individual components of the stacked bar chart easily. But sometimes you need to have a floating total values displayed at the top of a stacked bar graph so that make the chart more understandable and readable. The basic chart function does not allow you to add a total data label for the sum of the individual components. However, you can work out this problem with following processes.
- Add total labels to stacked column chart in Excel ( 9 steps )
- Add total labels to stacked column chart with an amazing tool ( 2 steps )
- Create a stacked column chart with total labels in Excel ( 3 steps )
Add total labels to stacked column chart in Excel
Now the total labels are added and displayed above the staked columns. However, the Total data series label still shows at the bottom of the chart area.
Add total labels to stacked column chart with an amazing tool
If you have Kutools for Excel installed, you can quickly add all total labels to a stacked column chart with only one click easily in Excel.
Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30 -day, no credit card required! Free Trial Now!
Create a stacked column chart with total labels in Excel
If you have Kutools for Excel installed, you can quickly create a stacked column with total labels and percentage data labels at the same time with several clicks only.
2 . Select the data source, and click Kutools > Charts > Stacked Chart with Percentage to enable the feature.
Demo: Add total labels to stacked column chart in Excel
Related articles:
- How to add a horizontal average line to chart in Excel?
- How to add a chart title in Excel?
- How to add and remove error bars in Excel?
The Best Office Productivity Tools
Kutools for excel solves most of your problems, and increases your productivity by 80%.
- Reuse : Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range ...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns ... Prevent Duplicate Cells; Compare Ranges ...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets , Check Boxes and more...
- Extract Text , Add Text, Remove by Position, Remove Space ; Create and Print Paging Subtotals; Convert Between Cells Content and Comments ...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets ; Merge Tables based on key columns; Split Data into Multiple Sheets ; Batch Convert xls, xlsx and PDF ...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name ...
- More than 300 powerful features . Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint , Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
- Skip to primary navigation
- Skip to main content
- Skip to primary sidebar
Peltier Tech
Peltier Technical Services - Excel Charts and Programming
Add Totals to Stacked Bar Chart
Tuesday, October 15, 2019 by Jon Peltier 18 Comments
Stacked Bar Chart with Labeled Totals
How do you add totals to a stacked horizontal bar chart? Especially if you’ve already used the existing labels for the individual bars?
There are never enough features built into an Excel chart, I know. But with a little magic, that is, smoke and mirrors, you can make an Excel chart do anything you want. That’s the premise of this entire blog, after all.
So how do you add stacked bar totals? It’s pretty easy for stacked columns, but stacked bars are pretty pretty complicated. But a student in a class last week showed me a less complicated way, and I’ll present it here.
Here’s the setup with the data and the initial stacked column and stacked bar charts.
In Label Totals on Stacked Column Charts I showed how to add data labels with totals to a stacked vertical column chart. That technique was pretty easy, but using a horizontal bar chart makes it a bit more complicated.
In Add Totals to Stacked Column Chart I discussed the problem further, and provided an Excel add-in that will apply totals labels to stacked column, bar, or area charts. Below are the stacked column and stacked bar charts with the labels produced by the add-in.
Using the add-in is great; I mean, it’s free and it works. But the labels are not dynamic: they compute a static total, and if any values are updated, the totals are incorrect. If anything changes, you need to rerun the add-in.
Stacked Column Chart with Labeled Totals
First, a quick review of the applicable procedure for a stacked column chart. Here is the data for the chart, and the initial appearance of the chart. The data in the chart is highlighted in the range, and you’ll notice the column of Totals, computed from the charted data range.
Adding Totals to this chart is pretty easy. First you expand the data range to include the Totals column (below left). The easiest way is to select the chart and drag the corners of the highlighted region to include the Totals.
Then convert the added series to a line chart series type (below right). Right click on the series (or on any series) and select Change Series Data Type, then find the series and in the chart type dropdown select the type you need. I used a line without markers.
Next, add data labels to the line chart series, above the points (below left). The default labels are Y values, so you don’t need to change anything.
Finally, a little clean up. Hide the Totals line (format it to use ‘No Line’) and remove the Totals legend entry (click once on the legend, then a second time to select the legend entry, and click Delete).
Stacked Bar Chart with XY Series Data Labels (The Hard Way)
I showed the above technique in my Advanced Excel Charting Master Class last week. One student asked how I would do that in a horizontal stacked bar chart. So I said, it’s complicated, but you can use an XY series instead of the Line chart series in the vertical column chart.
Here is the data setup and the initial horizontal stacked bar chart. The plotted data is highlighted, while the computed sums are in column F, and the necessary Y values for the added XY series are in column G. The formula in cell G3 is:
and this formula is filled down the column as far as necessary. This produces a set of values which are distributed along an axis that is scaled from zero to one.
Copy F2:G8, select the chart, and use Paste Special from the Paste dropdown on the Home tab of the ribbon, and add the data as a new series, by column, with series name in the first row and category labels in the first column (don’t replace existing categories). This gives you the small yellow bars added to the end of the stacks (below left).
Convert the added series to an XY chart type. Right click on the added series (or any series) and choose Change Series Chart Type, find the series, and choose an XY Scatter type; I used XY Scatter, with markers but no lines (below right).
So the markers are there, but not in the right place. To fix this, format the right hand vertical axis, and check the box for Values in Opposite Order (below left).
Add data labels to the XY series (below right).
Format the data labels to show X values, not the default Y values (below left).
Finally, hide the stuff you don’t need. Format the XY series to have no markers. Hide the secondary (right-hand)vertical axis by formatting the label position to ‘No Labels’ and formatting the line to have ‘No Line’. Hide the unwanted legend entry by clicking once to select the legend, clicking again to select the unwanted legend entry, and clicking Delete.
The finished chart is shown below right.
Stacked Bar Chart with Bar Series Data Labels (The Easy Way)
I showed the previous technique in the Master Class, and one of the other students told me she had figured out a better way. “And it doesn’t use so much MATH!” she added. My wife is an eighth grade math teacher, but I won’t take the comment personally.
Show me, I said. And she did. Here’s the setup, with the data and the original chart. The Totals data is exactly just a column of zeros, with the calculated totals in the Values column.
The first step is to expand the chart data range to include the column of zeros (below left). Zero-value bars don’t show up, so the chart below right shows how it would look if the values were a column of 2s.
Next, add data labels to the added series (below left) they are all zero, and they are centered on the bars, which means centered on the ends of the previous stack.
Format the labels to use the Inside Base position, which moves them a bit to the right (below right).
Format the data labels to use the Value From Cells option, and select the column of calculated sums (column G) for the labels (below left).
Finally, hide the unwanted legend entry (below right). Click once to select the whole legend, then again to select the unwanted entry, then press Delete.
Any time I learn something from the students in my class, it’s a successful class. I’m always happy to admit I don’t know everything, and I’m equally happy to share what I’ve learned.
Stacked Bar Chart with Clustered Bar Series (Also Easy)
A smart reader named Sandeep pointed out another variation that does not require too much math. His approach uses a clustered bar series for the labels, because clustered bars have an option to place the labels outside the ends of the bars. Since clustered and stacked bars can’t be on the same axis, we need to move some series to the secondary axis. I will move the stacked bar series to the secondary axis, so the clustered bars will remain hidden behind the stacks of bars.
The approach varies a bit if you show the vertical axis categories in reverse order, which I discuss in Why Are My Excel Bar Chart Categories Backwards? and Excel Plotted My Bar Chart Upside-Down .
Thanks, Sandeep, for sharing your suggestion.
Vertical Categories NOT in Reverse Order (default)
The data looks the same as above, with only a single added column calculating the totals.
Start with the stacked bar chart (below left), with categories not in reverse order (that is, upside-down), and add the Totals series (below right).
Right click on any of the series of bars and choose Change Series Chart Type from the pop-up menu. In the list of series below the chart preview, check Secondary Axis for all but the Total series.
The chart is shown below left. The Totals series is still present, but it is hiding behind the stacked bars. If I make the Total bars wider by decreasing the gap width, you can see the edges of the Totals bars peeking around the stacked bars.
But leave the gap width alone, so the Totals remain hidden. In the same dialog as above, change the chart type of the Totals series from stacked bar to clustered bar. The appearance is still the same (below left). Format the data labels of the Totals series so their position is Outside End (below right).
Finally clean up the legend by removing the unneeded legend entry: select the legend, then select the Totals legend entry, and press Delete.
Vertical Categories in Reverse Order
Start with the stacked bar chart (below left), with categories in reverse order, and add the Totals series (below right).
As above, right click on any of the bars, choose Change Series Chart Type from the pop-up menu, and check Secondary Axis for all but the Total series. Some of the longer Totals bars extend beyond the shorter stacks (bottom left). Change the Totals chart type to clustered bar (below right).
Format the Totals data label position to outside end (below left).
The Totals bars are plotted in reverse order (the longest bar is at the bottom as in our starting chart) but the stacks are not (the longest stack is at the top). Add the secondary vertical axis and delete the secondary horizontal axis (below right).
Format the secondary vertical axis so that its categories are in reverse order; the Totals are now completely hidden by the stacked bars (below left).
Now we just need to clean up the chart. You can delete the secondary vertical axis, or you can keep it in the chart but hide it by formatting the tick label position to no labels and the line format to no line. Also, remove Totals from the legend: click on the legend, then click on the legend entry, and press Delete.
Coming to you today from…
Rather than sitting in a hipster coffee shop while I wrote this article, I went across town to the Greater Good Imperial Brewing Company . While writing, I had a flight (well, two flights) and some hot pretzels with mustard. I was not the only patron there working on my laptop.
Share this:
- Click to share on Twitter (Opens in new window)
- Click to share on Facebook (Opens in new window)
- Click to share on LinkedIn (Opens in new window)
- Click to share on Tumblr (Opens in new window)
- Click to share on Pinterest (Opens in new window)
- Click to share on Reddit (Opens in new window)
- Click to print (Opens in new window)
- Click to email a link to a friend (Opens in new window)
Posted: Tuesday, October 15th, 2019 under Data Labels . Tags: Stacked Bar Charts , totals . Comments: 18
Reader Interactions
David N says
Tuesday, October 15, 2019 at 3:15 pm
Looks like the Inside Base option with an all zero helper series works for stacked columns as well, avoiding the need for any math to calculate a line.
Andy Pope says
Wednesday, October 16, 2019 at 2:58 am
Another alternative on stacked bar chart is to use a cluster bar on secondary axis. The new total series bar can have data labels displayed outside end. You do have to make sure the secondary vertical axis is formatted similar to primary and remove fill from the new total series.
Jon Peltier says
Wednesday, October 16, 2019 at 8:33 am
David –
Inside Base works fine for positioning labels in a stacked column chart. However, you still need to do the math to calculate the sume, for the labels. And you need to use the “Values from Cells” option for the data labels, which is not as reliable as the Y Values option.
Wednesday, October 16, 2019 at 8:39 am
Andy –
That works as well, with a couple caveats. Keeping the axes aligned can be a nuisance. Also, with the invisible bar on the secondary axis, it blocks attempts to click on one of the primary stacked bars. This is easily handled by putting the total bar on the primary axis and the stacked bars on the secondary.
Wednesday, October 16, 2019 at 8:48 am
Jon, you’re correct. I should have stipulated that I was referring to the more generic case of positioning a label that could then contain any text, be it the total that would still require math, or some other tidbit of information that could be totally unrelated to the Y value.
I’ve personally found that the Values from Cells option is most reliable when “everything” abot the chart is sourced from columns wihtin a structured Table. Then each new data point not only appears automatically in the plotted series, but its custom label is automatically applied as well. (As opposed to other/older methods of applying cutom labels which require that something be done or redone each time a new data point is added to the series.)
Wednesday, October 16, 2019 at 10:24 am
For the general case of data label positioning, yes, the zero-value column/bar is a nice thing to have in our toolkit. If the label shows the totals, then the line chart approach is better for a stacked column.
In the ideal world, all chart data is neatly arranged in orderly Tables, and most everything is pretty reliable. In actuality, data comes in many forms, and sometimes it takes a lot of effort to get it into a Table.
Robert Lalonde says
Friday, November 1, 2019 at 2:07 pm
Cool technique. I think with your experience with presenting data, you would have sorted the beer samples from light to dark. :) Cheers.
Thursday, November 7, 2019 at 2:54 pm
Bob –
I’d like to say they were ordered by ABV or IBU, but really, they’re in the order I picked them off the menu.
John Wright says
Wednesday, November 27, 2019 at 7:56 am
IBU – so I’ve learned two things from this post!
Rich Williams says
Friday, September 11, 2020 at 3:08 pm
Thanks Jon! I can be dense when it comes to following instructions, but it was a clear post.
Nicola Kastner says
Monday, January 11, 2021 at 5:15 pm
I thought I was missing something simple. Apparently not but this worked. Thanks for sharing
Sandeep Lele says
Friday, October 7, 2022 at 3:07 pm
I found another way.
No need to have the ‘zero’ value column in the table. Just need the data columns and the ‘totals’ column. Create the chart as a combo chart. Series gap 100%. Make the data bars as ‘stacked’ bars with data labels in the ‘center’. Make the totals bar as ‘line’ chart. The line will show wrt X axis. don’s worry. Simply add data labels to ‘right’. now convert this series to ‘clustered’ bar. It will hide behind the stacked bars. and the totals will automatically show outside the respective bars.
Friday, October 7, 2022 at 11:54 pm
What zero value column? Didn’t you just describe the actual technique I’ve used?
Saturday, October 8, 2022 at 5:57 am
I had looked only at your solutions for stacked bar chart, not the stacked column. And also at your statement in the original post viz. “… One student asked how I would do that in a horizontal stacked bar chart. So I said, it’s complicated … ”
Now I see that the solution I gave is similar to the one you showed for stacked column. I wonder why did you not use the same method for stacked bar chart and rather said ‘ it is complicated ‘.
Saturday, October 8, 2022 at 2:13 pm
It’s complicated since you cannot use a line chart series to add labels for a horizontal bar chart: they will never line up. So it is necessary to use an XY Scatter series with extra data to align the markers with the bars, or one of the other tricks with more bars.
Sunday, October 9, 2022 at 1:01 am
Not really. If you follow the steps I mentioned, the horizontal bar chart also shows totals. Without any additional columns or XY scatter in the data table. I wrote it only after I could do that myself in a chart.
Tuesday, October 11, 2022 at 11:00 am
I must apologize. Your approach works fine. I was reading on a train to the airport, so I must have been distracted, and I missed your last step. Now that I’m back in my office, I tried your protocol step-by-step and got the desired result. It can be more complicated than your approach shows for various reasons, such as when the categories are plotted in reverse order, but it can still be used effectively.
Tuesday, October 11, 2022 at 11:09 am
No worries. In fact, it’s good that we have an agreement !
Leave a Reply
Your email address will not be published. Required fields are marked *
Notify me of follow-up comments by email.
Notify me of new posts by email.
This site uses Akismet to reduce spam. Learn how your comment data is processed .
IMAGES
VIDEO
COMMENTS
The method used to add the totals to the top of each column is to add an extra data series with the totals as the values. Change the graph type
Unfortunately, there's no automatic way to do this. However, you can ease the pain slightly by opening the Excel data sheet that accompanies the
Exit the data editor, or click away from your table in Excel, and right click on your chart again. Select Change Chart Type and select Combo
PPT Design Tip - Stacked Bar Chart Totals Based on Real Data: Michael from eSlide walks you through tips and tricks on how to add totals to
In this PowerPoint tutorial we show you how to create total values in stacked column charts.
8. In the chart, right-click the "Total" series and then, on the shortcut menu, select Add Data Labels.
Highlight your data including the “Spacing” column but not including the “Grand Total” column · Right click to “Format Data Labels” and change
To do this, you will need to go into the Format Data Labels Pane by right-clicking one of the chart data labels associated with the Total Labels
1. Create the stacked column chart. · 2. Select the stacked column chart, and click Kutools > Charts > Chart Tools > Add Sum Labels to Chart. · Then all total
Adding Totals to this chart is pretty easy. First you expand the data range to include the Totals column (below left). The easiest way is to