This post walks you through all the steps required to create a stacked bar chart that displays each bar’s totals, as shown below.
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:
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.
This creates a stacked bar chart as shown:
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.
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.
iv. Now, add a suitable chart title and format it with options available in the Home Ribbon.
v. Click on the chart and use the “+” icon to add data labels, format the labels if needed
vi, Similarly, add the axis titles too.
vii. Click on the legend, and use the format pane legend options to reposition them to top.
viii. Click on the chart, go to Chart Area from the format pane, and format the chart border as shown.
With this, the simple stacked bar chart looks something like this:
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.
Now, add a Total column to the table with the formula as shown:
=[@East]+[@West]+[@North]+[@South]
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
ii. Remove Total series as shown here:
Ensure “Spacing” series stays at the bottom of all the series
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)
iv. Click on the Total labels, position it at inside base and format them as needed.
With this, the stacked bar chart with totals is ready to be included in your next presentation!
