-1

I do not use excel very often and am running into an interesting issue.

I am trying to plot a simple line graph against time where there are missing data. I would like gaps for the missing data. I apologize for the formatting, but essentially something like this:

    1950 1951 1952 1953 1954 1955
    12    13              14   15

I would like my graph to have a line between 12 and 13, then a gap, then 14 and 15. This is a simplified example, the data set is much bigger, and there are instances where there is only one point surrounded by blanks, and thus I have a formula to duplicate data values in that case so I can actually have a line between two points (e.g. make Blank Blank 15 Blank Blank into Blank 15 15 Blank Blank).

Now this is the interesting part. When I paste as values and get something like the above, the missing values show up as 0, even when going through the chart formatting options in Excel 2013 to select the gaps option for how to show missing data.

However, if I go to the blank cells and hit backspace, then enter (seemingly deleting nothing), the gaps show up as desired.

I have verified the original cells are blank using ISBLANK and CODE(). ISBLANK returns TRUE and CODE() returns VALUE?.

Does anyone know what hitting backspace is doing that pasting a blank cell is not, and does anyone know a way to automate the process so that I do not have to go to every blank cell, hit backspace then enter for it to work.

Thanks, Evan

Evan Smith
  • 1
  • 2
  • I just saw this: http://stackoverflow.com/questions/1119614/return-empty-cell-from-formula-in-excel. I will try it and close question if it works. – Evan Smith Mar 23 '15 at 17:06

1 Answers1

-1

I believe if you replace your blanks with the #N/A error code it will reflect in the graph the way you want. The #N/A will show up as a blank on your graph that is.

You can use the NA() formula to produce the error code when the cell there is a blank cell with an IF() statement, i.e. 

=IF(A1="",NA(),A1)
Ratbert
  • 5,463
  • 2
  • 18
  • 37
C.Penrod
  • 11
  • 6
  • This seems to work for some people (In the question that this links to, sorry for the duplicate!) but not for me. No matter what I did the NA function had Excel interpolate the line between the non missing data instead of gaps, even when I had the chart settings to make gaps. What actually worked was Find and Replace blanks with letters, then find replace letters with the blank again. Weird. – Evan Smith Mar 24 '15 at 02:00
  • #N/A results in no marker being plotted, and a line interpolated across the space where the missing marker would have appeared. – Jon Peltier Jul 11 '15 at 15:55