+ Reply to Thread
Results 1 to 13 of 13

Adding More Data to a Stacked Chart

  1. #1
    Forum Contributor
    Join Date
    06-10-2005
    MS-Off Ver
    2007
    Posts
    223

    Adding More Data to a Stacked Chart

    Hi,
    I have monthly sales data for 5 guys in a stacked column chart. Next to each month's sales data, I'd love to show the amount each person is over/under budget, along with the number of new clients. I've used text boxes to overlay this type of "descriptive" data in the past but it gets crazy and i keep having to make adjustments when the chart data shifts to add in each new month.
    Any help would be appreciated.
    --Phillycheese
    Attached Files Attached Files
    Last edited by Phillycheese5; 06-27-2011 at 11:56 AM. Reason: Adding Attachment

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Adding More Data to a Stacked Chart

    Post example of what you currently have set up
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Contributor
    Join Date
    06-10-2005
    MS-Off Ver
    2007
    Posts
    223

    Re: Adding More Data to a Stacked Chart

    Okay, I've uploaded the file. Note that I've only included my text box floating data for a couple of the January data points. Generally, I would need to do that for each month for all sales guys.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Adding More Data to a Stacked Chart

    Because of your choice of 3d formatting you options are very limited.

    If you used a 2d formatted chart you could use additional series to display datalabels exactly where you need them and have them update dynamically.

  5. #5
    Forum Contributor
    Join Date
    06-10-2005
    MS-Off Ver
    2007
    Posts
    223

    Re: Adding More Data to a Stacked Chart

    Andy,
    I can change to 2-D, no problem. Would you kindly give me some guidance as to how to get additional data to display as I have in the sample? Having them update dynamically as you mentioned would be great! Please let me know.
    Thanks,
    Phillycheese

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Adding More Data to a Stacked Chart

    I have added an additional line series for each person.
    The lines are plotted on the secondary axis and the secondary axis has ticks on marks with centered data labels to position to the right of stacks.

    If you want the 2 numbers to be different colours then simplest this is to add another set of lines and position labels above and below.

    Use this free addin to link data labels to cells.
    http://www.appspro.com/Utilities/ChartLabeler.htm

    You will need to format the lines and axis to it is not shown and delete the additional series from the legend
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    06-10-2005
    MS-Off Ver
    2007
    Posts
    223

    Re: Adding More Data to a Stacked Chart

    Andy,
    This looks great. I will check it out further to make sure I don't get stuck.
    Thank you,
    Phillycheese

  8. #8
    Forum Contributor
    Join Date
    06-10-2005
    MS-Off Ver
    2007
    Posts
    223

    Re: Adding More Data to a Stacked Chart

    Andy,
    I'm almost there but not exactly. I see how you plotted the secondary axis to put in the line series so it positions the data labels in the middle of the stacks. However, I can't figure out how to "link" the descriptive data labels to the line graph as you did. I deleted the line labels for "Pete" but then when I try to use the add-in to put them back, the data label "6 1" is overwriting the "344" in the stacked data series.
    Any idea on what step I'm missing?
    I appreciate your patience.
    Phillycheese

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Adding More Data to a Stacked Chart

    Did you download the free addin I mentioned?

    Otherwise you can do the task manually
    http://www.andypope.info/tips/tip001.htm

  10. #10
    Forum Contributor
    Join Date
    06-10-2005
    MS-Off Ver
    2007
    Posts
    223

    Re: Adding More Data to a Stacked Chart

    Andy,
    I was using the add-in, but I can't seem to add the series of labels to a particular line. I can do it the manual way and do it one by one (as in your example with the chart title), but that's not too efficient.

    When I right click on the data labels on the secondary axis and click on "format data labels" I see under the "number" menu that there is a checkbox indicating the data is linked to the source. That is where I'm getting stuck. I've tried to use the add-in but as I mentioned, it overwrites the labels in the stacks (primary axis) with the labels I want to add in for the lines (secondary axis).

    Once you created the secondary axis, what steps did you take to attach the series of descriptive labels?

    Thanks,
    Phillycheese

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Adding More Data to a Stacked Chart

    Sounds like you are selecting the wrong series to apply data labels too.

    Don't forget you have 2 series with each persons name, so 2 Petes.
    You need to select the 2nd occurance of a name.

  12. #12
    Forum Contributor
    Join Date
    06-10-2005
    MS-Off Ver
    2007
    Posts
    223

    Re: Adding More Data to a Stacked Chart

    Andy,
    It seems I've slid back a few steps when trying to re-create what you produced in the sample file. In my actual file, I have the stacked chart with no problem. However, I'm having difficulty adding the second series and getting it to line up correctly. For some reason the lines start in the middle of the second stack and go all the way past the YTD stack.
    I've looked at some online examples of charts with columns/lines, but they typically select all of the data at once and then change it while it's on the graph. In this situation, I have an existing stacked chart and I'm trying to add in a separate series of data. I've played around with different ways (even trying to put all the data in one area and select it) but I cannot replicate what you've done.
    I know it's a bit of spoon-feeding, but I would really appreciate it if you could give me a step-by-step on how you did it.
    Much appreciated,
    Phillycheese

  13. #13
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Adding More Data to a Stacked Chart

    Select B3:O9 and insert stacked column chart
    Use Select Data dialog to add 6 new series, Values for 1st addition would be DATA!$B$30:$O$30. Repeat for rows 31:35
    Move additional series to secondary axis
    Change their chart type to line
    Apply secondary horizontal axis
    Format axis to cross on Tick marks
    Delete secondary vertical axis
    Apply data labels to lines and then link data lables to range B37:O37 etc.

+ 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