Shading areas between xy chart

[majana54]

hi all. need help please. can someone please help how to put colors in between the spaces between date1 and date2 and date3 please? Thanks!

[sweep]

Hi,

Jon Peltier has it covered on his website: http://peltiertech.com/fill-under-be...n-excel-chart/

[majana54]

thanks for the response. i've read the article (for hours now actually). problem for me is the example given used values and not dates.

[MrShorty]

I'm not sure that dates are causing the problem. Dates are just numbers to Excel, and should not interfere with creating a combination scatter + stacked area chart like that described in Peltier's tutorial. The problem I see with your data is that it is more vertically oriented than horizontally oriented, and your "fill" areas are more about filling left/right than up/down.

How well did you understand Peltier's tutorial? How far through the comments did you get? I note that your problem looks a lot like Alicea's comment/question on Apr. 3 2014 (about midway through the comments) where he/she is trying to fill horizontally rather than vertically. Jon's response is to use the same basic "stacked area" technique, expanded to include multiple stacked areas to account for the left/right variations of the vertically oriented data series. He suggests that it could be a bit tedious to figure out all of the values you need to work through the multiple stacked areas, but it should be the same basic technique.

It might be easier to use a variation that uses a stacked bar (not stacked column) instead of stacked area. The technique would be basically the same (create a stacked bar with a date axis as the vertical axis and the date values for the stacked bars). The main problem with using a stacked bar is that you will get inevitable "pixelation" from the square tops of the stacked bars. You should be able to minimize the pixelation by using a very fine date axis grid, then hiding the remaining pixelation under a suitably "fat" line of your scatter chart series.

At this point, what approach would you like to use? What part of creating a scatter + stacked area or scatter + stacked bar do you have difficulty with?

[majana54]

MrShorty thanks for the time explaining my options. i made a simpler sample data which i have attached. my only problem now is how to "flip" the Delta Area (in yellow) so it will fit between Q1 and DE. any help is greatly appreciated.

[MrShorty]

my only problem now is how to "flip" the Delta Area (in yellow) so it will fit between Q1 and DE.

One does not simply "flip" a stacked area chart on its side. The idea is that you need to "build" multiple "stacks" on top of each other until you fill the desired space. I have started this process (manually entering x and y values into column E and on) to show that you will need additional stacks. This will continue through another blank stack (to "fill" the blank space between the 2nd and 3rd red segments), another fill stack (to fill between the 3rd green and 3rd red segment), and so on through the rest of the chart. Not difficult, but certainly tedious. Through that process, figure out the formulas you need to automate the process of choosing/computing values (both horizontal and vertical) for each stacked area series, and do that for as many stacked area series as you need to completely fill between the two series.

I also included the beginning of the "stacked bar" approach. You can see that the stacked bar readily locates the "fill" series for the given data points. It would need the stacked bar table filled out with many more entries to provide the "fill" for the two stacked bar series.

[majana54]

Hi MrShorty,

You've explain what I need to do to color all the spaces in my chart but my brain just can't seem to understand how area chart works. I have attached the exact file i am currently working on. I tried adding another stack but I am having difficulty adjusting the area charts. PLEASE, can you show me what / how i can do this please?

[MrShorty]

but my brain just can't seem to understand how area chart works.

I think I should apologize for this, but, after thinking about how to respond all day, I think that, if you brain just cannot understand how an area chart works, then maybe the area chart approach is just not going to work for you. I'm sure I could get it to work, but I find myself without the time nor motivation to work through the tedium of putting it together for you. With 70+ data points, it could potentially need 200 or so columns/data series/"stacks" in the stacked area chart (The simplified example I started in post #6 has 4 columns/data series/stacks with four more needed to adequately fill between those two xy series with 5 points each).

Is there something specific about my example that you are having trouble understanding or seeing how it adapts to your real data set? Perhaps if you can pinpoint the part/concept that you don't understand, it will help narrow down the discussion.

If you are bound and determined to do it this way, I would suggest you spend some time with stacked area charts until you understand how stacked are charts work so you can apply

Again, I apologize for my lack of willingness. It just seems to me that, if you are really having trouble understanding this at this point, that it is probably more work than it is worth to do it with a stacked area chart.

Looking at other options, did you look at the stacked bar option I suggested? Did you understand how it works? It seems to me that it would be easier to figure out how to fill in a thousand data points in a stacked bar chart than it is to figure out dozens or scores of data series/stacks in a stacked area chart.

Another option -- though I suspect it is an unwanted option. Is it absolutely necessary to have date on the horizontal axis and facility number on the vertical axis? In post #5 you suggested that you simply needed to flip the stacked area on its side, which is difficult to do. Would it be allowable to "flip" the XY scatter data series (which should be quite easy to do)? If you could put facility number along the horizontal axis and date on the vertical axis, the simple stacked area option Peltier's tutorial describes would readily apply to that chart.

[majana54]

Thanks for the explanation. Was able to figure it out. Guess all i needed was a decent sleep to clear my head. Thank you!

[MrShorty]

Glad to see you got it worked out.