Results 1 to 7 of 7
Like Tree1Likes
  • 1 Post By AlphaFrog

Combining a stacked column chart with a clustered chart for historical data

This is a discussion on Combining a stacked column chart with a clustered chart for historical data within the Excel Questions forums, part of the Question Forums category; Hi I have the following data, and would like to present it in a column chart where the machines (770, ...

  1. #1
    New Member
    Join Date
    Jan 2015
    Posts
    3

    Default Combining a stacked column chart with a clustered chart for historical data

    Hi

    I have the following data, and would like to present it in a column chart where the machines (770, X1, Kon, etc) are shown side by side, in each month, but the colour and mono data, for each machine, is stacked.

    Is there any way to do this?

    Thanks

    Machine Print Type Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14
    770 colour 183,976 175,089 208,163 226,253 130,930 84,564
    mono 12,516 16,558 16,746 18,428 14,155
    X1 colour 120,957 112,856 106,276 112,776 178,934 171,000
    mono 0 0 0 0 0
    Kon colour 456,892 163,423 500,056 506,787 0 0
    mono 1,417 39,338 11,132 0 0
    Can colour 882 43,847 269,171 207,700
    mono 198 155 13,647 51,384
    2100 colour 207,227
    mono 12,087

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,388

    Default Re: Combining a stacked column chart with a clustered chart for historical data

    It can be done but there are several steps. It requires your data to be rearranged.

    Do a web search for something like Excel mixed clustered stacked column chart. You will find several step-by-step guides, videos, tutorials on how to do it.

    Here's a good one.
    Stacked Cluster Chart
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    New Member
    Join Date
    Jan 2015
    Posts
    3

    Default Re: Combining a stacked column chart with a clustered chart for historical data

    This doesn't work. I tried it out before I posted the question because as soon as I spread the data over two rows, it fails to plot on the chart, although it shows in the legend and the range is correctly selected. Very confused!

    770 col 770 mon Kon col Kon mon
    Apr-14 206,158 11,717
    222,216 8,284
    May-14 219,656 3,339
    172,673 25,900
    Jun-14 136,992 43,031
    456,892 278,731
    Jul-14 183,976 15,232
    163,423 1,417
    Aug-14 175,089 12,516
    500,056 39,338
    Sep-14 208,163 16,558
    506,787 11,132
    Oct-14 226,253 16,746
    0 0
    Nov-14 130,930 18,428
    0 0
    Dec-14 84,564 14,155
    0 0

  4. #4
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,388

    Default Re: Combining a stacked column chart with a clustered chart for historical data

    Your Dates as the X-axis set the default X-axis scale to sequential date-time. The X-axis should be set to scale as Category for this to work. Perhaps the tutorial should express that as a caveat.

    After creating the stacked chart, right-click on it and select Chart Options. Then on the Axis tab, change the X axis from Automatic to Category.

    Then follow the other steps in the tutorial about formatting the gaps and spacing on the chart.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  5. #5
    New Member
    Join Date
    Jan 2015
    Posts
    3

    Default Re: Combining a stacked column chart with a clustered chart for historical data

    You little beauty!!!! Thanks so much :D

  6. #6
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,388

    Default Re: Combining a stacked column chart with a clustered chart for historical data

    You're welcome.
    jodava likes this.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  7. #7
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,365

    Default Re: Combining a stacked column chart with a clustered chart for historical data

    If you want a more rigorous treatment of clustered/stacked charts, check out my tutorial, Clustered and Stacked Column and Bar Charts.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com