How To Create A Clustered Stacked Column Chart In Excel

Create Excel Cluster Stack Charts

A cluster stack chart has clusters of columns or bars, with a stack in each column or bar. See how to set up your Excel data to create a cluster stack column chart or a cluster stack bar chart. Or use a pivot table and pivot chart, for a quick and easy solution.

What Is a Clustered Stacked Chart?

A Clustered Stacked chart is a combination of a Stacked Column or Bar chart, and a Clustered Column or Bar chart. Excel does not have a built-in Clustered Stacked Column or Bar chart type, but this tutorial different ways to create one.

Follow the steps below, to create a cluster stack chart, similar to this one. This chart has:

• a Cluster of columns for each region (East, West, North, South)
• a Stack in each column, with a different coloured segment for each season in the year

There are written steps, and there is also a step-by-step video below.

Clustered Stacked Chart In Excel

In Excel, you can create a Stacked Column chart, or a Clustered Column chart, using the built-in chart types. Excel does not have a built-in Clustered Stacked Column chart type, but this tutorial shows 3 different methods that you can use to create one.

• A) Data in a Summary Grid - Rearrange the Excel data, then make a chart
• B) Data in Detail Rows - Make a Pivot Table & Pivot Chart
• C) Data in a Summary Grid - Save Time with Excel Add-In

Clustered Stacked Chart Example

In the examples shown below, there are

• 2 years of data
• 4 seasons of sales amounts each year
• 4 different regions

With each method, the goal is to create a cluster stack chart, similar to one shown below.

• The chart has a Cluster for each region, with 2 columns in each cluster - one for each year of data
• There is a Stack in each column, with a segment for each season in that year

A) Data in Summary Grid - Rearrange Data

If your data is summarized in a grid, like the one shown below, you can rearrange the data slightly, and then you'll be able to create a cluster stack chart.

• NOTE: If your data is in details rows, instead of a summary grid, try the Pivot Table Cluster Stack Chart method instead

The table has 2 years of data for seasonal sales per region.

Build a Cluster Stack Chart

The instructions below will show you how to create the chart from this type of data:

1) First, you'll see the limitations of using Excel's built-in chart types.

2) Next, you'll see the easy steps to rearrange your data slightly, before building the cluster stack chart.

NOTE: There are written steps, and there is also a step-by-step video below.

Excel's Built-In Chart Types

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

• Clustered Column chart
• Stacked Column chart

Neither of those chart types do exactly what you need, as you can see in the two examples shown below.

Clustered Column chart

Here is a clustered column chart, built from the regional sales data that is shown in the previous section. In this chart:

• The sales data is grouped by region -- there is a cluster of columns for each region
• Each season, per region, is in a separate column - the legend at the right helps you find each year/season combination for each region
• You can't compare year totals per region -- only the year/season amounts are shown

Stacked Column chart

Next, here is a stacked column chart, built from the same regional sales data. In this chart:

• The sales data is grouped by region -- there is a single stacked column for each region
• All year/season amounts, per region, are in a single column - the legend at the right helps you find each year/season combination for each region
• You can't compare year totals per region -- only overall 2-year total is shown

How to Make a Cluster Stack Chart

Instead of using one of those built-in chart types, follow the steps below, to create a Cluster Stack chart.

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

1. Make changes to the data layout
2. Create a chart from the revised data
   • a) Cluster Stack Column Chart
   • b) Cluster Stack Bar Chart

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.

Original Data Layout

Here is the original data layout for the sample data, arranged in a summary grid. The table shows 2 years of data for seasonal sales per region.

• Year and season names are in columns, across the top
• Region names are in rows, down the left side
• Sales amounts are in the grid, for each region, for each year/season

Make Data Layout Changes

Make the following small changes to the data:

NOTE: The video below shows a quick way to make those changes.

1. Add a blank row after the heading row, to act as a spacer
2. Add 2 blank rows after each region's data
3. Move the second year's data down one row.
4. Insert blank rows where you want columns separated
5. Add a blank row at the end of the data

Revised Data Layout

