How to Show Percentages in Stacked Bar and Column Charts

Fixing the Total Data Labels

You might have noticed that the chart is currently showing our data twice… We have all the contributing regions stacked up, and then we have the total for each year on top of the regions. Our bars are twice as tall as we want them to be. The reason the Total bar is there is so we can take advantage of the Data Label Excel puts on it. We are going to keep the label and hide the bar. There is a tutorial on how to add totals to stacked charts here. We’ll briefly step through it again now.

To start, double-click on one of the data labels for the Total data series.

Select Total Label

In the Format Data Label dialog box that appears, change the Label Position to Inside Base and click OK. This will rest the total label on top of the rest of the stack of bars.

Format Data Label

Now select the bars for the Total data series by clicking on one of them.

In the Format menu tab of Chart Tools under the Shape Styles section, click on Shape Fill and then choose No Fill. This will hide the bars for the Total data series but leave the data labels.

Shape Fill No Fill

We’ve hidden the Total bars, but the chart is still scaled to show them, so let’s manually fix the Y-Axis range. Double-click on the Y-Axis to bring up the Format Axis dialog box. In the Axis Options section under Maximum choose Fixed and type in a number that will let the tallest bar and label show but hide the rest of the blank space. In this example data set, 1,800,000 is a good number. Click OK.

Format Axis

Now the chart is properly scaled, but the Total series is still marked on the chart Legend. Click on the Total label in the legend to select it, and press Delete to remove it from the Legend.

Total Legend Delete

By now, our stacked column chart should be looking more like we want, but we still need to put in those percentages!

Reformatted Chart

Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

18 thoughts on “How to Show Percentages in Stacked Bar and Column Charts

  1. fun little tip I came up with after using your great instructions:

    You can add in the item and Percentage simply by using a Text() function.

    Refer to both the Item cell and % cell
    =Text(,”0%”)&”, “&

    you then point to this string, and your chart will show both percent and item.

    1. Can you explain your tip a little more? I am trying to apply it to my work but I am having difficulty. It sounds like it is just what I need. Thanks!

  2. Does anyone know how to get a graphic symbol in the same cell with a percentage ? I really like it, but I can’t figure out how to do this.

    Thanks for your help.


    1. This is done with Conditional Formatting – usually located on your “Home” tab on your menu ribbon:

      1 – Select the range of cells you want to have this style of formatting.
      2 – Click “New Rule” from the Conditional Formatting dropdown menu.
      3 – Select “Format All Cells Based On Their Value” in the upper part of the window.
      4 – Select “Format Style – Icon Sets” in the lower part of the window.
      5 – Select the appropriate “Icon Style” from the dropdown menu.
      6 – Adjust the parameters so that the appropriate Value or Percentage will display the appropriate Icon.
      7 – OK!

  3. I’ve noticed an issue with the stacked percentage bar chart not adding up to 100% within Excel. Even in your illustration, year 2012 adds up only to 99%. Does anyone have a fix for this or does it require intensive quality control each time one of these is generated?

  4. As i have couple of data where in as per the stock is the chart is ready but unable to show the percentage with the stock please help

  5. Your solution for adding percentages manually is EXACTLY WHAT I WAS LOOKING FOR! It’s the best of all the cumbersome workarounds! Excel should just fix this dated and silly issue.

  6. You are double counting your data. You don’t need to include “Totals” in your data selection. The graph looks like your company sold 3M+ units, when in reality it’s half that.

  7. Thank you so much!!! I’ve been looking for this for days and once I found it I immediately practiced your tips. Turned out very great. Thank you again!!

  8. Hi, Hertriani.

    Sorry I am new to all of this and I am having a little trouble following.

    Which instructions did you use to create that graph that gives you a percentage stacked bar as well as a number.

    In the hotel world I would like to have a percentage occupancy showing while having the average rate paid labeled above it. In the end it should look like what you created.



    1. hi Bryan, apologize for the delayed response cos I was not aware of your question until I got a notification email earlier today. I followed the instructions in this article, and made minor modification on the label setting. But basically it’s everything that is mentioned in this article. Feel free to contact me at [email protected] if you want to discuss this.

  9. Can someone help me on this… I got to the last step but when I click on the data label there is already a formula in the formula bar since the chart is obviously referencing cells. If I delete what is in there and then select the desired percentage cell, nothing happens. If I leave the formula in there and try to add the percentage cell before it, the chart turns into a mess.

    Any help would be great

  10. Very good. Thanks a lot.

    I did a little vba code wich helps as well.
    You just need to prepare the graph regularly (do not concerne about percentage)… when you get done, apply the code.

    Sub GrafValor_LabelPercent()

    Dim volMGDia(1000) As Double, c As Integer, x As Integer, vPerc As Double, n As Integer, vLabel As Double

    For n = 0 To 1000
    volMGDia(n) = Empty
    Next n

    ActiveChart.SetElement (msoElementDataLabelNone)
    ActiveChart.SetElement (msoElementDataLabelCenter)

    For c = 1 To ActiveChart.SeriesCollection.Count
    If LCase(ActiveChart.SeriesCollection(c).Name) “total” Then
    For x = 1 To ActiveChart.SeriesCollection(c).Points.Count
    volMGDia(x) = volMGDia(x) + Val(Format(ActiveChart.SeriesCollection(c).Points(x).DataLabel.Text, “0”))
    Next x
    End If
    Next c

    For c = 1 To ActiveChart.SeriesCollection.Count
    If LCase(ActiveChart.SeriesCollection(c).Name) “total” Then
    For x = 1 To ActiveChart.SeriesCollection(c).Points.Count
    vLabel = Val(Format(ActiveChart.SeriesCollection(c).Points(x).DataLabel.Text, “0”))
    If volMGDia(x) > 0 Then
    vPerc = vLabel / volMGDia(x)
    vPerc = 0
    End If
    ActiveChart.SeriesCollection(c).Points(x).DataLabel.Font.Size = 12
    If vPerc > 0 Then
    ActiveChart.SeriesCollection(c).Points(x).DataLabel.Text = Format(vPerc, “0%”)
    End If
    Next x
    ActiveChart.SeriesCollection(c).DataLabels.Position = xlLabelPositionAbove
    ActiveChart.SeriesCollection(c).DataLabels.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 112, 192)
    ActiveChart.SeriesCollection(c).DataLabels.Format.TextFrame2.TextRange.Font.Bold = msoTrue
    End If
    Next c

Leave a Reply

Your email address will not be published. Required fields are marked *