Stacked Area Chart problem - filling above line?

**dannytoone** · 02-02-2011, 10:54 AM

I normally find excel help just by googling my problem, but for some reason it seems nobody else has this problem.

I have a stacked area chart using dynamic named ranges that is currently giving me problems. When I first plot the chart, the the fill is below the line, but at the end of the range, the line connects back to the beginning of the range, messing up my fill area. (see first image)

In my effort to fix the problem, I toggled one of the data series to use a secondary axis, and all of a sudden it is filling in the area between the line and the top of the chart. (see second image)

But if I switch back so both lines are on the primary axis, this line still fills in above the line all the way to the top of the chart. (see third image)

I have tried redoing the chart, and the same things happen every time. I just need the chart to display like a normal stacked area chart.

What am I doing wrong? Is it how I have selected my data?

In case you need it, see the fourth image to see what my data looks like.

**Andy Pope** · 02-02-2011, 12:15 PM

Post an actual workbook with charts rather than images.

And maybe explain what you think the chart should look like.

What line are you actually referring to when you say 'below the line'?

**dannytoone** · 02-02-2011, 03:46 PM

thanks for the help. I was initially reluctant to post the workbook because it contains some proprietary data, but I created a copy with fake data so you can see it.

I would like the stacked chart to look like this.

The line I am referring to is the plot line. Normally a stacked area chart uses a color fill below the plot line all the way down to the y intercept. In my case, when I first create the chart, it fills in the area below the plot line down to some random unwanted line that extends from the far right of the plot area down to the far left bottom the plot area.

After fiddling with the plot series axis, it is placing the color fill above the plot line all the way to the top of the chart.

**Andy Pope** · 02-03-2011, 05:24 AM

The #N/A when used as a Date for the X axis is causing it to be treated as zero and the chart connects the last point back to the origin, giving the appearance of the floating stack.

You can either setup named ranges for the chart data series so the Category labels only use valid dates.
Or revised the formula in column O to

O3: =IFERROR(INDEX(SelectedDays,ROW()-ROW($O$2),1),INDEX(SelectedDays,ROWS(SelectedDays),1))

copy down.