Home > Pivot > Charts > Cluster Stack How to Make Excel Clustered Stacked Column Pivot Chart How to make an Excel cluster stack column pivot chart or a cluster stack bar pivot chart, based on your source data. Quick and easy trick, then make copies for different layouts. For other ways to create this type of chart, go to the Cluster Stack Chart page.

Cluster Stack Chart Introduction

Excel doesn't have a Cluster Stack chart type, but you can make a pivot chart with stacked columns that are grouped into "clusters".

In the example shown in the screen shot above:

• Regions are clustered, with dividing lines between the regions
• There is a stack for each year, showing the sales per season

NOTE: This type of cluster stack chart has equal space between all the columns. The "cluster" effect is created by the region labels and dividing lines below the horizontal axis

Video: Make a Clustered Stacked Column Pivot Chart

You can watch this short video, to see the steps for making a pivot table, and a clustered stacked column pivot chart. There are written steps below the video.

Source Data for Pivot Table

To build a pivot table, the data should be in a named Excel table, like the one shown below, where:

• each field is in a separate column: Region, Year, Season, Sales, Ssn (season number)
• all the Sales amounts are in a single column
• each Sales amount is on a separate row

If your data is in this layout, go to the Make a Pivot Table section below.

Different Data Layout

Instead of a named table, with each sales amount on a separate row, your data might be in a different layout, like this summary report of seasonal sales per region.

Here, there are 8 sales amounts in each region row, in a different column for each year/season combination.

For that type of data, try one of these suggestions:

• use the steps on the Cluster Stack Chart page to slightly rearrange the data, and build the chart.
• OR, follow the steps at the end of this page, to change your data layout.

Make a Pivot Table

To create a pivot table for the cluster stack chart, follow these steps:

• Select any cell in the source data table
• On the Insert tab, click Pivot Table
• The Table/Range box should automatically show the name of the selected table
• Click OK, to create the blank pivot table

Next, add the following fields to the pivot table layout:

• In the Rows area, add the fields you want in the chart's horizontal axis -- Region and Year in this example
• In the Columns area, add the fields you want to "stack" -- Season number (Ssn) in this example
• Add the number field to the Values area - Sales in this example
• (Optional) Turn off Subtotals and Grand Totals -- this will not affect the pivot chart

Pivot Chart Cluster Stack

The final step is to create a pivot chart, based on the pivot table.

• Select any cell in the pivot table
• On the Insert tab, click the Column Chart button
• In the 2-D Column section, click on Stacked Column chart type

Format the Chart (Optional)

• Right-click one of the columns, and click Format Data Series
• To make the columns wider, change the Gap Width to a lower number, such as 40%

In the screenshot below, you can see the completed Cluster Stack Pivot Chart:

• Regions are clustered, with dividing lines between the regions
• There is a stack for each year, showing the sales per season

The result is similar to a clustered column chart, but with stacked columns, instead of solid columns. Legend entries show the color for each season

Create Different Pivot Charts

After you create the first pivot table and pivot chart, you can duplicate that worksheet, and create different reports. For example:

• Make a copy of the original pivot table/chart worksheet
• In the pivot table, move the Year field above the Region field
• The pivot chart changes automatically --Years are "clustered", with a stack for each Region

Or, change chart type, to create a cluster stack bar chart.

Change Data to Table Layout

To change your data from a report-style layout, to a named Excel table, follow the steps below. This technique uses Power Query (Get & Transform) to rearrange the data automatically.

You can download the sample file below -- get the Report-Style Data sample file.

Get the Data

With the Get & Transform feature, you can:

1. use data that's in the active workbook
2. OR, get data from a different workbook.

1) If your data is on a sheet in the active workbook, follow these steps to get the data:

• WARNING: If your data is NOT in a named table, the steps below will convert it to a named table. To keep the data in its current format, make a copy of it. Then, use these steps on the copied data.

• Select the table or range of cells where the data is stored
• On the Excel Ribbon, click the Data tab
• At the left, in the Get & Transform group, click From Table/Range
• If the data is NOT in a named table, the Create Table dialog box appears.
  • Click OK to create the named table

2) If your data is in a different workbook, follow these steps to get the data:

• Close the data workbook, and open a new workbook in Excel
• On the Excel Ribbon, click the Data tab
• At the left, in the Get & Transform group, click Get Data
• Point to the From File option, then click From Workbook
• Browse to the folder where your data file is stored, select your file, and click Import
• Next, in the Navigator window, select the sheet where your data is stored - Sheet1 in the sample file
• At the right, you should see your data, with column headings, and empty cells, if there are any
• Click the Transform Data button

Power Query Editor

Your data appears in a table in the Power Query Editor, with the query name at the left. At the right, the Query Settings pane shows the query name, and a list of Applied Steps.

First, follow these steps, if needed, to put the headings in place. The commands are highlighted in the screen shot below.

• At the top, on the Home tab, click Reduce Rows, and click Remove Blank Rows
• Next, click Use First Row as Headers (don't click the arrow)

Unpivot the Data

In the data, there are 32 sales amounts, stored in the table:

• 4 rows of data - one for each region
• 8 columns of sales amounts - one for each year/season combination

Next, follow these steps to "unpivot" the data, which will put each sales amount into a separate row, along with its region, year and season details.

• To begin, select the first column only (click on its heading)
• Click the Transform tab
• Click the arrow beside Unpivot Columns
• In the drop-down list, click Unpivot Other Columns

The data "unpivots" and transforms to 32 rows and 3 columns, with each sales amount in a separate row.

Split Year and Season

Next, follow these steps to create separate columns for the year and season:

• Select the column that has the combined year and season
• On the Transform tab, in the Text Column group, click Split Column, then By Delimiter
• Under Select or Enter Delimiter, choose Space from the drop-down list
• For Split at, choose Each occurrence of the delimiter, and click OK

Add Season Number

Next, follow these steps to create a new column for the season number. This will make it easier to sort the data by season, if needed:

• Select the column with the season names
• At the top, click the Add Column tab
• At the left, click Column From Examples, then click From Selection
• In the new column:
  • Type a 1 beside the first Win
  • Type a 2 beside the first Spr
    • The new column will show an AutoFill suggestion, with "null" in some rows
  • Type a 3 beside the first Sum
  • Type a 4 beside the first Fall
• Above the new column, click OK, to accept the AutoFill suggestions

The new column has numbers 1-4, matched to the season name

Rename the Columns

The final step in transforming the data is to give the columns meaningful names.

• Right-click the first column heading, and click Rename (or double-click the heading)
• Type Region as the new name
• Rename the remaining columns with these names: Year, Season, Sales, Ssn

Optional: Instead of leaving the default query name (Sheet1), you can change it

• In the Query Setting pane, select the query name
• Type a new name, such as SeasonSales
• Press Enter, to complete the name change

Load the Data

The data has been transformed to a structured table, and is ready to load to the workbook.

• At the top, click the Home tab
• Click the arrow beside Close & Load
• Click on Load To
• In the Import Data dialog box, select PivotTable Report
• Click OK, to create the pivot table

Then, go to the Make a Pivot Chart section above, to continue with the pivot chart.

Get the Sample Files

• Report-Style Data: To try the steps for changing report-style data to a named Excel table, get the Region Sales Seasons sample file. The zipped workbook is in xlsx format, and does not contain any macros.
• Excel Table Data: To see the sample data in a named Excel table, with 2 different chart layouts, get the Named Table sample file. The zipped workbook is in xlsx format, and does not contain any macros

Related Links

Cluster Stack Chart

Pivot Charts, Compare Years

Create a Pivot Table

Unpivot Source Data -Macro