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: https://www.mrexcel.com/youtube/c7dPCe2AIEQ/
This video requires Excel 2013 or newer. There is an older video for Excel 2007 or Excel 2010: https://youtu.be/j8kMp5IC6oE
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: https://youtu.be/IwWh3UOTymE
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