How to set up your Excel data to create a cluster stack column chart or a cluster stack bar chart. See how to add blank rows to space the data, and separate the data into different rows, before you build the chart.
Are you struggling to create a chart from annual or monthly data, and make the results easy to understand? An Excel cluster stack chart would be perfect, but you can't figure out how to create one.
For example, this table shows 2 years of data for seasonal sales per region.
And this is the type of cluster stack chart you'd like to create from your data.
Excel does not have a built-in cluster stack chart type. The two built-in chart types that come closest are:
Here is a clustered column chart, built from the regional sales data.
And here is a stacked column chart, built from the regional sales data.
To create a Clustered Stacked chart in Excel, there are 2 main steps:
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.
Make these 3 changes to the data, as shown in the screen shot below:
In the revised data layout shown below:
Here is the revised data layout, for the clustered stacked Excel chart. The 2021 data has been shifted down one row.
In this screen shot of the revised data layout:
Here are the steps to create a clustered stacked column chart from the revised data:
When it is finished, the Clustered Stacked Column chart should look like the one 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:
Here's another example of a Cluster Stack chart. 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:
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.
P.S. There are Standard and Advanced versions of the Excel charting utility, so choose the version that has the tools you need.
Last updated: July 20, 2021 3:57 PM