51

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

Improve this question

3 Answers 3

Reset to default
50

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.

Improve this answer
4
  • more reference. I can't find any official documentation on when excel started interpreting error as nothing for charts.
    – Raystafarian
    Commented Nov 7, 2014 at 9:58
  • 3
    Raystafarian - 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
    Commented Dec 29, 2014 at 3:58
  • 4
    Note: 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
    Commented Jan 13, 2020 at 15:41
  • Achieves the goal well but I hate the fact that #NA is displayed in the cell - any workaround for this?
    – Percy
    Commented Sep 26, 2023 at 7:50
5

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

Improve this answer
1

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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .