Search code examples
excelchartsexcel-formulaexcel-2010excel-charts

Excel 2010 area chart drop off at the beginning

Earlier this week, I asked a question about getting an area chart to drop off vertically instead of diagonally when I don't have enough data for some series.

https://superuser.com/questions/809955/excel-2010-area-chart-drop-off

teyley's answer works really well, that's exactly what I wanted. However, there is now some situations where I will have data for February to August for example but no data for January (because the employee wasn't yet in the enterprise at this moment for example).

It does create a diagonal drop off at the beginning of the chart again even though January's data are set to NA(). Using teyley's answer, I can get dynamic ranges for the series, but it still has to be progressive, the first row of data will always be January using that method. What to do if the data for a specific series actually starts on February?

So, assuming I have data for February to August, using teyley's example and answer, how could I make it so that there is a vertical drop off in February instead of a diagonal going down to 0 on January?

Here is the formula for the dynamic named range:

FirstSeries = OFFSET($B$1,1,0,COUNT($B:$B),1)

Then, I use this in the chart for each series as shown in my earlier question:

=SERIES($B$1,$A$2:$A:$A13,Book1!FirstSeries,1)

Screenshot

Solution

  • I got this answer from another forum, but I've been looking quite a while for the answer, so I'll post it here as well. The original thread can be found here: http://www.excelforum.com/showthread.php?t=1024240

    • make your category axis into dates (not text or select automatically)
    • insert a row in your excel sheet for the date where you want the cliff (move values down) and repeat that date. In your case your Date column will read: Jan, Feb, Feb, Mar, Apr, May etc, and you don't have to add any values in the cells that are created next to Feb in the One, Two, Three, and AxisSpacer columns.

    I haven't researched how you can apply this with dynamic named ranges.