1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Discuss Data Visualizations and Charting' started by Varna, May 12, 2016.

  1. Varna

    Varna New Member

    Messages:
    7
    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!

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,351
    Arna

    Firstly, Welcome to the Chandoo.org Forums

    upload_2016-5-12_21-34-23.png
    If not can you hand draw the chart ?
    Thomas Kuriakose and Varna like this.
  3. Luke M

    Luke M Excel Ninja

    Messages:
    9,343
    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.

    Attached Files:

  4. vletm

    vletm Excel Ninja

    Messages:
    3,513
    Varna
    No need 'chart'.
    Check this too ...
    Which values would You like to see around bars?

    Attached Files:

    Dillina, Thomas Kuriakose and Varna like this.
  5. Varna

    Varna New Member

    Messages:
    7
    Thank you for the warm welcome. I came up with something similar too and yes that is a much cleaner way to display a complex table! I have a lot to learn, thank you. :)
  6. Varna

    Varna New Member

    Messages:
    7
    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.
  7. Varna

    Varna New Member

    Messages:
    7
    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.
  8. vletm

    vletm Excel Ninja

    Messages:
    3,513
    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.

    Attached Files:

  9. Varna

    Varna New Member

    Messages:
    7
    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.
  10. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,351
    Doesn't Lukes last chart above do that ?
  11. Varna

    Varna New Member

    Messages:
    7
    Hi Hui,

    Actually it does, magnificently. I was referring to the VBA/Automated chart when I said that. Both versions are very helpful.
  12. vletm

    vletm Excel Ninja

    Messages:
    3,513
    Varna
    + Rich-values
    + visual information between Classes
    + possible to change 'Rich' & 'Poor' -colors

    Attached Files:

    Dillina, Varna and Thomas Kuriakose like this.
  13. Varna

    Varna New Member

    Messages:
    7
    This is awesome. Thank you so much. I love how this allows for customisation and extended information. Much appreciated.
  14. pannch

    pannch New Member

    Messages:
    1
    actually i would like to create this kind of chart. Could you please tell me how?

Share This Page