Create Excel Cluster Stack Charts

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.

Cluster Stack Chart Introduction

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.

• Cluster for each region
• Stack for each year, with a segment for each season

Excel's Built-In Chart Types

Excel does not have a built-in cluster stack chart type. The two built-in chart types that come closest are:

• Clustered Column chart
• Stacked Column chart

Here is a clustered column chart, built from the regional sales data.

• Date is grouped by region
• Each season, per region, is in a separate column
• You can't compare year totals per region

And here is a stacked column chart, built from the regional sales data.

• Date is grouped by region
• All seasons, per region, are in the same column
• You can't compare year totals per region

How to Make a Cluster Stack Chart

To create a Clustered Stacked chart in Excel, there are 2 main steps:

1. Make changes to the data layout
2. Create a chart from the revised data

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.

Here is the original data layout.

Make these 3 changes to the data, as shown in the screen shot below:

1. Put data for different columns on separate rows
2. Insert blank rows where you want columns separated
3. Add a spacer row at the start and end

In the revised data layout shown below:

1. For each region
• 2020 data (red border) is in one row
• 2021 data (blue border) was moved down to the row below
2. Blank row was inserted after each region
3. Spacer rows above the first region, and after the last region

• orange cells have this formula, to create an empty string: =""
• zero values aren't needed - just a reminder not to delete spacer rows

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:

• blank rows (yellow) and the spacer rows (blue) are highlighted.
• orange cells (B3 and B15) contain formulas for empty strings.

Create a Clustered Stacked Column Chart

Here are the steps to create a clustered stacked column chart from the revised data:

• Select the headings, data and blank rows (cells within thick border, in screen shot above)
• Create a Stacked Column chart from the selected data
  • OR, choose the Stacked Bar chart type instead
• Change the Gap Width to zero
• (optional) Change the chart colours

When it is finished, the Clustered Stacked Column chart should look like the one below.

For each region:

• 2020 data is in the left column
• 2021 data is in the right column

Create a Clustered Stacked Bar Chart

If you chose the Stacked Bar chart type, the Clustered Stacked Bar chart should look like the one below.

For each region:

• 2020 data is in the top bar
• 2021 data is in the bottom bar

Compare Win/Loss Scores

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.

Original Data Layout

Originally, the win/loss data had one column per week, with four rows:

1. Win Low
2. Win High
3. Lose Low
4. Lose High

Revised Data Layout

To create a cluster stack chart, the following changes were made to the data:

• blank rows and columns were added
• win and loss data were put in separate columns for each week

Calculations Added

Next, a new section was added to the worksheet. In this section:

• formulas calculate the differences between high and low scores
  • For example, this formula is in cell D20: =D8-D7
• yellow cells are used as source data for the cluster stack chart

Get the Sample Files

• Region Data: To see the original data layout, and the rearranged data, with the cluster stack chart, get the Cluster Stack Chart sample file. The zipped workbook is in xlsx format, and does not contain any macros.
• Win/Loss Data: To see how the win/loss chart data is set up, and view the completed chart, you can get the Win and Loss Chart sample file. The zipped file is in xlsx format, and does not contain macros.

Peltier Tech Chart Utility 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.

P.S. There are Standard and Advanced versions of the Excel charting utility, so choose the version that has the tools you need.