I am plotting a line chart which is a time series. After a certain period of time, the time series shows #NUM! How can I get excel to ignore the #NUM! and plot nothing. Currently, it plots all values of #NUM! as zero which makes my chart inaccurate.


Consider using =IFERROR(A1,NA()). This will replace all error values with NA(), which Excel will automatically skip giving you a gap.

  • 2
    NA() or #N/A does not leave a gap. It prevents drawing a marker, but if the other points are connected by a line, Excel draws a line between the points on either side of the #N/A. – Jon Peltier Nov 27 '12 at 20:48

You can setup a graph to handle missing data, but not to handle faultive data.


So, to solve this you should protect your time-series with a code like:


Whereby A1 would be your "raw" data and B1 is the cell/column which you will have to use as chart-input.

enter image description here

  • how do i get to hidden and empty cell settings? – daysandtimes Sep 27 '12 at 14:49
  • choose 'select data source' or so, after a right-click into your chart, then the button at the lft-bottom – Jook Sep 27 '12 at 14:53
  • 4
    "" is interpreted by Excel as text, and is assigned a value of zero. If you don't want a point at zero, use NA() in the formula instead of "". This puts the ugly #N/A into the cell, but it is not plotted with a point. If the points are connected by lines, a line is drawn connecting the points on either side of the #N/A. – Jon Peltier Nov 27 '12 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.