Indzara

How to Create a Stacked Bar Chart with Totals in Excel?

This post walks you through all the steps required to create a stacked bar chart that displays each bar’s totals, as shown below.

Stacked bar chart with totals final chart
Stacked bar chart with totals final chart

Using a regular stacked bar chart, the width of the bar is used to analyze which department has the highest and the lowest employee count based on a third dimension, here by the region.

Let’s look at the steps required to create this chart in Excel. Consider a sample data as shown:

Stacked bar chart with totals sample data

Ready to save time on your next presentation? Explore our Data Visualization Toolkit featuring multiple pre-designed charts, ready to use in Excel. Buy now, create charts instantly, and save time! Click here to visit the product page.

Step 01: Create Chart

i. Select all data, press CTRL+T to convert it to table.

Ii. Select all data and insert a stacked bar chart.

Stacked bar chart with totals insert stacker bar

This creates a stacked bar chart as shown:

Stacked bar chart with totals

Step 02: Format Chart

Before adding the total values on top, let us first format this stacked bar chart.

i. Click on the chart, and press CTRL+1 to open the format pane.

ii. From the drop-down, choose one of the series and format the fill color as shown.

Stacked bar chart with totals choose series
Stacked bar chart with totals fill

Repeat the same steps for all series (as need be)

iii. From the drop-down, choose the “Horizontal (Value) Axis Major Gridlines” and format it to make this less apparent.

Stacked bar chart with totals horizontal axis major gridlines
Stacked bar chart with totals gridline color

iv. Now, add a suitable chart title and format it with options available in the Home Ribbon.

Stacked bar chart with totals chart title

v. Click on the chart and use the “+” icon to add data labels, format the labels if needed

Stacked bar chart with totals add data labels

vi, Similarly, add the axis titles too.

Stacked bar chart with totals  add axis titles

vii. Click on the legend, and use the format pane legend options to reposition them to top.

Stacked bar chart with totals legend position

viii. Click on the chart, go to Chart Area from the format pane, and format the chart border as shown.

Stacked bar chart with totals chart border

With this, the simple stacked bar chart looks something like this:

Stacked bar chart with totals (a)

Step 03: Add Total and Spacing columns

Now to add the total values to the stacked bar chart, let us add a “Spacing” column to the table with zeroes as the values, this is just to create an additional stack to add the data labels.

Stacked bar chart with totals spacing column

Now, add a Total column to the table with the formula as shown:

Stacked bar chart with totals add total column

Step 04:Add Total to the chart

This adds both the series to the chart, we need only the “Spacing” to be present.

i. Right-click on the chart, go to Select Data

Stacked bar chart with totals select data

ii.  Remove Total series as shown here:

Stacked bar chart with totals remove total series

Ensure “Spacing” series stays at the bottom of all the series

Stacked bar chart with totals spacing series

Click on the “Spacing” legend from chart and delete the same.

With this, the chart should look like this:

Stacked bar chart with totals (b)

iii. Now, click on the “0” labels, and from the format pane go to Label Options.

From here, check the “Value from Cells” option and choose the Total series values as shown. (Uncheck other options)

Stacked bar chart with totals value from cells
Stacked bar chart with totals data label range

iv. Click on the Total labels, position it at inside base and format them as needed.

Stacked bar chart with totals labels

With this, the stacked bar chart with totals is ready to be included in your next presentation!

Stacked bar chart with totals final chart
Stacked bar chart with totals final chart

Leave a Reply

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