Ignoring zeros and non-numeric data in a chart

[ajcolyer]

How can I make an excel 2007 graph ignore data that equals zero or is non-numeric? I am plotting stock prices, but am pulling in the data from elsewhere. Where the graph dates start before the company traded, the data shows as a zero. I want to avoid having the graph spike from zero to the trading amount on the first data of trading (i.e., it shows a vertical line now, with lines at zero prior to trading). Thanks.

 

[tusharm]

If the cells are truly empty (i.e., no value, no formula, no nothing), Excel will not plot those cells.

So, your best bet would be to simply select those cells and hit the Delete key.

If you cannot do that, replace the existing formula with something that yields NA() -- it will show up as #N/A -- whenever there is no data to be shown.

 

[Andrew Poulsom]

Try AutoFiltering your data for non zero. Excel should plot only the visible cells if that is is selected in Chart options.

 

[ajcolyer]

Thanks folks. I did the NA() and that worked beautifully.

 

[riclop]

Entering the na() formula works for a basic chart. I am running into a problem with a combo chart and the line not showing zeros or #N/A values as 0 but ignoring them. Any thoughts on how to get a combo chart to ignore zeros or non-numeric data points?

This picture shows a simple example:

If the cells are truly empty (i.e., no value, no formula, no nothing), Excel will not plot those cells.

So, your best bet would be to simply select those cells and hit the Delete key.

If you cannot do that, replace the existing formula with something that yields NA() -- it will show up as #N/A -- whenever there is no data to be shown.

 

[riclop]

I figured it out. The setting on "Select Data" --> Hidden and Empty Cell Settings --> Show empty Cells as Gaps needs to be selected. The line chart type then needs to be a "Line" rather than a "Stacked Line with Markers." This will do the trick.