Where is the option not to plot zero value on a chart hiding in Excel 2007?

In article ,
says...
Where is the option not to plot zero value on a chart hiding in Excel 2007?

Is there such an option? In previous versions, the solution was to replace
the zero with a truly empty cell or, if the result of a formula, NA().

--
Regards,

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

"Tushar Mehta" wrote in message
om...
In article ,
says...
Where is the option not to plot zero value on a chart hiding in Excel
2007?

Is there such an option? In previous versions, the solution was to
replace
the zero with a truly empty cell or, if the result of a formula, NA().

Perhaps what the OP was thinking of in earlier versions was:
Tools/ Options/ Chart/ Plot empty cells as ...
The options a
Not plotted (leave gaps), or
Zero, or
Interpolated

Empty cells are of course different from zeroes, but that may be where the
confusion arises?
--
David Biddulph

Yes. I have "#N/A" in the cells, but the chart is still interpolating them.
I want gaps. I thought if I could be sure that the switch was set to "Not
plotted" it may leave gaps. But I can't find that switch in Excel 2007.


"David Biddulph" wrote:

"Tushar Mehta" wrote in message
om...
In article ,
says...
Where is the option not to plot zero value on a chart hiding in Excel
2007?

Is there such an option? In previous versions, the solution was to
replace
the zero with a truly empty cell or, if the result of a formula, NA().

Perhaps what the OP was thinking of in earlier versions was:
Tools/ Options/ Chart/ Plot empty cells as ...
The options a
Not plotted (leave gaps), or
Zero, or
Interpolated

Empty cells are of course different from zeroes, but that may be where the
confusion arises?
--
David Biddulph

I can't do the same thing but in 2003. I have a formula that needs to be in
the cells or I'll have to adjust the range every single time I need the data.
This would result in me cutting and pasting all day to generate the dozens
of reports I need.

I have tried making the forumula return an empty cell "" or even a dash "-"
but it reads it all as zero.

Any help or ideas??

Hi,

The setting is on the Select Data dialog. "Hidden and Empty cells" button.
Select chart, select Design contextual tab, Data group.

But this will not resolve your problem as your cells are not empty. #N/A
will be Interpolated.
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy

Sashi wrote:
Yes. I have "#N/A" in the cells, but the chart is still interpolating them.
I want gaps. I thought if I could be sure that the switch was set to "Not
plotted" it may leave gaps. But I can't find that switch in Excel 2007.


"David Biddulph" wrote:


"Tushar Mehta" wrote in message
.com...

In article ,
says...

Where is the option not to plot zero value on a chart hiding in Excel
2007?


Is there such an option? In previous versions, the solution was to
replace
the zero with a truly empty cell or, if the result of a formula, NA().

Perhaps what the OP was thinking of in earlier versions was:
Tools/ Options/ Chart/ Plot empty cells as ...
The options a
Not plotted (leave gaps), or
Zero, or
Interpolated

Empty cells are of course different from zeroes, but that may be where the
confusion arises?
--
David Biddulph

Andy's trick was pretty cool, but I have multiple lines which will
likely cross one another and some bars to boot, so the masks show. Are
there any other ways to prevent Excel from interpolating between
points? My final chart will be a bar chart with 3 lines. The lines
can stop/start at any point (inlcuding single points) and could even
occur on the same date (date is X).

Thanks in advance to anyone who offers ideas - I've been down the
"empty cell" that's really a formula, and use #N/A only to find it
interpolates between points road for a few hours and plan on either
giving in to lines that shouldn't be .. or finding an uber-geek on this
board with a complete solution (just don't say "use Lotus").

Andy Pope wrote:
Hi,

The setting is on the Select Data dialog. "Hidden and Empty cells" button.
Select chart, select Design contextual tab, Data group.

But this will not resolve your problem as your cells are not empty. #N/A
will be Interpolated.
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy

Sashi wrote:
Yes. I have "#N/A" in the cells, but the chart is still interpolating them.
I want gaps. I thought if I could be sure that the switch was set to "Not
plotted" it may leave gaps. But I can't find that switch in Excel 2007.


"David Biddulph" wrote:


"Tushar Mehta" wrote in message
.com...

In article ,
says...

Where is the option not to plot zero value on a chart hiding in Excel
2007?


Is there such an option? In previous versions, the solution was to
replace
the zero with a truly empty cell or, if the result of a formula, NA().

Perhaps what the OP was thinking of in earlier versions was:
Tools/ Options/ Chart/ Plot empty cells as ...
The options a
Not plotted (leave gaps), or
Zero, or
Interpolated

Empty cells are of course different from zeroes, but that may be where the
confusion arises?
--
David Biddulph

Hi,

You could create a set of specific charting data where the cells that
contain #N/A have their content cleared.
Obviously this can be done manually or with VBA code.

Cheers
Andy

Corey Mac wrote:
Andy's trick was pretty cool, but I have multiple lines which will
likely cross one another and some bars to boot, so the masks show. Are
there any other ways to prevent Excel from interpolating between
points? My final chart will be a bar chart with 3 lines. The lines
can stop/start at any point (inlcuding single points) and could even
occur on the same date (date is X).

Thanks in advance to anyone who offers ideas - I've been down the
"empty cell" that's really a formula, and use #N/A only to find it
interpolates between points road for a few hours and plan on either
giving in to lines that shouldn't be .. or finding an uber-geek on this
board with a complete solution (just don't say "use Lotus").

Andy Pope wrote:

Hi,

The setting is on the Select Data dialog. "Hidden and Empty cells" button.
Select chart, select Design contextual tab, Data group.

But this will not resolve your problem as your cells are not empty. #N/A
will be Interpolated.
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy

Sashi wrote:

Yes. I have "#N/A" in the cells, but the chart is still interpolating them.
I want gaps. I thought if I could be sure that the switch was set to "Not
plotted" it may leave gaps. But I can't find that switch in Excel 2007.


"David Biddulph" wrote:



"Tushar Mehta" wrote in message
ft.com...


In article ,
says...


Where is the option not to plot zero value on a chart hiding in Excel
2007?


Is there such an option? In previous versions, the solution was to
replace
the zero with a truly empty cell or, if the result of a formula, NA().

Perhaps what the OP was thinking of in earlier versions was:
Tools/ Options/ Chart/ Plot empty cells as ...
The options a
Not plotted (leave gaps), or
Zero, or
Interpolated

Empty cells are of course different from zeroes, but that may be where the
confusion arises?
--
David Biddulph






--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info