Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Charts with zero values for data points

Charts with zero values for data points

Charts with zero values for data points

Does anyone know how to not show a data point on a chart (lines) when the data point is zero?  The lines connecting data points are going all the way back to zero which is undesireabe.  I would rather just not chart the zeroes.

RE: Charts with zero values for data points

The point, and the lines leading into it and out from it, will show on the graph unless either its X or its Y value is an empty cell.  This is logical up to a point, since (as the ancient Greeks first pointed out to an astonished world) zero IS a number.

The problems begin when the number to be plotted is actually the result of a formula, and there are some circumstances under which you do not want the point to appear.  I have been unable to find a way of achieving this directly:  Excel does not provide a way of having a formula's result being seen as "null" by the charting process.  A serious weakness, IMHO.

The only workaround I have found is to assign an area of the spreadsheet to be a "copy" of the data you wish to graph.  Then write a VBA procedure that firstly nulls that area, then copies into it only the cells that you want to be included in the graph.  Drive your graph from this copy area.  Set the VBA procedure up so that it will be executed every time any change is made to your worksheet.

Not for the faint of heart!


RE: Charts with zero values for data points

To start at a number greater than  zero on any axis, simple right click on any number in axis, then select: format axis/scale/minium and change to whatever number you want to start with.

RE: Charts with zero values for data points

You can do the following:
>  wrap an IF statement around the calculations such that if the resulting calculation is zero, to put "" in the cell.  Which makes it sort of blank.  If you just have data, you can create an adjacent column that has the IF statement checking for zero values and blanking if zero.

>  then, turn on the autofilter and select top NN, where NN is the number of data points.  Autofilter will not select the blanked cells.


RE: Charts with zero values for data points

if you have your formula output #N/A! (by using the function NA() ) Excel won't plot the cell (e.g. when it is zero).
See also Thread770-83245


Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Charts with zero values for data points

Thanks, Joerd.  I was unaware of that.  It is an extremely useful piece of information, and it partly solves the problem I discussed above.

The part if does not solve is how to get Excel to leave a gap in the line.  Consider a dataset that comprises 5 points A through E.  Each point contains a numeric X value and a formula that gives the Y value.
(1)  If all points are valid and numeric, then the resulting X-Y graph will go A-B-C-D-E.
(2)  If the formula result for point C is NA(), then (as Joerd has told us) the plotted line will go A-B-D-E.
(3)  How can one force the line to appear as two separated sub-lines, one going A-B and the other going D-E?  In other words, what formula result will force Excel's charting capability treat the cell the same way as it would treat a completely empty cell?

Any good ideas out there on the e-waves?

RE: Charts with zero values for data points

Why don't you just plot it with the data markers on?  This way, you'll see a missing marker where there was no data?


RE: Charts with zero values for data points

If you go down the column with the numbers for the Y axis and delete all the zeros it will chart with gaps between them. It seems that if there is anything in the cell, even a formula to make cell blank, it will go to zero.

RE: Charts with zero values for data points


Only workaround for your item (3) I see at the moment is to do it manually: select the series, select the connecting line you want to delete, and format it to show no line. Of course, this is not what you want, you could more easily empty the cells with 0 by hand...


Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Charts with zero values for data points

to leave a gap in the line when the cells are empty,
click on a chart, the go to "TOOLS" "OPTIONS" and "Chart".  then select the what you want.

RE: Charts with zero values for data points

JEB66, that only works when cell is empty, no formula, no nothing.

Watco/ Denial, if you don't mind vertical lines happening at either side of the "blanked" portion, you can modify IRstuff's approach and, instead of blanking with "" in the IF statement, insert a large (many orders of magnitude) negative number in the charting column. You need to be charting with straight lines and not "smoothing" to make the points join properly, but you will have near vertical lines at the break.

If you use a large enough number, it will appear as #### in the column anyway.

Advantage over manually deleting zero or blanked cells is that the formula is retained in each of those cells.

RE: Charts with zero values for data points

If you don't want the lines going to/from the zero points (or between non-sequential points where a zero has been eliminated in between by one of the other suggested methods), you can manually eliminate them by clicking on the line segment a second time (the first time selects the whole series, the second time selects only the line segment joining one pair of points), then right-click and choose "Format Data Point", then on the Patterns tab select Line|None.  That segment then becomes hidden. Repeat for any other segments you don't want to show.

RE: Charts with zero values for data points

Use the Autofilter to select rows with your zero results (or error flag, or whatever). Delete the filtered values then turn the autofilter off.  Should plot without the joining lines, but will need to be redone manually if your data changes.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close