• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a Combined Cluster-Stacked Chart (with multiple series) in Excel

Varna

New Member
Hi,

I'm trying to see if it is possible to create a stacked-cluster chart with multiple series in excel. From what I have looked up, doing this natively isn't possible and the closest I have got is to use a stacked area chart. My data is in the attached file (example data) and 1 or two possible solutions are also in it. I've also uploaded the type of chart I'm trying to create in sheet 1 in the workbook attached. The visually closest solution is 'option 3', but extremely tedious, is there a more efficient way to do this or to automate it using VBA/Macro?

Thank you!
 

Attachments

  • Chart.xlsx
    48.1 KB · Views: 20
Hi Varna, and welcome to the forum. :awesome:

You can create most of your chart using just an Area type chart. First trick is to have the x-axis be date format, and then use 0/blank to have the area fall straight down. We can then add an extra series to put the labels.
upload_2016-5-12_9-41-5.png


IMO, I would simplify the whole chart though. Since you only have two groups, you can get all the information into a small area, like so:
upload_2016-5-12_9-40-1.png

The attached shows both charts, and their associated data layout.
 

Attachments

  • Chart LM.xlsx
    53.7 KB · Views: 21
Hi Varna, and welcome to the forum. :awesome:

You can create most of your chart using just an Area type chart. First trick is to have the x-axis be date format, and then use 0/blank to have the area fall straight down. We can then add an extra series to put the labels.
View attachment 30901


IMO, I would simplify the whole chart though. Since you only have two groups, you can get all the information into a small area, like so:
View attachment 30899

The attached shows both charts, and their associated data layout.

Thank you for the warm welcome! I agree with you about the simplification. And thank you for showing me how to make the same chart in excel, that is some really clever formatting.
 
Varna
No need 'chart'.
Check this too ...
Which values would You like to see around bars?

This is very cool. Wow. :) The values around the bars are years - 1999 on the left and 2005 on the right. The way I would read the chart would be to see a trend from left to right i.e. the change in percentage of poor or rich from 1999 to 2005.
 
Years are Okay.
Would You show what kind of 'a trend' You would like to see.
How would You like to calculate it?
You could add Your need over that sample.
 

Attachments

  • Chart2.xlsb
    59.5 KB · Views: 8
Years are Okay.
Would You show what kind of 'a trend' You would like to see.
How would You like to calculate it?
You could add Your need over that sample.

Thank you. The information i.e. trend I would like to be able to see is how the percentage (proportion of poor & rich) changes from each year to the next (i.e. 199 to 2005), within each block of Class 1, Class 2 and Class 3. Perhaps a trend-line or a line graph (using some kind of combination chart) might work, i.e. the percentages for the later year minus the percentages for the earlier year would illustrate the difference. In the original chart that I am trying to replicate the years for each block are clubbed into one column and the diagonal divider illustrates the trend for that block.
 
Back
Top