How to add totals to Stacked Column Chart

Here is a simple trick to add total labels to your stacked column charts..

 

Assume this data and a pre made stacked column chart

How to add totals to Stacked Column Chart1

How to add totals to Stacked Column Chart 2

We now want to add total label (for showing Laptops + Music Player Sales) to this chart

 

Step 1 Add totals to your data

How to add totals to Stacked Column Chart 3

Add a new row that calculates the sum of the products

 

Step 2 Add totals to the Chart

How to add totals to Stacked Column Chart 4

To add the totals to the chart

  1. Copy (Ctrl + C) the Total Sales (Values only)
  2. Then select the chart and Paste the values (Ctrl + V)
  3. The chart will incorporate a Total bar on top of Laptops and Music Sales, but that is not what we want!

So let’s fix it!

 

Step 3 Move the total bars to secondary axis

How to add totals to Stacked Column Chart 5

Move the Total Bar (only) to the Secondary Axis and do a No Fill to the Total Bar

 

Step 4 Now Add Data Labels

How to add totals to Stacked Column Chart 6

Just Add the Data Labels. One of the things which can be tedious is to place the labels on top of the bars. There are two ways to solve this

  1. Drag each label manually to the top of each bar (as shown above)
  2. Or change the chart type of secondary axis (total bar) to clustered column chart and place the data labels on the top (I would go with this one)
  3. Also don’t forge to hide the secondary axis, since it has no meaning!

 

More on Charts

  1. How to pick the right color for your charts
  2. How to make a Waterfall Chart 
  3. Quick Tip : How to draw a Chart in 1 Keystroke
  4. How to make a check button Chart

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI