I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.

select the chart.
goto tools options and check plot empty cells as any of the three chices
given there.


"fishy" wrote in message
...
I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.

Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

Cheers
Andy

R..VENKATARAMAN wrote:
select the chart.
goto tools options and check plot empty cells as any of the three chices
given there.


"fishy" wrote in message
...

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.




--

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

Working thanks - as you said, already tried the first suggestion but as it
was a formulae, it still showed.

"Andy Pope" wrote:

Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

Cheers
Andy

R..VENKATARAMAN wrote:
select the chart.
goto tools options and check plot empty cells as any of the three chices
given there.


"fishy" wrote in message
...

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.




--

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

I have tried doing a conditional format on the #N/A but it does not seem to
work. What do you need to specifically put in the conditional format. I have
tried #N/A, NA(), N/A ?

"fishy" wrote:

Working thanks - as you said, already tried the first suggestion but as it
was a formulae, it still showed.

"Andy Pope" wrote:

Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

Cheers
Andy

R..VENKATARAMAN wrote:
select the chart.
goto tools options and check plot empty cells as any of the three chices
given there.


"fishy" wrote in message
...

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.




--

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

Use the Is Formula option, and this formula, where the active cell is A1

=ISNA(A1)

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


"AR" wrote in message
...
I have tried doing a conditional format on the #N/A but it does not seem to
work. What do you need to specifically put in the conditional format. I
have
tried #N/A, NA(), N/A ?

"fishy" wrote:

Working thanks - as you said, already tried the first suggestion but as
it
was a formulae, it still showed.

"Andy Pope" wrote:

Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced
with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

Cheers
Andy

R..VENKATARAMAN wrote:
select the chart.
goto tools options and check plot empty cells as any of the three
chices
given there.


"fishy" wrote in message
...

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.




--

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

Jon

I have done this but in an earlier discussion Andy suggested after doing the
formula you: " Use conditional formating to hide the #N/A". THis is the part
I cannot get to work. Whatever version of N/A I use I cannot fomat it.


"Jon Peltier" wrote:

Use the Is Formula option, and this formula, where the active cell is A1

=ISNA(A1)

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


"AR" wrote in message
...
I have tried doing a conditional format on the #N/A but it does not seem to
work. What do you need to specifically put in the conditional format. I
have
tried #N/A, NA(), N/A ?

"fishy" wrote:

Working thanks - as you said, already tried the first suggestion but as
it
was a formulae, it still showed.

"Andy Pope" wrote:

Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced
with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

Cheers
Andy

R..VENKATARAMAN wrote:
select the chart.
goto tools options and check plot empty cells as any of the three
chices
given there.


"fishy" wrote in message
...

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.




--

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

I suggest you read Jon's answer again. He's told you exactly how to do it.
--
David Biddulph

"AR" wrote in message
...
Jon

I have done this but in an earlier discussion Andy suggested after doing
the
formula you: " Use conditional formating to hide the #N/A". THis is the
part
I cannot get to work. Whatever version of N/A I use I cannot fomat it.

"Jon Peltier" wrote:

Use the Is Formula option, and this formula, where the active cell is A1

=ISNA(A1)

"AR" wrote in message
...
I have tried doing a conditional format on the #N/A but it does not seem
to
work. What do you need to specifically put in the conditional format. I
have
tried #N/A, NA(), N/A ?

"fishy" wrote:

Working thanks - as you said, already tried the first suggestion but
as
it
was a formulae, it still showed.

"Andy Pope" wrote:
Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced
with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A
....

Here's Debra Dalgleish's explanation:

http://contextures.com/xlCondFormat03.html#Errors

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


"AR" wrote in message
...
Jon

I have done this but in an earlier discussion Andy suggested after doing
the
formula you: " Use conditional formating to hide the #N/A". THis is the
part
I cannot get to work. Whatever version of N/A I use I cannot fomat it.


"Jon Peltier" wrote:

Use the Is Formula option, and this formula, where the active cell is A1

=ISNA(A1)

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


