# Excel: How to create (truly) blank cells from formula so that they wont show up in a chart

chartsmicrosoft excel

I have a table where track data on a daily base, compare it to a daily target I have set, calculate the gap between the two and display the data on a line chart.
The data has 4 columns:

A. Date (from today until 31-12-2014
C. Actual value (only filled for past dates)
D. Target Value (all filled until 31-12-2014)
E. Gap (C-D)

I wanted the Gap (E) to be empty as long as there is no current date, and thus filled it with the formula:

=IF(ISBLANK(C10), "", C10-D10)


The future dates of Column E correctly display blank.
When I create a chart from the data (with E being on a different axis), the line is not drawn for future dates of column C since the values are blank, but they are drawn for future dates of column E with Zero.

I am assuming that the result of the formula with a "" content of the field is not considered as "blank" so that the chart assumes it to be zero.

How can I make the line of the chart in Column E disappear for dates where there is no value in Column C (and therefore also in Column E)?

Found the answer here. If you make the formula return NA() instead of "", the chart will accept it as empty and not display a line.