+ Reply to Thread
Results 1 to 9 of 9

Stacked Area Chart - Negative Values

  1. #1
    Registered User
    Join Date
    10-22-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    5

    Stacked Area Chart - Negative Values

    Hi,

    I have a stacked area chart (Chart 2) which I have generated in Excel, please see the attached image illustrating this.

    The problem is as follows:

    Chart 1 and Chart 2 use the same data, however Chart 2 in done so in Excel.

    How can I preserve the negative values in the stacked area chart in Excel? Essentially I want Chart 2 to replicate Chart 1.

    I am happy to use a macro if need be.

    Many thanks in advance.

    Sagar
    Attached Images Attached Images
    Last edited by sagaramlani; 11-10-2010 at 07:07 AM.

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

    Re: Stacked Area Chart - Negative Values

    Use a normal area chart and calculate new stacked values.
    You may need to alter the order of the series so the final stacked is at the bottom of the plot order.

    If you need more help post a workbook rather than images.
    Cheers
    Andy
    www.andypope.info

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

    Re: Stacked Area Chart - Negative Values

    Hello,

    It is not quite clear to me which of your data series has the negative value. If you want to show it below the x axis, you may need to create a third data series that lists just that one negative data point.

    This would be easier to solve if you could post some sample data.

    cheers,

  4. #4
    Registered User
    Join Date
    10-22-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Stacked Area Chart - Negative Values

    Hi,

    Thank you for your prompt responses.

    Please see the attached Excel workbook.

    As you can see 'chart 1' contains negative values which lies below the x axis, however in the excel document this seems to be combined with the positive values and stacked together.

    I would like to be able to display the negative parts in the excel chart, as shown in the image.

    Many thanks in advance.

    Sagar
    Attached Files Attached Files
    Last edited by sagaramlani; 11-11-2010 at 05:25 AM. Reason: removed in-lin attach tags to reveal file size

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

    Re: Stacked Area Chart - Negative Values

    As Andy has suggested: re-arrange the order of the series.

    see attached

    cheers,
    Attached Files Attached Files

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

    Re: Stacked Area Chart - Negative Values

    Changing the series order is probably the most correct, although it's impossible to see that the 'Series 3' data is responsible for the negative drop.

    If you more series 3 to the secondary axis and delete the secondary value axis the negative spike will appear but this will then distort the rest of the data in the chart.

    Perhaps a stacked column is more suited.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-31-2011
    Location
    Gainesville, FL
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Stacked Area Chart - Negative Values

    I had same problem. I really want to have area instead of bars. I found a compromise, if you do stacked bars and then go to settings "format plot area", you can select gap width of 0. Now it looks almost as if area graph except jumps have sharp corners and looks uglier.

  8. #8
    Registered User
    Join Date
    09-05-2012
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Stacked Area Chart - Negative Values

    My problem is similar, but not the same.

    I'm trying to do a stacked area chart, where the negative values affect the sum (so the high point includes the negative values), but that they aappear below the x axis.

    So, for example, in the file 752684.xlsx, posted earlier, in row 23, the sum of series 1 through 4 is 354%, whereas the graph shows the high point of 520% (the sum of series 2 and 4, excluding the negative number in series 3). I need the high point to be the sum of all the series, not just the positive ones, with the negative ones below the x axis.

    Any ideas?

    Thanks.

    Phil

  9. #9
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Stacked Area Chart - Negative Values

    plr,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ 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