Microsoft Excel Tutorial – Creating Cluster Stack Chart in Excel.<\/p>\n

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.<\/p>\n

To download the workbook: https:\/\/www.mrexcel.com\/youtube\/c7dPCe2AIEQ\/<\/a><\/p>\n

This video requires Excel 2013 or newer. There is an older video for Excel 2007 or Excel 2010: https:\/\/youtu.be\/j8kMp5IC6oE<\/a><\/p>\n

For other variations:
\nLeila 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<\/a><\/p>\n

Jon Peltier sells a charting utility where there are quarterly clusters and every column contains a stack: bit.ly\/41QxYdM<\/p>\n

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:
\n1. Insert two blank series with zeroes after Series 1b
\n2. Insert Line1, Line2 with =MAX() in Q1
\n3. Create a Stacked Column Chart
\n4. Switch Row\/Columns so quarters along bottom
\n5. Change Chart Type
\n6. Choose Combo Chart
\n7. Resize dialog
\n8. Choose 2nd Axis for Blank1, Blank2, Series2, Line1
\n9. Change Series 1a to Stacked (which changes series 1b automatically)
\n10. Change Blank1, Blank2, Series2 to Clustered
\n11. Change Line1, Line2 to Line
\n12. OK
\n13. Double-Click the Left column (Format pane opens)
\n14. Increase Gap Width to about 388%
\n15. Click on Legend
\n16. Move Legend to Top
\n17. Click on Blank 1 in Legend to select it
\n18. Press Delete Key
\n19. Click on Legend again, Click on Blank 2, press Delete key
\n20. Click on Legend again, Click on Line1, press Delete key
\n21. Click on Legend again, Click on Line2, press Delete key
\n22. Click on Right vertical axis
\n23. With right axis selected, white font from Home tab<\/p>\n

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:
\n1. Insert two blank series with zeroes after Series 1b
\n2. Insert Line1, Line2 with =MAX() in Q1
\n3. Use Dialog launch for all chart types, click Combo
\n4. Resize dialog
\n5. Choose 2nd Axis for Blank1, Blank2, Series2, Line1
\n6. Change Series 1a to Stacked (which changes series 1b automatically)
\n7. Change Blank1, Blank2, Series2 to Clustered
\n8. Change Line1, Line2 to Line
\n9. OK
\n10. Double-Click the Left column (Format pane opens)
\n11. Increase Gap Width to about 388%
\n12. Click on Legend
\n13. Move Legend to Top
\n14. Click on Blank 1 in Legend to select it
\n15. Press Delete Key
\n16. Click on Legend again, Click on Blank 2, press Delete key
\n17. Click on Legend again, Click on Line1, press Delete key
\n18. Click on Legend again, Click on Line2, press Delete key
\n19. Click on Right vertical axis
\n20. With right axis selected, white font from Home tab<\/p>\n

Table of Contents
\n(0:00) Chart with 2 Stacked & 1 Clustered Series
\n(0:25) Use other video for Excel 2007-2010
\n(0:39) Leila version: Series 1 is Total, Series 2 is a subset in foreground
\n(1:01) Jon Peltier Add-In: every column is a stack
\n(1:11) Deciding which steps
\n(1:30) Pause button in YouTube
\n(1:56) Steps to Create Chart with Less than 7 Items
\n(3:38) Why Blank1 & Blank2
\n(5:20) Deleting specific Excel chart legend entries
\n(5:50) Keep left & right axis in sync with 2 invisible line chart series
\n(6:09) What would happen without Line1 and Line2
\n(6:57) Steps to Create Chart with More than 6 Items
\n(9:15) Wrap-up<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":3,"featured_media":5613,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[21,142,138],"tags":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/bonbonsguide.com\/wp\/wp-content\/uploads\/2023\/04\/c7dPCe2AIEQ.jpg?fit=1280%2C720&ssl=1","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7QB1z-1sw","_links":{"self":[{"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/posts\/5612"}],"collection":[{"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/comments?post=5612"}],"version-history":[{"count":0,"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/posts\/5612\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/media\/5613"}],"wp:attachment":[{"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/media?parent=5612"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/categories?post=5612"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bonbonsguide.com\/wp\/wp-json\/wp\/v2\/tags?post=5612"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}