## Charts with zero values for data points

## Charts with zero values for data points

(OP)

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.

Kevin.

Kevin.

## RE: Charts with zero values for data points

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!

HTH

## RE: Charts with zero values for data points

## RE: Charts with zero values for data points

> 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.

TTFN

## RE: Charts with zero values for data points

See also Thread770-83245

Cheers,

Joerd

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

## RE: Charts with zero values for data points

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

TTFN

## RE: Charts with zero values for data points

## 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...

Cheers,

Joerd

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

## RE: Charts with zero values for data points

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

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

## RE: Charts with zero values for data points