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.
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 will quickly pay for itself, because you wont have to waste your time fiddling with data arrangements and chart settings.
Just select your data, click the Cluster Stack button, set a few options, and click OK, as shown in the Excel cluster stack chart video below.
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 buy a copy now!
You'll save time and reduce frustration, and your boss and clients will be impressed by how quickly and easily you can create complex Excel charts.
There are 8 custom chart types in this All-in-One Chart Tool -- Waterfall, Stacked Waterfall, Cluster Stack, Marimekko, Cascade, Box Plot, Dot Plot, and Quick XY Chart.
The tool also includes a Loess data tool, and several other chart tools, like Export Chart, and Label Last Point.
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: November 22, 2016 3:22 PM