# Excel chart, how to make a formula produce gaps?

I have a chart in Excel 2007 that is basically just a line graph.

Some cells that make up the series data cannot produce a value through their formula, and I'd like the chart to place a gap in that location, but no matter what I do, the fact that there is a formula in there seems to make the series consider this as a zero, or similar.

If I hit Delete on that cell, the gap appears, but when I add a formula, a line that connects the points on either side appears.

Is there any way I can return something with an IF function that produces the gap?

``````IF(somecondition; SUM(...); "")
^^-  what to add here, is it possible?
``````

The following does not work:

``````IF (somecondition; SUM(...); "")
IF (somecondition; SUM(...);)
IF (somecondition; SUM(...))
IF (somecondition; SUM(...); NA)
IF (somecondition; SUM(...); 1/0)
``````

They are all still drawn as though the cell contained 0 (zero).

• That is considered to be zero, which makes the graph hit bottom for that point. I want the graph to disappear/stop. Jan 8, 2010 at 14:11
• The real answer now (many years later) is to update to a recent version of Excel. Then if you make the gap value equal to #N/A, you can have that plot as a blank cell, which means you can get a gap, rather than an interpolated line. Mar 14 at 1:11

Here's the same question/answer from SO. It seems that you could use a Worksheet_Change macro to accomplish this but the only option is to delete the contents of the cell if the value is empty or zero which, in turn, removes your formula unfortunetely.

https://stackoverflow.com/questions/1119614/return-empty-cell-from-formula-in-excel

I'd normally say return NA(). However, in a line graph, even if you set the series preferences to leave gaps ("Select data" -> "Hidden and empty cells" -> "Show empty cells as:" -> "Gaps"), the line will just connect continuously to the next available point in the series. If you do not mind changing to another chart type, e.g. the area chart will leave the gaps.

• If you use a scatter plot any points you define as =NA() are ommited and no connecting line is drawn. Jan 9, 2010 at 6:14
• A recent (late 2017) enhancement to Excel 2016 treats #N/A like a blank cell. So finally there is an answer, and it is NA(). Jan 27, 2018 at 3:20

If you use a scatter plot any points you define as =NA() are ommited and no connecting line is drawn. – DaveParillo Jan 9 '10 at 6:14

Only partially correct. NA() or #N/A in a line or XY chart will suppress drawing of a marker. If there are markers on either side of one or more points suppressed by NA(), a line will connect the existing markers across the gap. If one or more suppressed points are at the end of the data range, no line will connect, because there is only a point on one side, and no point to connect to on the other side.

EDIT: A recent (late 2017) enhancement to Excel 2016 treats #N/A like a blank cell. (If you have Office 365, you can toggle this setting.) Finally now we can have a formula produce a result which is plotted as a gap.

• Where can the toggle be found in Office 365?
– Rick
Jul 17, 2018 at 14:04
• @Rick - Right click on chart, choose Select Data, click on Hidden and Empty Cells, check box for Show #N/A as an Empty Cell. Jul 18, 2018 at 14:39

Well, since it's a line graph, you could try setting the cell value to be the average between the previous and the next dot in the graph. For instance:

IF (somecondition; SUM(...); (nextcell + prevcell)/2 )

As I see it, if you did manage to place a gap in this cell, than excel would simply link the previous and next cells with a straight line segment (it is a line graph after all). However, by setting the middle cell's value to the average between its neighboors (instead of blank), you are ensuring that excell does indeed link the neighboor cells with a straight line segment, just as if the middle cell were blank.