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.
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.
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 one.
In the examples shown below, there are
With each method, the goal is to create a cluster stack chart, similar to one shown below.
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.
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 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.
Here is a clustered column chart, built from the regional sales data that is shown in the previous section. In this chart:
Next, here is a stacked column chart, built from the same regional sales data. In this 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:
Whether you want to make a cluster stack column chart or a cluster stack bar chart, follow these steps to change the 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 the following small changes to the data:
NOTE: The video below shows a quick way to make those changes.
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:
Here are the steps to create a clustered stacked column chart from the revised data:
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
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.
When it is finished, the Clustered Stacked Column chart should look similar to the chart below.
For each region:
If you chose the Stacked Bar chart type, the Clustered Stacked Bar chart should look like the one below.
For each region:
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.
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.
Originally, the win/loss data had one column per week, with four rows:
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:
Here is the chart that was created from the new formula section of Win/Loss fantasy football data layout.
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
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)
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:
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.
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.
Last updated: November 14, 2021 9:57 AM