Make a Clustered Stacked Column Pivot Chart in Excel

Excel doesn’t have a built-in Cluster Stack chart type, but you can make one, with a quick pivot chart.

See how to make the chart shown below, with stacked columns, grouped into regional “clusters”.

Video: Clustered Stacked Column Pivot Chart

In this short video, you’ll see the steps for making a pivot table, and then a clustered stacked column pivot chart.

The full transcript is below the video, and there are full details on the How to Make Excel Clustered Stacked Column Pivot Chart page on my Contextures site.

Video Transcript

Here’s the video transcript, with a few screen shots added.

———-START OF TRANSCRIPT

In this video, you’ll see how to make a pivot chart that is clustered and stacked.

We’ve got a cluster for each region, and a stack for each year, showing the four quarters of those years.

This is Debra Dalgleish from contextures.com.

Data in Excel Table

To create this chart, we’re using data that’s in a named Excel table.

If I click in one of the heading cells, you can see Table Design up at the top.

There are four regions, and we’ve got data over two years for every quarter in each year.

So we’re using this to create a pivot table and then a pivot chart.

Make a Pivot Table

To create the pivot table:

Select any cell in this named Excel table and
Go to the Insert tab on the Ribbon.
Click Tables, Pivot Table.
Here, it’s automatically selected our table, which is named tblSales.
I’ll put this pivot table on a new worksheet, and I’m not going to add it to the Data Model
So I’ll click OK, and here’s our empty pivot table.

Add Fields to Pivot Table

At the right, we’ve got the pivot table fields, and those are the four columns that are in the named Excel table with the data.

To add fields to the pivot table, we’re going to think about how we want the chart to look.

In the Row area, we want the fields that should be across the bottom of the chart, the Regions and the Years.
- I’m going to put Region first, because that’s our main grouping
And then for each region, we want to see a stacked column showing the quarters for each year
- So I’ll check Year next.
Excel decided, because that’s a number, I want it in the Values area, which is not what I want.
- I’m going to move it in the Pivot Table Field List, and just drag it below Region.

Now we’ve got the Region name and the Year.

Add More Fields

Within each Year, we want to see a stack that shows the four quarters

So I’m going to take Qtr, and put it into the Columns area.

This is what we want to stack in a column, and that now shows across the top.

The values that we want to see in our stacked column is the Sales amount
So if I check that it should go into values automatically.

Those are the numbers that we want to see, for each Year, for each Region.

Create a Pivot Chart

The next step is to create a pivot chart based on this pivot table.

To do that, I’m selecting any cell in this pivot table
Then I go up to the Insert tab
And for the Chart Type, I’ll click Column
And under 2D Column, I want a Stacked Column chart.

That puts the pivot chart right on the worksheet.

It overlaps our pivot table, so you can do a bit of formatting to clean this up.

Format the Pivot Chart

The first thing you might do is move this to a different sheet.

If I right-click, Move Chart, you could put it on a chart sheet or put it on an existing sheet.
Now it’s on a sheet all on its own.

We can see the groups, but they’re spread apart a bit

So I’m going to change the gap width, to bring everything closer together.

To change the gap width,

Right-click on any of the columns and Format Data Series.
In the Format Data Series pane that appears, the Gap Width is at the bottom.
I’m going to make that a much smaller number: 20.

That brings everything closer together

And you’ve got a pivot chart, grouped by Region and Year, and stacked by quarter.

———-END OF TRANSCRIPT

Get the Sample File

You can get the Excel file, and detailed steps, on the How to Make Excel Clustered Stacked Column Pivot Chart page on my Contextures site.

____________________