zero value remove chart

[Guest]

I want to plot a chart but only show data labels for the values which are
greater than zero. I cannot find a way to remove the zero values and have a
chart which has a large amount of worthless data labels. How can
automatically remove them?

 

[Jon Peltier]

Apply a number format that hides zeros. A custom number format has four
parts, separated by semicolons:

0.00;-0.00;-;@

The four parts are for positive, negative, zero, and text values. Remove the
item in the zero's spot:

0.00;-0.00;;@

More on number formats:

http://peltiertech.com/Excel/NumberFormats.html

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

 

[James Silverton]

Jon wrote on Wed, 23 Nov 2005 07:54:26 -0500:

JP> 0.00;-0.00;-;@

JP> The four parts are for positive, negative, zero, and text
JP> values. Remove the item in the zero's spot:

JP> 0.00;-0.00;;@

JP> More on number formats:

JP> http://peltiertech.com/Excel/NumberFormats.html

Again, making a comment while not the OP, that is a fascinating
discussion Jon. I was particularly interested to find out for
the first time how to change the color of a particular value.

A rather similar problem has intrigued me for some time: is it
possible to change a gridline for a particular value to a
different thickness or color? I can find a use for this when the
zero value for the y-axis is inside the chart rather than along
an edge.

Again, thanks in advance and my apologies if I should have been
able to find this in Help.


James Silverton.

 

[Jon Peltier]

Official Excel gridlines are one color/style only. There are ways to fake a
gridline:

http://peltiertech.com/Excel/Charts/ArbitraryGridlines.html
http://peltiertech.com/Excel/Charts/AddLine.html

What you might want, if the zero is between the min and max, is to keep the
axis (a distinct line) at zero, but change the label position to "Low" so
they're out of the way.

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

 

[James Silverton]

Jon wrote on Wed, 23 Nov 2005 09:46:24 -0500:

JP>
http://peltiertech.com/Excel/Charts/ArbitraryGridlines.html
JP> http://peltiertech.com/Excel/Charts/AddLine.html

JP> What you might want, if the zero is between the min and
JP> max, is to keep the axis (a distinct line) at zero, but
JP> change the label position to "Low" so they're out of the
JP> way.

??>> Some clipping of less relevant information <<<<<<<<<<<<<

??>> A rather similar problem has intrigued me for some time:
is it
??>> possible to change a gridline for a particular value to a
??>> different thickness or color? I can find a use for this
??>> when the zero value for the y-axis is inside the chart
??>> rather than along an edge.

Thanks again Jon for a very prompt reply and some fascinating
information. I will have to read your two web pages very
carefully. It is a pity that the Excel programmers have never
thought of implementing my idea.

James Silverton.

 

[Jon Peltier]

Jon wrote on Wed, 23 Nov 2005 09:46:24 -0500:

JP> http://peltiertech.com/Excel/Charts/ArbitraryGridlines.html
JP> http://peltiertech.com/Excel/Charts/AddLine.html

JP> What you might want, if the zero is between the min and
JP> max, is to keep the axis (a distinct line) at zero, but
JP> change the label position to "Low" so they're out of the
JP> way.

??>> Some clipping of less relevant information <<<<<<<<<<<<<

??>> A rather similar problem has intrigued me for some time: is it
??>> possible to change a gridline for a particular value to a
??>> different thickness or color? I can find a use for this
??>> when the zero value for the y-axis is inside the chart
??>> rather than along an edge.

Thanks again Jon for a very prompt reply and some fascinating information.
I will have to read your two web pages very carefully. It is a pity that
the Excel programmers have never thought of implementing my idea.

James Silverton.

To be fair, I don't think the Excel designers envisioned half of the ways we
abuse and contort their charting module. I'm always glad they made it as
flexible as they did, so I can add features I want the way I want.

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