What people try to do to fake a blank is return “” in the formula.
=IF(A1=””,””, A1) The result to you and me like a blank, but to Excel, “” is a text string, albeit a short one, and it is therefore assigned a value of zero.
In the chart at the right, there is no point (wedge) and no corresponding data label where the empty cell would be plotted.
Apparently all charts except the Area chart provide a non-plotted point for an empty cell in the data range, though we can spoil that by selecting the option to treat a gap as a zero value.
=IF(A1=””, NA(), A1) What #N/A does is prevent the rendering of a marker in XY and line charts: notice the lack of markers and data labels.
It’s not a gap, but it’s pretty much the next best thing. The column and area charts place a data label showing #N/A at zero.
A function like BLANK() or NULL() would be nice, and we’ll keep asking. And if we really need it, we can hack something in VBA.