Add a total to stacked column charts in Excel

Stacked column charts show the parts of a total across a series of categories, often time periods such as weeks, months or years.

But including a total at the top of each column, which can often be useful, is not a standard feature so here’s the work around you will need in order to do this.

Let’s take a set of data as an example, in this case a category breakdown of internet spend over a period of 7 years:

Stacked Bar Chart Data

Stacked Bar Chart Data

You might want to look at how the breakdown has changed over the years and therefore would produce a stacked column chart:

 

Stacked Bar Chart with data label

Stacked Bar Chart with data label

Unfortunately, you can’t add total values at the top of each column simply by right-clicking on the bars and selecting “Add Data Labels” because this only adds them for one segment at a time.

So instead you need to build a stacked column chart that includes the total column from your raw data table as well as the breakdown:

Stacked Column Chart

Next select the data series ‘Total’ and make it appear on a secondary axis by right clicking, selecting Format Data Series and then picking Secondary Axis

This puts the ‘Total’ data series in front of the other data, hiding the stacked columns.

Right click again on this series and select Add Data Labels, then move them to the top of the bars if they aren’t already there:

Chart with totals

Chart with totals

Finally, all you need to do is format the ‘Total’ data series to have no fill and no border, delete the legend entry for ‘Total’ and turn the right hand axis white:

Stacked column chart with totals in Excel

Stacked column chart with totals in Excel

Now you have a stacked column chart with totals displayed clearly at the top.

 

TIP FOR EXCEL: to show totals on stacked column charts include the total as part of the stack and then format as an invisible secondary data series!

 

 

4 comments to Add a total to stacked column charts in Excel

Leave a Reply

  

  

  


6 × nine =

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>