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)?

uncovery

Posted 2014-11-07T05:26:40.833

Reputation: 1 158

Answers

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.

uncovery

Posted 2014-11-07T05:26:40.833

Reputation: 1 158

more reference. I can't find any official documentation on when excel started interpreting error as nothing for charts. – Raystafarian – 2014-11-07T09:58:11.513

2Raystafarian - It's nothing new, Excel has done this since at least Excel 97, and probably earlier. Text and most errors are treated as zeros. #N/A is not plotted in a line or XY chart. It does not result in a gap in the line, though (as uncovery says), but a line will connect the points on either side of the #N/A. – Jon Peltier – 2014-12-29T03:58:11.127

1Note: For some diagrams, this might still be treated different than truly empty cells (in the sense of not even containing any formula at all). For example line charts skip empty values by not drawing a line between adjacent points in this case, but if the "empty" cell is #N/A, the line is shown (without a point). Example: the series 1, 2, <empty>, 4 draws three points (1,2,4) and one line (from 1 to 2). But 1, 2, #N/A, 4 draws the same points and the two lines 1 to 2 and 2 to 4. – leemes – 2020-01-13T15:41:33.227

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

Andy Robertson

Posted 2014-11-07T05:26:40.833

Reputation: 51

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.

Number Formatting

JP Birbal

Posted 2014-11-07T05:26:40.833

Reputation: 11