Here is the revised data layout, for the clustered stacked Excel chart. The second year's data has been shifted down one row, and blank rows were inserted.

In the revised data layout shown above:

1. For each region
• First year's data (red border) is in one row
• Second year's 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 required - just a reminder not to delete spacer rows

2a) 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 cells in the data range.
  • In the sample data, select the cells within the thick outline border, in screen shot above (cells B2:J15)
• Click the Insert tab, at the top of Excel, and click the Insert Column or Bar Chart command
• In the 2-D Column section, click Stacked Column
  • OR, in the 2-D Bar section, click Stacked Bar

Change the Gap Width

At first, the chart's columns are narrow, and spread apart. To make the chart look better, make the following changes to the gap width setting.

• Right-click on one of the columns, and click Format Data Series
• The Format Data Series pane opens, usually at the right side of the Excel window
• Change the Gap Width setting to a low number - between 0% and 10%

After you reduce the Gap Width setting, the columns are wider, and closer together

Change the Chart Colours

At first, each season/year combination has a different colour. To make the seasons easier to compare, year to year. you can change the chart colours, for the second year's stacked column

Make the second year's colours a lighter shade of the first year's colours, for each season. For example:

• Click the Winter segment, in one of the second year stacked columns,
• At the top of Excel, click the Format tab, at the far right
• Click the arrow for Shape Fill
• Select a fill colour that's a lighter shade of the first year' colour for that season

Then, repeat those steps to colour the remaining seasons for the second year.

Completed Cluster Stack Column Chart with Format Changes

When it is finished, the Clustered Stacked Column chart should look similar to the chart below.

For each region:

• First year's data is in the left column
• Second year's data is in the right column
• Seasonal colours are similar, to make comparisons easier

2b) 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

Video: Cluster Stack Column Chart

This short video shows how to set up your Excel data, by adding blank rows to space the region and year data, and putting the annual data on different rows. Then, build the cluster stack chart, and make a couple of quick formatting changes, to end up with an attractive, and easy-to-understand clustered stacked chart.

Example 2: Compare Win/Loss Scores

Here's another example of a Cluster Stack chart, based on data in a summary grid.

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

Cluster Stack Chart for Win/Loss

Here is the chart that was created from the new formula section of Win/Loss fantasy football data layout.

• There is a cluster for each week, with 2 columns in each cluster - Win and Loss
• Each column is stacked, with a segment for high (red), and a segment for low (grey)

B) Data in Detail Rows - Pivot Chart

If your data is in details rows, instead of a summary grid, there is a quick and easy way to make a cluster stack chart. First, make a pivot table, and then make a pivot chart based on that pivot table.

An overview of this method is shown below, and that might be all the help you need for this method. If you'd like to see the detailed steps for creating the pivot table and pivot chart, you can go to the Cluster Stack Pivot Chart page.

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

Data in Detail Rows

To use this pivot chart method, your data must be in details rows. This screen shot shows a named Excel table, with sales data for 2 years, for 4 different regions.

Each column has one type of information - Region, Year, Season, Sales and Season Number (Ssn)

Make a Pivot Table

First, create a new blank pivot table, based on the table with data in detail rows.

Next, add fields to the pivot table layout, based on the cluster stack chart that you want:

• Rows: Add fields for chart's horizontal axis -- Region and Year in this example
• Columns: Add fields for the "stack" -- Season number (Ssn) in this example
• Values: Add number field - Sales in this example

Make Pivot Chart

Next, create a stacked column chart based on the pivot table.

• Tip: To make the columns wider, change the gap width to a low percentage, such as 20%

Here is the pivot chart, with a "cluster" for each Region, and a stack for each Year, showing a breakdown by season.

C) Save Time with Excel 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.

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.
• Pivot Chart Cluster Stack: See sales data in 2 different cluster stack layouts, using pivot tables and pivot charts. The zipped workbook is in xlsx format, and does not contain any macros.

How To Create A Clustered Stacked Column Chart In Excel

Source: https://www.contextures.com/Excel-Addins-Charts-Cluster.html

Posted by: furnesswidefirearm.blogspot.com

Share this post