Stacked bar charts look complex but are super easy to create in Excel. Let’s walk through what a stacked bar chart is, how to insert it, and an example to put it into practice.
What is a Stacked Bar Chart?
A bar chart is a graph with rectangular bars that compares data points across categories. The bars are typically vertical, but can also be set up in a horizontal layout.
A stacked bar chart takes the bar chart one step further. Within each category, a stacked bar chart displays sub-categories. It shows this by stacking mini bars on top of each other, adding up to the category total.
Let’s think about this in a business context. Let’s say that you want to show product categories sold during a month. This would be perfect for a bar chart. The horizontal axis will be bars for each category and the vertical axis will be units sold. The stacked bar chart can take this one step further and show sales by region across each category. The regions will be a breakdown of each category.
How to Insert a Chart
Charts can be found on the Insert tab of the ribbon. Once you are on the Insert tab, select the bar charts dropdown (icon of a bar chart). There are stacked versions of both the 2-D and 3-D column and bar charts.
You will need to apply the chart to an existing data set. We will walk through this in more detail in the example. I’d recommend sticking to 2D charts versus 3D until you feel comfortable.
Example – Sales Volume by Product and Region:
Step 1: Layout data set
First, you will need the data set to include in the chart. For our example, we will look at sales volume by product by region for a full year period.
Step 2: Select Range and Insert Chart
To insert a chart, you first need to highlight the data range. Make sure to pull in the headers as Excel will automatically include them as data labels.
Once you have highlighted the range, insert a stacked bar chart following the instructions above.
Step 3: Format the Chart
Excel will create the chart with default formatting, doing its best to scale, label, and make the chart readable. This was what Excel’s first crack at it looked like:
That said, you will often want to tweak the formatting for personal preference or company brand standards.
To adjust the formatting, you can click on any part of the chart that you want to edit. You can click on the gridlines to remove them, the axis to adjust the scale, the bars to change the color scheme, really anything you want. Also note that when you select a chart, two additional tabs are added to the ribbon. The tabs are “Chart Design” and “Format” and will help you with changes.
We will make the following changes to the chart:
- Click on the title and hit delete
- Click on the product types and bold them
- Go to chart design, change colors, and select the blue gray pattern
Here is the end result of our changes that looks a lot more executive presentation ready:
Have any questions on stacked bar charts in Excel? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.