1

enter image description here

I would like to create a stacked area chart for values, in columns, through time.

  • I will have in column A, dates (quarterly) and in each subsequent column a series of profit figures for each division next to each date up to column D. Using this data I have created a stacked column chart to represent the profit values in the various columns through time.
  • Time will be on the horizontal axis and profit values on the vertical axis. At each point in time the profit of each division must be represented by an area chart and the charts should stack on top of each other and the sum of the stacks should equal the total revenue for the companies.

However one of the divisions is making losses (absorbs mostly costs) and therefore I would like the area chart for this division to be below zero and the other charts to stack up on top of this chart, and the total of all these charts should equal the total profit for this company.

However this does not seem to be happening as the negative values are being displayed above zero. Also an area chart ( not stacked) will display negative values below the line but will not stack the remaining divisions (thus if one divisions profits are lower than another you will not see this chart as it will be hidden).

Can you assist me in creating a stacked area chart which stacks positive values on top of negative ones ?

  • 1
    I tried this in Excel 2010 and it worked as you want: The negative values are below x-axis and the others stacked above. – IQV Mar 24 '17 at 10:25
  • Welcome to Super User: to post images. Go to editing click in the area you wish the image to be placed then click on the image icon above the editing box. This will open a dialog box that will yet you search for the image on your computer, click on it and it will up load the image to this site(like email) – mic84 Mar 24 '17 at 10:53
  • Hi thanks for getting back to me, I have attached an image for the problem above thanks – Paul Smith Mar 24 '17 at 11:45
  • Unfortunately I do not have access to excel 2010 and must use 2013 – Paul Smith Mar 24 '17 at 11:46
  • 1
    @PaulSmith Sorry, I didn't read your question exactly and I didn't check my try exactly. So after analyzing and playing around with some numbers I found out, that an stacked area chart doesn't work for you. When you switch to a stacked line chart you better see what happens. For your case I found out, that your column for Division C must be the first; then you get what you want, but only as line diagram. But this solution does only work for your case, not generally. Especially if another division makes losses, it doesn't work. – IQV Mar 27 '17 at 7:31
2

So finally, I got the solution. It works, but it works only for your special set of data. The clue is that you have to calculate your "stacked" values by yourself and draw a normal area chart (no stacked area chart).

Simulated stacked area chart

Column A are your dates, Column B to D your original values of the three divisions.
Column E is the sum of all three divisions =B2+C2+D2.
Column F is the difference between the negative division and the first division =B2+D2.

Now you draw a normal area chart with columns A and D to F. Then you sort the data rows so you see all areas:

  1. DivC (column D)
  2. Sum (column E)
  3. DivA+DivC (column F)

and get the "simulated stacked" area chart you want and which has the correct values. Finally rename the headings of the columns according to your needs. Note that the order of the data rows in the legend is not straight A - B - C as the different areas would be hidden. But eventually you can change the data order and the formulas to get an appropriate sequence.

  • @PaulSmith Feel free to accept the answer when it works for you. – IQV Mar 28 '17 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.