Indzara

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

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

100% Stacked Bar with totals  final chart
100% Stacked Bar 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, not only that, we’ll look at how to add the department wise total value above each bar.

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

100% Stacked Bar 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 the chart

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

ii.Select all data and insert a 100% stacked bar chart.

100% Stacked Bar with totals insert stacked bar

This creates a 100% stacked bar chart as shown:

100% Stacked Bar with totals

Step 02: Format the chart

Before adding the total values on top, let us first format this 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.

100% Stacked Bar with totals choose series
100% Stacked Bar with totals fill & line

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

If need be, you can also adjust the gap width from the series options: the lesser the gap width, the broader (thicker) your bars will be.

100% Stacked Bar with totals gap width

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

100% Stacked Bar with totals Horizontal axis major gridlines
100% Stacked Bar with totals gridlines color

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

100% Stacked Bar with totals chart title

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

100% Stacked Bar with totals add data labels

vi, Similarly, add the axis titles too.

100% Stacked Bar with totals add axis titles

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

100% Stacked Bar with totals Legend position

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

100% Stacked Bar with totals  format chart border

With this, the 100% stacked bar chart looks something like this:

100% Stacked Bar with totals (a)

Step 03:

Now to add the total values to the 100% 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.

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

Step 04: Add the Total Value to 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

100% Stacked Bar with totals select data

ii.  Remove Total series as shown here:

100% Stacked Bar with totals remove series

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

100% Stacked Bar with totals spacing

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

With this, the chart should look like this:

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)

100% Stacked Bar with totals value from cells
100% Stacked Bar with totals  data label range

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

100% Stacked Bar with totals label options

With this step the total label gets added to the 100% stacked column chart:

Step 05: Format X-Axis

The above chart looks fine but to include more space for the total labels, we need to format the horizontal axis.

To do this:

i. Right-click on the horizontal-axis, go to Format Axis:

100% Stacked Bar with totals format axis

ii. In the Axis Options, reset the minimum and maximum bounds to 0.0 and 1.1 respectively.

100% Stacked Bar with totals min and max bounds

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

Leave a Reply

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