Microsoft Excel Tutorial - Creating Cluster Stack Chart in Excel.
Jeff from Akron asks how to create a column chart where two series are stacked, but a third series is clustered. Episode 2595 shows you how.
To download the workbook: Excel Stacked And Clustered Chart in Excel - Epsiode 2595 Sample Files - MrExcel Publishing
This video requires Excel 2013 or newer. There is an older video for Excel 2007 or Excel 2010:
For other variations:
Leila has a video where each column has a Total (say 2000) and a second column showing a subset (say 500). That video is here:
Jon Peltier sells a charting utility where there are quarterly clusters and every column contains a stack: bit.ly/41QxYdM
Every demo of this chart has four quarters. Any time the items along the horizontal axis are 6 or less, you have to do a longer set of steps:
1. Insert two blank series with zeroes after Series 1b
2. Insert Line1, Line2 with =MAX() in Q1
3. Create a Stacked Column Chart
4. Switch Row/Columns so quarters along bottom
5. Change Chart Type
6. Choose Combo Chart
7. Resize dialog
8. Choose 2nd Axis for Blank1, Blank2, Series2, Line1
9. Change Series 1a to Stacked (which changes series 1b automatically)
10. Change Blank1, Blank2, Series2 to Clustered
11. Change Line1, Line2 to Line
12. OK
13. Double-Click the Left column (Format pane opens)
14. Increase Gap Width to about 388%
15. Click on Legend
16. Move Legend to Top
17. Click on Blank 1 in Legend to select it
18. Press Delete Key
19. Click on Legend again, Click on Blank 2, press Delete key
20. Click on Legend again, Click on Line1, press Delete key
21. Click on Legend again, Click on Line2, press Delete key
22. Click on Right vertical axis
23. With right axis selected, white font from Home tab
If your data has 7 or more items along the horizontal axis (perhaps because you have 12 months), then you can follow these slightly easier steps:
1. Insert two blank series with zeroes after Series 1b
2. Insert Line1, Line2 with =MAX() in Q1
3. Use Dialog launch for all chart types, click Combo
4. Resize dialog
5. Choose 2nd Axis for Blank1, Blank2, Series2, Line1
6. Change Series 1a to Stacked (which changes series 1b automatically)
7. Change Blank1, Blank2, Series2 to Clustered
8. Change Line1, Line2 to Line
9. OK
10. Double-Click the Left column (Format pane opens)
11. Increase Gap Width to about 388%
12. Click on Legend
13. Move Legend to Top
14. Click on Blank 1 in Legend to select it
15. Press Delete Key
16. Click on Legend again, Click on Blank 2, press Delete key
17. Click on Legend again, Click on Line1, press Delete key
18. Click on Legend again, Click on Line2, press Delete key
19. Click on Right vertical axis
20. With right axis selected, white font from Home tab
Table of Contents
(0:00) Chart with 2 Stacked & 1 Clustered Series
(0:25) Use other video for Excel 2007-2010
(0:39) Leila version: Series 1 is Total, Series 2 is a subset in foreground
(1:01) Jon Peltier Add-In: every column is a stack
(1:11) Deciding which steps
(1:30) Pause button in YouTube
(1:56) Steps to Create Chart with Less than 7 Items
(3:38) Why Blank1 & Blank2
(5:20) Deleting specific Excel chart legend entries
(5:50) Keep left & right axis in sync with 2 invisible line chart series
(6:09) What would happen without Line1 and Line2
(6:57) Steps to Create Chart with More than 6 Items
(9:15) Wrap-up
Jeff from Akron asks how to create a column chart where two series are stacked, but a third series is clustered. Episode 2595 shows you how.
To download the workbook: Excel Stacked And Clustered Chart in Excel - Epsiode 2595 Sample Files - MrExcel Publishing
This video requires Excel 2013 or newer. There is an older video for Excel 2007 or Excel 2010:
For other variations:
Leila has a video where each column has a Total (say 2000) and a second column showing a subset (say 500). That video is here:
Jon Peltier sells a charting utility where there are quarterly clusters and every column contains a stack: bit.ly/41QxYdM
Every demo of this chart has four quarters. Any time the items along the horizontal axis are 6 or less, you have to do a longer set of steps:
1. Insert two blank series with zeroes after Series 1b
2. Insert Line1, Line2 with =MAX() in Q1
3. Create a Stacked Column Chart
4. Switch Row/Columns so quarters along bottom
5. Change Chart Type
6. Choose Combo Chart
7. Resize dialog
8. Choose 2nd Axis for Blank1, Blank2, Series2, Line1
9. Change Series 1a to Stacked (which changes series 1b automatically)
10. Change Blank1, Blank2, Series2 to Clustered
11. Change Line1, Line2 to Line
12. OK
13. Double-Click the Left column (Format pane opens)
14. Increase Gap Width to about 388%
15. Click on Legend
16. Move Legend to Top
17. Click on Blank 1 in Legend to select it
18. Press Delete Key
19. Click on Legend again, Click on Blank 2, press Delete key
20. Click on Legend again, Click on Line1, press Delete key
21. Click on Legend again, Click on Line2, press Delete key
22. Click on Right vertical axis
23. With right axis selected, white font from Home tab
If your data has 7 or more items along the horizontal axis (perhaps because you have 12 months), then you can follow these slightly easier steps:
1. Insert two blank series with zeroes after Series 1b
2. Insert Line1, Line2 with =MAX() in Q1
3. Use Dialog launch for all chart types, click Combo
4. Resize dialog
5. Choose 2nd Axis for Blank1, Blank2, Series2, Line1
6. Change Series 1a to Stacked (which changes series 1b automatically)
7. Change Blank1, Blank2, Series2 to Clustered
8. Change Line1, Line2 to Line
9. OK
10. Double-Click the Left column (Format pane opens)
11. Increase Gap Width to about 388%
12. Click on Legend
13. Move Legend to Top
14. Click on Blank 1 in Legend to select it
15. Press Delete Key
16. Click on Legend again, Click on Blank 2, press Delete key
17. Click on Legend again, Click on Line1, press Delete key
18. Click on Legend again, Click on Line2, press Delete key
19. Click on Right vertical axis
20. With right axis selected, white font from Home tab
Table of Contents
(0:00) Chart with 2 Stacked & 1 Clustered Series
(0:25) Use other video for Excel 2007-2010
(0:39) Leila version: Series 1 is Total, Series 2 is a subset in foreground
(1:01) Jon Peltier Add-In: every column is a stack
(1:11) Deciding which steps
(1:30) Pause button in YouTube
(1:56) Steps to Create Chart with Less than 7 Items
(3:38) Why Blank1 & Blank2
(5:20) Deleting specific Excel chart legend entries
(5:50) Keep left & right axis in sync with 2 invisible line chart series
(6:09) What would happen without Line1 and Line2
(6:57) Steps to Create Chart with More than 6 Items
(9:15) Wrap-up
Transcript of the video:
Stacked and clustered chart in Excel.
The goal today, this is from Jeff from Akron. Jeff has two things that have to be stacked on the left hand side, so Series 1a and Series 1b.
And then compare that to Series 2.
So this is a stacked chart for these two items and a clustered chart for that item.
Now, this video goes back to a 2009 version of this video.
With, wow, this is one of my top 15 videos.
But things are much easier starting in Excel 2013.
So there's less steps.
Decided to update the video.
But before we go too far, my video is for, hey, we have to stack these two next to a cluster.
Leila has a completely different video on stacked and clustered.
Where for each column, like this column is 2000 and a subset of it is 500.
Check out that video using the card, the link in the top right hand corner of this video right now.
And then there's another version of clustered stack from Jon Peltier.
Where every column is a stack.
I'll put that link down in the YouTube description below.
Now, in this video, I'm going to do the set of steps twice.
If you have four quarters or six items across the bottom, you have to do the longer set of steps.
Just kind of a weird bug in Excel.
If you happen to have seven or more items across the bottom.
For example, 12 months, then skip ahead to the slightly easier 20 steps.
No matter which set of steps you use, it's an intimidating amount of steps.
Most people don't realize that YouTube has a secret Pause button.
You just have to hover over where the pause button is hidden and you can pause.
If you're working along, make liberal use of that pause button.
Also down in the description, I'm going to have both set of steps.
So the 23 steps for quarters and the 20 steps for months.
All right, let's dive in.
We have our Series 1a and 1b.
Those need to be stacked.
Series 2 is just clustered.
The first thing we have to do is insert two blank series before Series 2, and just fill those with zero.
The next thing to do, after Series 2, add two more series called Line1 and Line2.
The formula here is the max of all of the numbers.
And then this formula just copies that number down.
All right, so now we have only four columns here, or five or six items that we want to have across the bottom.
Slightly longer set of steps.
We're going to select the data.
On the Insert tab, open the column chart and choose Stacked.
See, and the problem here.
Because there are only four quarters.
They default to having the quarters in the legend.
And we need to switch that.
On Chart Design, choose Switch Row/Column.
All right, step five.
Change the chart type.
Choose Combo charts.
And I like to resize this so I can see all of the series at once.
The secondary axis column, we're going to choose Blank 1, Blank 2, the Series 2, and Line1.
We need these first two items to stack.
So as soon as I choose the first one to a stacked column chart.
It automatically changes the second one.
Blank 1 and Blank 2 are Clustered column.
Change Series 2 to a clustered column.
Now, step 11 is to make sure that Line1 and Line2 are line charts.
And in my case, they already are.
So we click okay.
Now, you might be wondering why we put in Blank 1 and Blank 2.
If you didn't have those, our third series would be covering up Series 1a and Series 1b.
You can see that if I put in fake low numbers.
And you'll see that Series 1a and 1b are there.
They're just hidden by Series 2, which is on top.
So by having Blank 1 and Blank 2, it pushes Series 2 over a bit.
Let me show you what would happen if we had some low numbers in there.
I'll just fill it with 120.
You see that's Blank 1 and Blank 2.
So having those there pushes Series 2 off to the right a little bit.
So we can see Series 1a and 1b behind.
Okay, so now we have the problem that our Series 1a and 1b stack is wider than Series 2.
I'll double-click.
Which opens over here on the right hand side the Format Data Series.
In my case, I'm lucky, it opened right here to the three column panel.
But if you don't have that, click the three columns.
It's one of these oddities in Excel.
To make that column narrower, what you have to do is increase the Gap Width.
In the old days, this was about 300%.
Now it seems to be 380%.
You can choose what looks right to you.
Like to me, that's a little bit wider.
Just adjust this up until the stack of columns is about the same width as the clustered column.
I'm going to click on the legend.
I'm going to move the legend to the top.
That's your call.
But we still need to get rid of some extra Series in the legend.
With the legend selected, when I click on Blank 1, it'll select that one item.
And then on the keyboard I press the Delete key.
See, and then for some reason it unselected the legend.
So we click on the legend again.
Click on Blank 2, and press Delete.
Click on the legend again.
Click on Line 2, press Delete.
Click on the legend again.
Click on Line 1 and press Delete.
Now, you'll notice here that the max on the left axis is 700, and the max on the right is 700.
We're getting that thanks to having Line1 and Line2 have the largest number.
One of them on the left axis and one on the right axis.
This is a great trick that I learned from Leila.
And if you didn't use the two Line series.
You'll end up with the left axis going to 400, the right axis going to 700.
And the scale will be completely wrong.
Way back in 2009, the 30-step process.
All of these were just trying to fix the scale issue.
So thanks to Leila for her awesome idea of putting Line1 and Line2 in here.
Because it's a single point, it doesn't even show up in the chart.
It's brilliant.
The last thing to do is people will wonder why you have the number zero to 700 on the left and on the right.
So click on the numbers on the right and just choose a white font to hide those.
All right, there you go.
That's the set of steps if you have four quarters or six items across the bottom.
It's slightly easier if you have 12 months.
You're still going to insert two series, called Blank 1 and Blank 2 with zeros all the way across.
And then at the bottom add two series, Line1 and Line2.
The formula here is the max of all of your numbers.
And then just another formula to bring that value down.
It'll be easier here because we don't have to switch row and column.
Select all of your data.
On the Insert tab, we want to get to All Charts.
This dialogue launcher says it's the same as pressing Recommended Charts.
Choose the All Charts tab and at the bottom choose Combo.
I like to make this taller so I can see all of the items.
We're going to choose the secondary axis for Blank 1, Blank 2, Series 2, and Line1.
We want Series 1a and 1b to be stacked.
As soon as I change the first item to a stack chart, the other item changes as well.
Blank 1 and Blank 2 are already correct at Clustered Column.
I need to change Series 2 to clustered column.
Line1 and Line2 are already correct as a line chart.
Step nine, click Okay.
We'll double click the left column.
Over here on the right hand side, click the three column charts.
It's called Series options.
And increase the gap width to about 380%.
We'll click on the legend, move that to the top.
With the legend selected, click on Blank 1 and press the Delete key.
That unselects the legend for some reason.
Click on the legend again.
Click on Blank 2 & delete.
Click on the legend again.
Click on Line2 & delete.
Click on the legend again.
Click on Line1 & delete.
You'll see that the left and right axis both go to a max of 1000.
That's thanks to Leila's great trick here of using Line1 and Line2.
It's a single point, so the line doesn't even get drawn, but it forces these axes to have the same max.
Last step here.
Choose the right axis.
Home, and a white font.
Okay, there you go.
20 steps.
Much shorter than the 30 steps back in 2009.
Thanks to the Excel team for introducing that new Combo chart.
Which makes this a little bit easier.
All right, well, hey, thanks to Jeff from Akron for sending that question in.
Thanks to Leila for the awesome tip on how to make the left and right axis have the same scale.
Thanks to the Excel team for improving things in Excel 2013 and giving us the combo chart.
And finally, thanks to you, for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to leave any questions down in the comments below.
The goal today, this is from Jeff from Akron. Jeff has two things that have to be stacked on the left hand side, so Series 1a and Series 1b.
And then compare that to Series 2.
So this is a stacked chart for these two items and a clustered chart for that item.
Now, this video goes back to a 2009 version of this video.
With, wow, this is one of my top 15 videos.
But things are much easier starting in Excel 2013.
So there's less steps.
Decided to update the video.
But before we go too far, my video is for, hey, we have to stack these two next to a cluster.
Leila has a completely different video on stacked and clustered.
Where for each column, like this column is 2000 and a subset of it is 500.
Check out that video using the card, the link in the top right hand corner of this video right now.
And then there's another version of clustered stack from Jon Peltier.
Where every column is a stack.
I'll put that link down in the YouTube description below.
Now, in this video, I'm going to do the set of steps twice.
If you have four quarters or six items across the bottom, you have to do the longer set of steps.
Just kind of a weird bug in Excel.
If you happen to have seven or more items across the bottom.
For example, 12 months, then skip ahead to the slightly easier 20 steps.
No matter which set of steps you use, it's an intimidating amount of steps.
Most people don't realize that YouTube has a secret Pause button.
You just have to hover over where the pause button is hidden and you can pause.
If you're working along, make liberal use of that pause button.
Also down in the description, I'm going to have both set of steps.
So the 23 steps for quarters and the 20 steps for months.
All right, let's dive in.
We have our Series 1a and 1b.
Those need to be stacked.
Series 2 is just clustered.
The first thing we have to do is insert two blank series before Series 2, and just fill those with zero.
The next thing to do, after Series 2, add two more series called Line1 and Line2.
The formula here is the max of all of the numbers.
And then this formula just copies that number down.
All right, so now we have only four columns here, or five or six items that we want to have across the bottom.
Slightly longer set of steps.
We're going to select the data.
On the Insert tab, open the column chart and choose Stacked.
See, and the problem here.
Because there are only four quarters.
They default to having the quarters in the legend.
And we need to switch that.
On Chart Design, choose Switch Row/Column.
All right, step five.
Change the chart type.
Choose Combo charts.
And I like to resize this so I can see all of the series at once.
The secondary axis column, we're going to choose Blank 1, Blank 2, the Series 2, and Line1.
We need these first two items to stack.
So as soon as I choose the first one to a stacked column chart.
It automatically changes the second one.
Blank 1 and Blank 2 are Clustered column.
Change Series 2 to a clustered column.
Now, step 11 is to make sure that Line1 and Line2 are line charts.
And in my case, they already are.
So we click okay.
Now, you might be wondering why we put in Blank 1 and Blank 2.
If you didn't have those, our third series would be covering up Series 1a and Series 1b.
You can see that if I put in fake low numbers.
And you'll see that Series 1a and 1b are there.
They're just hidden by Series 2, which is on top.
So by having Blank 1 and Blank 2, it pushes Series 2 over a bit.
Let me show you what would happen if we had some low numbers in there.
I'll just fill it with 120.
You see that's Blank 1 and Blank 2.
So having those there pushes Series 2 off to the right a little bit.
So we can see Series 1a and 1b behind.
Okay, so now we have the problem that our Series 1a and 1b stack is wider than Series 2.
I'll double-click.
Which opens over here on the right hand side the Format Data Series.
In my case, I'm lucky, it opened right here to the three column panel.
But if you don't have that, click the three columns.
It's one of these oddities in Excel.
To make that column narrower, what you have to do is increase the Gap Width.
In the old days, this was about 300%.
Now it seems to be 380%.
You can choose what looks right to you.
Like to me, that's a little bit wider.
Just adjust this up until the stack of columns is about the same width as the clustered column.
I'm going to click on the legend.
I'm going to move the legend to the top.
That's your call.
But we still need to get rid of some extra Series in the legend.
With the legend selected, when I click on Blank 1, it'll select that one item.
And then on the keyboard I press the Delete key.
See, and then for some reason it unselected the legend.
So we click on the legend again.
Click on Blank 2, and press Delete.
Click on the legend again.
Click on Line 2, press Delete.
Click on the legend again.
Click on Line 1 and press Delete.
Now, you'll notice here that the max on the left axis is 700, and the max on the right is 700.
We're getting that thanks to having Line1 and Line2 have the largest number.
One of them on the left axis and one on the right axis.
This is a great trick that I learned from Leila.
And if you didn't use the two Line series.
You'll end up with the left axis going to 400, the right axis going to 700.
And the scale will be completely wrong.
Way back in 2009, the 30-step process.
All of these were just trying to fix the scale issue.
So thanks to Leila for her awesome idea of putting Line1 and Line2 in here.
Because it's a single point, it doesn't even show up in the chart.
It's brilliant.
The last thing to do is people will wonder why you have the number zero to 700 on the left and on the right.
So click on the numbers on the right and just choose a white font to hide those.
All right, there you go.
That's the set of steps if you have four quarters or six items across the bottom.
It's slightly easier if you have 12 months.
You're still going to insert two series, called Blank 1 and Blank 2 with zeros all the way across.
And then at the bottom add two series, Line1 and Line2.
The formula here is the max of all of your numbers.
And then just another formula to bring that value down.
It'll be easier here because we don't have to switch row and column.
Select all of your data.
On the Insert tab, we want to get to All Charts.
This dialogue launcher says it's the same as pressing Recommended Charts.
Choose the All Charts tab and at the bottom choose Combo.
I like to make this taller so I can see all of the items.
We're going to choose the secondary axis for Blank 1, Blank 2, Series 2, and Line1.
We want Series 1a and 1b to be stacked.
As soon as I change the first item to a stack chart, the other item changes as well.
Blank 1 and Blank 2 are already correct at Clustered Column.
I need to change Series 2 to clustered column.
Line1 and Line2 are already correct as a line chart.
Step nine, click Okay.
We'll double click the left column.
Over here on the right hand side, click the three column charts.
It's called Series options.
And increase the gap width to about 380%.
We'll click on the legend, move that to the top.
With the legend selected, click on Blank 1 and press the Delete key.
That unselects the legend for some reason.
Click on the legend again.
Click on Blank 2 & delete.
Click on the legend again.
Click on Line2 & delete.
Click on the legend again.
Click on Line1 & delete.
You'll see that the left and right axis both go to a max of 1000.
That's thanks to Leila's great trick here of using Line1 and Line2.
It's a single point, so the line doesn't even get drawn, but it forces these axes to have the same max.
Last step here.
Choose the right axis.
Home, and a white font.
Okay, there you go.
20 steps.
Much shorter than the 30 steps back in 2009.
Thanks to the Excel team for introducing that new Combo chart.
Which makes this a little bit easier.
All right, well, hey, thanks to Jeff from Akron for sending that question in.
Thanks to Leila for the awesome tip on how to make the left and right axis have the same scale.
Thanks to the Excel team for improving things in Excel 2013 and giving us the combo chart.
And finally, thanks to you, for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to leave any questions down in the comments below.
