How to set up Excel data to create a clustered stacked column chart, manually, or with a time-saving Excel add-in.
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 meat production, in thousands of tonnes.
Using Excel's built-in chart types, you can create a clustered column chart:
Or you can create a stacked column chart:
The Stacked Column chart is okay, but it doesn't let you compare the years and the seasons. Ideally, the chart should keep the Stacked Column chart, but with the years clustered, or the seasons clustered -- combining the features of the clustered column and the stacked column, to create a cluster stack chart.
Excel doesn't have a Cluster Stack chart type, but you can create your own version, by carefully rearranging your data. It will take some time and patience, and you can find the cluster stack chart instructions below.
The key to creating a Clustered Stacked chart is to arrange the data with blank rows where you want columns separated, and put the data for different columns on separate rows.
In this example, the 2002 and 2003 data is in separate rows, with a blank row after each meat type. There's also a blank row before the first meat type, to create space at the left in the chart. The new arrangement looks like this:
Create a Stacked Column chart from this data, then change the Gap Width to zero, and adjust the series, to include the first and last blank rows. When it is finished, the Clustered Stacked Column chart should look like this:
The stack on the left of each pair is 2002 and the stack on the right is 2003. Now it's clear that poultry production went up slightly in 2003, and the Other category went down.
To see the original data layout, and the rearranged data, with the cluster stack chart, download the Cluster Stack Chart sample file. The zipped workbook is in xlsx format, and does not contain any macros.
If you don't have the time or patience to arrange your data and create your own Excel Cluster Stack Column chart, Excel charting guru, Jon Peltier, offers a fantastic Excel Chart Utility. You can relax, while the chart utility does all the work.
Reasonably priced, the Peltier Tech chart add-in is a great investment, because you won't have to waste your time fiddling with data arrangements and chart settings.
This short video shows how easy it is to create a Cluster Stack chart using the Excel Chart Utility.
Instead of a long, painful process, the cluster stack chart is created in a few seconds. A new sheet is inserted in your workbook, with data linked to your original data, and a shiny, new Excel Cluster Stack Chart.
Here's the Excel cluster stack chart with clusters of stacked columns, showing production by type, with a stack for each year.
If you need to make this type of cluster stack chart, or other custom Excel charts, go to the Peltier Tech website, and check out Jon Peltier's Excel charting utility. The charting utility can help you create complex Excel charts, quickly and easily - much faster than building the charts yourself, from scratch.
There are Standard and Advanced versions of the Excel charting utility, so choose the version that has the tools you need.
Note: These are affiliate links, and I'll earn a small commission if you purchase the cluster stack chart utility through these links.
Last updated: April 19, 2021 1:54 PM