"AR" wrote in message
...
I have tried doing a conditional format on the #N/A but it does not seem
to
work. What do you need to specifically put in the conditional format. I
have
tried #N/A, NA(), N/A ?

"fishy" wrote:

Working thanks - as you said, already tried the first suggestion but
as
it
was a formulae, it still showed.

"Andy Pope" wrote:

Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced
with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

Cheers
Andy

R..VENKATARAMAN wrote:
select the chart.
goto tools options and check plot empty cells as any of the three
chices
given there.


"fishy" wrote in message
...

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.




--

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

Thanks Jon for the How To Conditional Format. I wasn't using Formula Is I was
using Cell Is

"Jon Peltier" wrote:

Here's Debra Dalgleish's explanation:

http://contextures.com/xlCondFormat03.html#Errors

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


"AR" wrote in message
...
Jon

I have done this but in an earlier discussion Andy suggested after doing
the
formula you: " Use conditional formating to hide the #N/A". THis is the
part
I cannot get to work. Whatever version of N/A I use I cannot fomat it.


"Jon Peltier" wrote:

Use the Is Formula option, and this formula, where the active cell is A1

=ISNA(A1)

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


"AR" wrote in message
...
I have tried doing a conditional format on the #N/A but it does not seem
to
work. What do you need to specifically put in the conditional format. I
have
tried #N/A, NA(), N/A ?

"fishy" wrote:

Working thanks - as you said, already tried the first suggestion but
as
it
was a formulae, it still showed.

"Andy Pope" wrote:

Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced
with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

Cheers
Andy

R..VENKATARAMAN wrote:
select the chart.
goto tools options and check plot empty cells as any of the three
chices
given there.


"fishy" wrote in message
...

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.




--

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

Really important because it is NOT obvious through the prior instructions in
this change: you have to set the conditional formatting for a single cell as
decribed and then copy the format with the paintbrush to all the other cells.
You can NOT select a range of cells and set the formula based conditional
formatting for them all at the same time.

"AR" wrote:

Thanks Jon for the How To Conditional Format. I wasn't using Formula Is I was
using Cell Is

"Jon Peltier" wrote:

Here's Debra Dalgleish's explanation:

http://contextures.com/xlCondFormat03.html#Errors

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


"AR" wrote in message
...
Jon

I have done this but in an earlier discussion Andy suggested after doing
the
formula you: " Use conditional formating to hide the #N/A". THis is the
part
I cannot get to work. Whatever version of N/A I use I cannot fomat it.


"Jon Peltier" wrote:

Use the Is Formula option, and this formula, where the active cell is A1

=ISNA(A1)

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


"AR" wrote in message
...
I have tried doing a conditional format on the #N/A but it does not seem
to
work. What do you need to specifically put in the conditional format. I
have
tried #N/A, NA(), N/A ?

"fishy" wrote:

Working thanks - as you said, already tried the first suggestion but
as
it
was a formulae, it still showed.

"Andy Pope" wrote:

Hi,

That will not work is the cells actually contain zero because by
definition containing zero is NOT empty.

The zeros will either need to be removed from the cells or replaced
with
NA().

=IF(SUM(myrange)=0,NA(),SUM(myrange))

Use conditional formating to hide the #N/A

Cheers
Andy

R..VENKATARAMAN wrote:
select the chart.
goto tools options and check plot empty cells as any of the three
chices
given there.


"fishy" wrote in message
...

I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value.

The graphs link off these tables and show drops to zero on these
occurences.
How do I get the graph to ignore the zero values.

The source table formula is too long to put an if arguement in.




--

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

Sorry for rehashing a really old topic, but I've got that conditional formatting set on the ranges of cells I'm graphing, but the graphs still show the empty categories:

Screenshot of data tables: https://www.dropbox.com/s/ckt2tx7j6zg2i5n/data.JPG?dl=0
Screenshot of graphs: https://www.dropbox.com/s/gctgh55yhp...raphs.JPG?dl=0

Is there any way to make those automatically not show if their value is 0?

-Sarah M