Using Stacked Charts and Showing Totals

A stacked chart in Excel allows users to take advantage of the best of both worlds: a column chart that shows period-over-period totals and a pie chart that can show what made up those totals. It can be a very useful chart, but knowing how to structure it is half the battle.

In my example, I pulled Alphabet’s earnings for the most recent four quarters. I wanted to show a) the period-over-period sales as well as where those sales went, and how much flowed through to the company’s operating income.

Below is the table that I used:

The key things is you want to make sure that all your categories add up to the amount that you’re trying to reconcile (in my case, it’s revenue).

Once the data is ready, select the data and insert a Stacked Column

stacked column chart excel
In my example, the categories showed on the horizontal axis, which is not what I wanted, so in order to fix that, right click on the chart and press Select Data
stacked column chart excel
From there, you want to hit the button to switch row/column:
chart switch row and column excel
This will give you a stacked chart. The problem, however, is that my total (revenue) is mixed into this, and that’s not going to give me the desired result. After all, I want to see where the revenue goes, not include the revenue in my categories.
To fix this, you’ll want to right click on one of your column charts and select Change Series Chart Type

excel change series chart type

That should take you to the Combo section. If it doesn’t, make sure to select it. All your series should show a stacked chart. You’ll want to change the revenue series to a Line chart. By doing so, it will not contribute to the stacked chart and now it’ll simply be made up of the other categories. 
combo chart excel
Next, add data labels for the line chart so now you’ll see the totals. To do this, right click on the line chart and click Add Data Labels

The problem is the labels show to the right, and it probably makes more sense for these labels to show above the stacked chart. Right click on any of the labels and select Format Data Labels

excel chart format data labels

Make sure that for the label position, Above is selected
excel chart format data labels
Now my chart is starting to come together:
excel stacked line chart
Except I still have that line going over the top of the stacked charts. To get rid of the line, right click on the line chart and select Format Data Series

excel chart format data series

Select No line from the Line section
excel chart format line

Now, select the Marker and make sure the fill option is set to No Fill
excel chart format marker
Now, I’ve gotten rid of the line completely:
excel stacked chart
At this point it just comes down to designing the chart how you want it. Some of the changes I made included:
  • Getting rid of ‘Revenue’ from the legend
  • Changing the color theme
  • Shrinking the gaps between the stacked charts
excel stacked chart

Add a Comment

You must be logged in to post a comment