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.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
![Add Vertical Lines To Excel Charts Like A Pro! [Guide]](https://www.thespreadsheetguru.com/wp-content/uploads/2022/05/AddVerticalLineToExcelBarorLineChart.png)
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.