drop off to 0

[Irenerz]

I've reading reading posts, and I can't find a specific answer to my
question.

I have some data I want to plot in an area graph.
X-axis values go from 2005 to 2015.

For the first 5 categories I only have values for the 2005/2006 year. The
next categories I have all data from 2005 to 2015.

When I plot the area chart the first 5 series, with values in 2005 & 2006.
The area graph shows a drop off to zero in 2007.

My question is: Is there a way to not show the drop of from 2006-2007. I
have no value for that cell, but it still shows a drop off. Is there a way
to cut off the area at 2006 rather than having the area graph continue on.

Example:
Department 1: 2005: 10 / 2006: 20
Department 2: 2005: 25 / 2006: 35
Big Department: data for all 10 years.

Hopefully this makes sense. Thanks for your help in advance!

 

[Tushar Mehta]

The only way that I know how to do this is as follows:

After creating the chart, make sure that the shorter series include
only the cells with data. By default, XL might have included a bunch
of empty cells in those columns. So, if your dept 1 and dept 2 data
are in rows 2 and 3 and big dept in 2:11, make sure that the 1st two
series refer only 2:3.

Now, move the smaller series to the secondary axis. Double-click each,
then from the Axis tab select Secondary.

Format the secondary y-axis so that the scale matches that of the
primary y-axis. Next, hide it (from the Patterns tab set all the
options to None.)

This 'trick' requires that all the series on the secondary axis end at
the same x-value.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Mangesh Yadav]

For the blank cells, you could enter the formula
=NA()
which will put #N/A in those cells. The cells containing these are not
plotted.

Mangesh

 

[Andy Pope]

Hi Mangesh,

Did you try your suggestion?
The NA() trick does not work for Area charts, it still treats it as zero.

Cheers
Andy

 

[Andy Pope]

Hi,

As Tushar has already pointed out the way to get the Area to vertically
drop to zero rather than slope down to zero by the next period is to
shortern the data range references for those series.

As you will be adding future years data at some point it might be worth
looking at creating dynamic named ranges for your data series.
Here are 2 examples.
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy

 

[Mangesh Yadav]

Haven't tried it on area charts, infact I rarely use them. I always use XYs,
and #N/A are my favourites. Didn't know they don't work with Area charts.
Got to know something new toay. Thanks

Mangesh

 

[Jon Peltier]

If you convert to a time scale axis, you can make a sharp drop in an
area chart. You need two points for the X value (2006 here) where the
drop off occurs. Any continuing series uses the same value for both 2006
occurrances.

BigDept Dept1 Dept2
2005 200 100 150
2006 215 125 165
2006 215 0 0
2007 225 0 0
2008 235 0 0
2009 250 0 0

Plot the data in an area chart. At first 2006 appears twice along the X
axis. Choose Chart Options from the Chart menu, select the Axes tab, and
under Category Axis, check the Time Scale box. Now you get a sharp drop
off at 2006, but the dates are goofed up. Double click the axis, and on
the Number tab, select General, and you'll get your years back again.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______