Turn off Zero values at the end of the chart

[Landmark]

Please can someone tell me how to stop my charts plotting zero values
at the end in cases where the chart runs for a longer period than my
data?

For example, my chart total date range is say Jan 05 to Dec 05, and I
have several lines to plot, the longest one extending for the whole 12
month period, but others for shorter periods from say Jan to Jun 05,
and Feb to Nov 05 etc. At the moment the way the source data is set
up, is that for the line item that ends in June, there are zero values
in the column for Jul to Dec 05 and my chart is plotting these zero
values, however I want my line to just end in June 05 otherwise my
chart looks odd.

The easy answer might be to delete the zero values from my source data,
but that will take a lot of fiddling around if I need to do that every
time I want to update my charts.

I am sure I remember turning off zero values once before, but I cant
remember how!

Thanks in advance

Laura

 

[Bernard Liengme]

1) use a dynamic chart - see www.stfx.ca/people/bliengme/ExcelTips
or
2) replace the blanks by =NA()
or
3) with chart selected use Tools |Options |Chart and specify how missing
data is to be treated
or
4) if using XL 2003 make a LIST and the chart will be automatically dynamic

 

[Landmark]

Hi Bernard, thanks for your reply, option 3 looks like the easiest
except for the fact that my data is not missing it is there as a zero
in the cell, I will have a play round with the various options and see
if I can make any of them work for me though.

Thanks and happy new year.

Laura

 

[CarlosAntenna]

If you have a formula that is returning the zeros, you could try something
like this...

=if(formula=0,"",formula)

Then maybe option 3 would work for you.