👉 NEW: Up4Excel Downloads Library: »» ml.up4excel.com/library ✅ FREE Access to ALL Up4Excel Files. Includes Excel Templates, Training Workbooks, Example Data, Cheat Sheets and more. New Content Added Weekly!
I’ll step you through producing a bespoke clustered stacked column chart in Excel. The key to this technique is to produce 2 clustered column charts and then overlay them on top of each other using a dual axis chart. Then we're going to adjust it to make sure that the axes are in line and that everything falls into place.
Glad you liked the video. The issue with this technique is it is limited to 2 stacks because it uses a dual axis to achieve the result, and you can't have more than 2 axis. I have an idea though to achieve 4 like you ask... essentially you could put another chart on top of it, perfectly aligned, with no background or labels etc, giving the illusion of 4 stacks. You would have to calculate the values based on the chart underneath so not especially straight forward, but achievable. Unfortunately I won't be making this video though but if you crack it and make your own video I'll link to it to give you a boost 👍 John
Very good explanation. How do you change the height of the stacks (diff colors) containing the data series values? Sometimes for very small values excel makes the height of the cell too small to fit the data label font. Ex: qtr1 2018 (5th stack) - the middle stack at very bottom might be too small to add a data label.
IT's a difficult one as you need to keep everything in proportion. A few ideas are to shrink the font size, scale everything on the chart up, or start the y axis at a negative number. That last tip is a great hack but pretty advanced. It would require setting up negative series in parallel to your existing data and plotting with the same colour as the bars above them.....worth a go if it is important to you though and gives me an idea for another video sometime 👍
Can you stack three data points one on top of another. So instead of three columns, I want two columns stacked three high. Can you do a demo of that if it is possible in Excel.
I would love to know how to do that in Excel. It is not possible with the technique in this video as it makes use of a 2 axis chart, and you can't have a 3 axis chart in Excel. There may be another technique....perhaps using transparent charts on top of each other and dependent on each other but I've never tried so can't teach it yet. If you find a method please let me know as I would love to learn myself 👍
Just to double-check, if you want to stacked/clustered combo column chart with 3 parts to each bar and 3 clusters (9 data series total), you can't use this method, right? This method only works if your stacking has 2 parts?
You are completely correct, this method only allows for 2 stacks on each bar. 3 stacks per bar with this type of method would require 3 axis that Excel doesn't currently allow.
Hi great video thanks! However, I got one problem. I need to show the data labels. Since the data series for one of the stacked portion is the sum of 2 series, then the data label will show the total of the 2 instead of its own data
Actually I think you had a good point to start with which got me thinking. As a general tip, you can create data labels of almost any value using hidden chart series. Use a set of stacked bars, both invisible, with the top one equal to the data label value you wish to show....then simply show the label but not the bars. This sort of technique can be used to show values at any height within a column too.
Thanks for your comment Sajede. That's quite a detailed question so you'll need to do some research on Google. As a pointer, you might want to use formulas on a worksheet to create the chart.
👉 NEW: Up4Excel Downloads Library: »» ml.up4excel.com/library
✅ FREE Access to ALL Up4Excel Files. Includes Excel Templates, Training Workbooks, Example Data, Cheat Sheets and more. New Content Added Weekly!
YOU MY GUY!!! Give this man a raise!!!
Excellent idea...I'll pass on your suggestion to RUclips 👍
I’ll step you through producing a bespoke clustered stacked column chart in Excel. The key to this technique is to produce 2 clustered column charts and then overlay them on top of each other using a dual axis chart. Then we're going to adjust it to make sure that the axes are in line and that everything falls into place.
Awesome👍 but what if i have 4 rows each for per product and delivery.... Please can you make a video by adding more 2 rows and show us... Thank you
Glad you liked the video. The issue with this technique is it is limited to 2 stacks because it uses a dual axis to achieve the result, and you can't have more than 2 axis. I have an idea though to achieve 4 like you ask... essentially you could put another chart on top of it, perfectly aligned, with no background or labels etc, giving the illusion of 4 stacks. You would have to calculate the values based on the chart underneath so not especially straight forward, but achievable. Unfortunately I won't be making this video though but if you crack it and make your own video I'll link to it to give you a boost 👍 John
My best wishes to you! All the best!
Thanks for your support, always great to hear from people!
Great John!!!
👍
Very good explanation. How do you change the height of the stacks (diff colors) containing the data series values? Sometimes for very small values excel makes the height of the cell too small to fit the data label font. Ex: qtr1 2018 (5th stack) - the middle stack at very bottom might be too small to add a data label.
IT's a difficult one as you need to keep everything in proportion. A few ideas are to shrink the font size, scale everything on the chart up, or start the y axis at a negative number. That last tip is a great hack but pretty advanced. It would require setting up negative series in parallel to your existing data and plotting with the same colour as the bars above them.....worth a go if it is important to you though and gives me an idea for another video sometime 👍
Can you stack three data points one on top of another. So instead of three columns, I want two columns stacked three high. Can you do a demo of that if it is possible in Excel.
I would love to know how to do that in Excel. It is not possible with the technique in this video as it makes use of a 2 axis chart, and you can't have a 3 axis chart in Excel. There may be another technique....perhaps using transparent charts on top of each other and dependent on each other but I've never tried so can't teach it yet. If you find a method please let me know as I would love to learn myself 👍
Very useful, thank you so much
Glad it was helpful Aelus 👍
great ..
thank you..
You're most welcome 👍
Just to double-check, if you want to stacked/clustered combo column chart with 3 parts to each bar and 3 clusters (9 data series total), you can't use this method, right? This method only works if your stacking has 2 parts?
You are completely correct, this method only allows for 2 stacks on each bar. 3 stacks per bar with this type of method would require 3 axis that Excel doesn't currently allow.
thank you for asking this..... this was what I was looking for as well
Hi great video thanks! However, I got one problem. I need to show the data labels. Since the data series for one of the stacked portion is the sum of 2 series, then the data label will show the total of the 2 instead of its own data
ok i take back. I can do Data Label Value from Cells ..:)
Actually I think you had a good point to start with which got me thinking. As a general tip, you can create data labels of almost any value using hidden chart series. Use a set of stacked bars, both invisible, with the top one equal to the data label value you wish to show....then simply show the label but not the bars. This sort of technique can be used to show values at any height within a column too.
Hi, I have a question: How can I create a dynamic pyramid chart that is horizontally and vertically segmented? In excel or power BI. Thanks a million.
Thanks for your comment Sajede. That's quite a detailed question so you'll need to do some research on Google. As a pointer, you might want to use formulas on a worksheet to create the chart.
How can you add a data point to each column to add a line ?
I'm fairly sure you could just add a new data series and change the chart type of just that series to a line (i.e. combo chart option).
Is it possible to make a "single" bar and 2 stacked bars clustered? So we have 2 columns for each quarters, 1 of the columns is stackedwith 2 bars.
It sounds possible by making one of the columns zero value, so when it stacks it is invisible. Give that a try maybe.