+ Reply to Thread
Results 1 to 3 of 3

Stacked Column chart that groups dates by month

  1. #1
    Registered User
    Join Date
    07-03-2011
    Location
    中国
    MS-Off Ver
    Excel 2003
    Posts
    1

    Stacked Column chart that groups dates by month

    I have the following list of data and am having trouble arranging it in a stacked column chart.
    Column A = Completed date
    Column B = Project name (just a letter)
    Column C = Work units (the variable data).

    --------------------------
    Completed Project Work units
    15/12/2010 A 30
    20/12/2010 B 67
    23/12/2010 C 92
    14/02/2011 D 108
    16/02/2011 E 50
    18/02/2011 F 45
    18/02/2011 G 29
    4/04/2011 H 38
    6/04/2011 I 8
    6/05/2011 J 256
    K 444
    22/06/2011 L 242
    M 21
    N 16
    O 29
    P 146
    Q 175
    R 191
    ------------------------------

    Chart specifications:
    Y axis = "Work units" data.
    X axis = time measured in months (one column per month).
    Layers of each column = the various projects completed in that month.

    Chart should not plot records with no "completed" date.
    Chart should display months where no projects were completed.

    Please let me know if the solution requires my data to be sorted in any particular way (I would ideally like a solution which doesn't require any sorting, but am open to anything).

    So far I've been fluffing round with pivottables to try and arrange the data before charting... but I suck at pivottables so am getting nowhere. Not even sure if that's a necessary step.

    Thanks for any help!

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Stacked Column chart that groups dates by month

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!

  3. #3
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: Stacked Column chart that groups dates by month

    See attached, hopefully this is along the lines of what you were looking for?

    The main problem is that excel has no way of knowing you want to group by month, so I've added a column to your data which automatically populates the month when a completed date is added (although you would need to remember to pull that formula down as and when new projects are added).

    I've also had to add some dummy rows to your data (rows 2:14), this prevents months when no work was completed from not appearing on the graph. Try deleting them and refreshing the pivot to see what I mean.
    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)

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