+ Reply to Thread
Results 1 to 5 of 5

Stacked Column Chart with independent time series

  1. #1
    Forum Contributor
    Join Date
    04-05-2011
    Location
    behind you!
    MS-Off Ver
    Excel 2010
    Posts
    116

    Stacked Column Chart with independent time series

    Hi,

    Please see the attached Excel. I like to build a Stacked Column Chart from the range A1:H8.
    Cells A1; C1; E1; G1 should be the labels.

    The result should look like the draft in range J2:P23.

    Is it possible to realize this in Excel (cell formula / no VBA)?

    Thanks a lot!

    Llyod
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,067

    Re: Stacked Column Chart with independent time series

    Kludgy at best. I made 4 separate charts, gave them invisible backgrounds. Saved the vertical axis on the first chart added at horizontal axis label to each chart. Gave it a 500 gap. dragged the charts on top of one another. Put a white rectangle behind all. I didn't quite match up the lines. I had the thought of adding data labels only on the last chart.

    There is still some work to be done for cleanup. Yes, it can be done but poorly.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    04-05-2011
    Location
    behind you!
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: Stacked Column Chart with independent time series

    Dear dflak,

    Thank you so much for that effort! That helps for the moment. As you said, this must be cleaned up a bit, but the template is super useful for me.
    Finally I'll use VBA to add the data labels (names, values) automatically, as doing this manually is pain for the overlapped charts.

    THANKS A LOT!


    Best,
    Lloyd

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,067

    Re: Stacked Column Chart with independent time series

    If you are going to use VBA, then maybe there is a way to "munge" the data so that it plots automatically. Let me give that a thought. Also I'll check my archives, I might have a data labeling macro there.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,067

    Re: Stacked Column Chart with independent time series

    Well, this has been a fun project.

    First of all, if you arrange the data "horizontally" as in rows 7:10 on Sheet2, then you can get the groups as stacked columns all on one chart.

    Then after a bunch of macro recording I came up with a way to assign the data labels.

    Is this what you want? The only thing to do from here is a short VBA to read the data in the format that you have it in sheet 1 and put it into the format in sheet 2.

    Can I assume that you will always have 4 groups and a maximum of 7 items in a group?
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Stacked chart: How to get series 2 into series 1
    By davelarue in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-01-2015, 05:52 PM
  2. Replies: 2
    Last Post: 12-16-2014, 12:47 PM
  3. How to set the distance between chart series in stacked column chart
    By Jan Zitniak in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 04-23-2013, 03:58 AM
  4. Stacked column chart with one series as lines
    By marpop78 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-23-2012, 02:39 PM
  5. Replies: 4
    Last Post: 04-13-2011, 11:51 AM
  6. Combine stacked column and line chart w/series lines
    By ancillaries in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-07-2006, 06:55 PM
  7. How do I set up a stacked chart with different series for each bar
    By Virent in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-10-2005, 12:55 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1