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

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.

Use #N/A to make the chart display blanks in a formula =IF(A1="",#N/A,A1) =IFERROR(A1,#N/A)

I found a slight workaround for column charts. If you want to use data points but don't want zeros to show. Change the data point number format to a custom format where zeros equal "".

I used the code:

_(* #,##0_);_(* (#,##0);_(* ""_);_(@_)

All this is is the accounting format with the

_(* #,##0_);_(* (#,##0);_(* **"-"**_);_(@_) 

replaced with

_(* #,##0_);_(* (#,##0);_(* **""**_);_(@_)

Notice "-" replaced with "". the - is zero in accounting format. Hope this helps someone.