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.
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.
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.
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.
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.
By now, our stacked column chart should be looking more like we want, but we still need to put in those percentages!
Get the latest Excel tips and tricks by joining the newsletter!
Andrew 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!