MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Want chart not to plot zero values?

  Asked By: Gin    Date: Oct 22    Category: MS Office    Views: 3644

This isn't a VBA question, exactly, but I have to start somewhere.
Perhaps somebody can point me in the right direction.

I want a line chart not to plot any cell with a zero value. Various
methods of not *displaying* zeroes don't affect what a chart plots--it
plots the invisible zero.

Here's an example:

I have a column E filled with the following formula (adjusted, of
course, to reflect the row number in which each cell appears):


I add entries to the data daily. As I enter values in columns A and B
in its row, the E cell displays the calculated result; until then it
appears blank. A line chart embedded in the same worksheet shows the
continuing effect of each entry.

At any given time the series shown on the chart may be set to plot the
contents of cells $E$2:$E$80, and,for example, only rows 2:15 have
data in them. Cells E21:E80 will display blank, but on the chart the
corresponding plot line will plummet to the X axis and stay there. The
result is ugly and unprofessional-looking. The plot line should just
end where the data stops.

True, I can tell the chart not to plot *empty* cells, but the presence
of a formula seems to mean the cells are not empty. I guess I'm trying
to find a way to tell the chart to treat a cell with a zero value as
an empty cell, and I'm beginning to think it can't be done.

Note: in the example given above, if I delete the contents of E21,
including the formula, so it really is a blank cell, the chart will
plot neither it nor any of the following cells in the column, even
though they still have the formula in them. I suppose I could use some
kind of VBA macro to put the formula in the E-column cells *only* as I
enter data in the A and B columns, but this seems to be a very awkward

Anybody got any ideas?



5 Answers Found

Answer #1    Answered By: Raquel Austin     Answered On: Oct 22

would you be happy if the chart  showed data  until the last entry, but
the line  went right across the chart, so that the right edge was the
last entry? I'm thinking a dynamic range.

Answer #2    Answered By: Alarice Miller     Answered On: Oct 22

Two solutions to this maybe:
1. The uglier solution, but quicker: where you've got
change it to
The chart  won't plot  the #N/A cells  but will draw a line  directly from
one valid point to the next, ie. no gaps. The ugly side of this is the
presence of a lot of cells displaying  #N/A in the worksheet.

2. Leave your formula  as it was, but create a named range with a
formula: Go into Insert|Name|Define... and enter  say 'graph' (without
the single quote marks) in the 'Names in workbook' field. Then in the
'Refers to:' field paste something similar to the following:
Then click the 'Add' button and close the dialogue box.
This will create a series  of values  like the cells in solution (1)
above, in the defined name 'graph' , and this is what we'll plot. Go
back to the chart and click on one of the points of your series so
that the whole series is selected. Look in the formula bar and you'll
see the likes of:
It's the
that needs changing to
(without quote marks) and press Enter. (It may change to
that's fine.)
Now you should see the same chart as in (1) above  but without all the
#N/As in the worksheet.

A third solution is to create a dynamic named range instead of the
static one above when a chart as proposed in my previous answer would
be produced.

Answer #3    Answered By: Frederik Jones     Answered On: Oct 22

This is very strange. I KNOW I have done plots  that simply omit non exsistent
data, but
couldn't figure out how i Used to do it [brain tells me there 'was' a
option, but brain has been wrong B4].

I tried an X-Y plot  - nope.

This may get you closer to a solution...
Here's what is the strangest.
I found that If I Paste Values (from the formula  cells), the chart  STILL
plots "empty" cells
as zero.
HOWEVER, if I Clear Contents in that cell, then the point and connecting
lines are

A "" is interpreted as a zero.
A Pasted Value of "" is interpreted as a zero.
a Cleared cell  is undefined / unplottable.

So, do a TypeName on the three types of "blank" cells( "" in formula, a Pasted
"" from a
formula and a fully Cleared cell) to see what they are.

This may mean you'll need a macro  to Clear a copy of the Zero valued cells.


Answer #4    Answered By: Niran Jainukul     Answered On: Oct 22

I suspect you'll find  that the times you did that were on column  charts,
rather than line  charts. It's relatively unusual to want to omit items in
line charts.

Answer #5    Answered By: Femke Bakker     Answered On: Oct 22

My recollection is clearly that it was on line  charts. I rarely do
column charts. However, it is entirely possible that I may have
simply Cleared the values  of the zero/blank cells. I also rarely do
plots of more than a few dozen values, so manual intervention for the
desired effect  is always a possiblity.

I am, however, still curious what the types of those cells  are...

So I checked. A Cleared cell  is Type "Empty".
A cell containing "" is an [obviously null] "String"...which, of
course, "makes sense"...

SO, perhaps a macro  to clear those pesky null cells (a copied
version) is in order.

Didn't find what you were looking for? Find more on Want chart not to plot zero values? Or get search suggestion and latest updates.