Create Excel Cluster Stack Charts
A cluster stack chart has clusters of columns or bars, with a stack in each column or bar. See how to set up your Excel data to create a cluster stack column chart or a cluster stack bar chart. Or use a pivot table and pivot chart, for a quick and easy solution.
What Is a Clustered Stacked Chart?
A Clustered Stacked chart is a combination of a Stacked Column or Bar chart, and a Clustered Column or Bar chart. Excel does not have a built-in Clustered Stacked Column or Bar chart type, but this tutorial different ways to create one.
Follow the steps below, to create a cluster stack chart, similar to this one. This chart has:
There are written steps, and there is also a step-by-step video below.
Video: Cluster Stack Column Chart
This short video shows how to set up your Excel data, by adding blank rows to space the region and year data, and putting the annual data on different rows.
Then, build the cluster stack chart, and make a couple of quick formatting changes, to end up with an attractive, and easy-to-understand clustered stacked chart.
The video transcript is below the video.
If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript
Video Transcript: Clustered Stacked Column Chart
Here is the full transcript for the Clustered Stacked Column Chart video.
In this workbook, I have: sales data for two years, for four different regions, broken down by season.
I'd like to create a chart like this one, that shows each of the regions, with a stack for each year and the seasons broken down within each stack. This is Debra Dalgleish from Contextures.com.
Cluster Stack Chart
This chart that I want to create is like a combination of a cluster column chart, and a stack column chart.
There's nothing built into Excel that will do that so I'm going to copy my data to a blank sheet, then change the way it's arranged.
To start I'll copy the columns with data (I want to leave the original data unchanged)
Copy that, go to a blank sheet and paste it.
To get the data ready for the chart, I'm going to add some blank rows. I want a blank row before the first region and then a blank row after each region.
A quick way to rearrange my data is to:
Now there's my blank at the top, and each region has its data in one row and then two blank rows after that.
All I have to do now is select the second year of data and drag it down one row.
So, we've got blanks, two rows of data, another blank, and this is how we need it to create our cluster stack column chart.
Make the Chart
I'm going to:
And then I'm going to insert my chart.
Because we've got these blank rows, we've got East has its first year of data and then its second year, and then there's a blank where the third row is empty, and the same for each of the other regions.
Now to make these look more clustered, I'll do a little formatting.
and now it's looking more clustered.
There's a bigger space between the regions than there is between the stacks for each region.
The final thing you could do to make this look nicer, is to match up the colours.
Right now, winter for both years is blue, but you could make this a different shade of orange.
And now you have a cluster stack chart and you can compare year to year totals for each region.
Clustered Stacked Chart In Excel
In Excel, you can create a Stacked Column chart, or a Clustered Column chart, using the built-in chart types.
Excel does not have a built-in Clustered Stacked Column chart type, but this tutorial shows 3 different methods that you can use to create an Excel cluster stack chart.
Clustered Stacked Chart Example
In the examples shown below, there are
With each method, the goal is to create a cluster stack chart, similar to one shown below.
A) Data in Summary Grid - Rearrange Data
If your data is summarized in a grid, like the one shown below, you can rearrange the data slightly, and then you'll be able to create a cluster stack chart.
The table has 2 years of data for seasonal sales per region, with the seasons in different columns.
Build a Cluster Stack Chart
The instructions below will show you how to create the chart from this type of data:
1) First, you'll see the limitations of using Excel's built-in chart types.
2) Next, you'll see the easy steps to rearrange your data slightly, before building the cluster stack chart.
NOTE: There are written steps, and there is also a step-by-step video below.
Excel's Built-In Chart Types
Excel does not have a built-in cluster stack chart type. The two built-in Excel chart types that come closest are:
Neither of those chart types do exactly what you need, as you can see in the two examples shown below.
Clustered Column chart
Here is a clustered column chart, built from the regional sales data that is shown in the previous section. In this chart:
Stacked Column chart
Next, here is a stacked column chart, built from the same regional sales data. In this chart:
How to Make a Cluster Stack Chart
Instead of using one of those built-in chart types, follow the steps below, to create a Cluster Stack chart.
To create a Clustered Stacked chart in Excel, there are 2 main steps, described in detail below:
1) Change the Data Layout
Whether you want to make a cluster stack column chart or a cluster stack bar chart, follow these steps to change the data layout.
Original Data Layout
Here is the original data layout for the sample data, arranged in a summary grid. The table shows 2 years of data for seasonal sales per region.
Make Data Layout Changes
Make the following small changes to the data:
NOTE: The video below shows a quick way to make those changes, by dragging the data with your mouse.
Revised Data Layout
Here is the revised data layout, for the clustered stacked Excel chart. The second year's data has been shifted down one row, and blank rows were inserted.
In the revised data layout shown above:
2a) Create a Clustered Stacked Column Chart
Here are the steps to create a clustered stacked column chart from the revised data:
Change the Gap Width
At first, the chart's columns are narrow, and spread apart. To make the chart look better, make the following changes to the gap width setting.
After you reduce the Gap Width setting, the columns are wider, and closer together
Change the Chart Colours
At first, each season/year combination has a different colour. To make the seasons easier to compare, year to year. you can change the chart colours, for the second year's stacked column
Make the second year's colours a lighter shade of the first year's colours, for each season. For example:
Then, repeat those steps to colour the remaining seasons for the second year.
Completed Cluster Stack Column Chart with Format Changes
When it is finished, the Clustered Stacked Column chart should look similar to the chart below.
For each region:
2b) Create a Clustered Stacked Bar Chart
If you chose the Stacked Bar chart type, the Clustered Stacked Bar chart should look like the one in the screenshot below.
For each region:
Example 2: Compare Win/Loss Scores
Here's another example of a Cluster Stack chart, based on data in a summary grid.
This chart compares the highest and lowest win/loss scores for a fantasy football league, for each week in the season. There 3 weeks of data so far.
Original Data Layout
Originally, the win/loss data had one column per week, with four rows:
Revised Data Layout
To create a cluster stack chart, the following changes were made to the data:
Next, a new section was added to the worksheet. In this section:
Cluster Stack Chart for Win/Loss
Here is the chart that was created from the new formula section of Win/Loss fantasy football data layout.
B) Data in Detail Rows - Pivot Chart
If your data is in details rows, instead of a summary grid, there is a quick and easy way to make a cluster stack chart. First, make a pivot table, and then make a pivot chart based on that pivot table.
An overview of this method is shown below, and that might be all the help you need for this method. If you'd like to see the detailed steps for creating the pivot table and pivot chart, you can go to the Cluster Stack Pivot Chart page.
NOTE: This type of cluster stack chart has equal space between all the columns. The "cluster" effect is created by the labels and lines below the horizontal axis
Data in Detail Rows
To use this pivot chart method, your data must be in details rows. This screen shot shows a named Excel table, with sales data for 2 years, for 4 different regions.
Each column has one type of information - Region, Year, Season, Sales and Season Number (Ssn)
Make a Pivot Table
First, create a new blank pivot table, based on the table with data in detail rows.
Next, add fields to the pivot table layout, based on the cluster stack chart that you want:
Make Pivot Chart
Next, create a stacked column chart based on the pivot table.
Here is the pivot chart, with a "cluster" for each Region, and a stack for each Year, showing a breakdown by season.
C) Save Time with Excel Add-in
If you frequently need to make this type of clustered stacked chart, or other custom Excel charts, you can save time by using an Excel add-in.
Go to the Peltier Tech website (affiliate link), and check out Jon Peltier's Excel charting utility.
Jon's charting utility can help you create complex Excel charts, quickly and easily - much faster than building the charts yourself, from scratch.
Get the Sample Files
More Chart Tutorials
Last updated: November 9, 2022 12:28 PM