Give us a proper NULL() worksheet function.
This would make calculations and charts treat a cell containing a formula as a blank cell, not as a cell with a text string ("") or as a zero.
Great suggestion – thanks again for taking the time to put it on this site and for the thoughtful followup comments. This is pretty related to some other work we’ve got going and already has a fair number of votes, so we’ll work on getting plans in place now and hope to get started on this soon.
John [MS XL]
... and just seen the recent Office 365 update to "show #n/a as empty cell"
@A.C.Wilson. The basic sheet structure is (see ecma openxml documentation):
- Cells are either constants or formulas that return values (number, text, logical, error) or else are empty (blank).
- Formulas are strings of operators, functions, names, values, or other derived data types (array, reference, missing).
Introducing a new data type (Null) in addition to these eight would very likely break compatility and involve redesign of many existing functions and features.
Adding an error value such as #MISSING! (#NULL! already exists) is also possible but adds complexity by introducing another null representation in addition to blank / missing.
Alternatively, other options could be added for how to handle #N/A or zero length string just as you have for blank cells in chart options.
[For +Infinity (2^1024 in terms of doubles):
Function inf() As Double
On Error Resume Next
A.C. WILSON commented
My primary reason to NOT call this thing "EMPTY()" [nor "BLANK()"] would be that my life is simpler when the same functionalities in Excel and in Access have the same names. But, this would not be a deal-breaker for me.
What to name this thing is a problem primarily because Microsoft's writings have been very vague about both the vocabulary to describe, and the behavior of, a cell's "state" [my own imperfect term, not Microsoft's].
It appears to me that each Excel cell has four possible "states":
1) BLANK [Default null; cannot (yet) contain any value or expression. All cells in a new empty worksheet start out in this "state". A cell can NOT (yet) be reset to BLANK/null via an Excel cell formula. [In Microsoft Access, and perhaps also in Excel VBA, formulas CAN be used to force to "NULL".] Depending on how we choose to conceptualize "value", a "BLANK" cell either cannot contain any value in it, or can contain only one possible value, which we conceptualize as "blank".]
2) TEXT [Can be of zero length, as ="" , or equivalent value.]
4) LOGICAL [In many respects, behaves like a variant of NUMBER]
When any Excel functionality, including a cell formula, "tests" a cell, how that functionality responds depends in part on which of the above 4 states it is in. Therefore, some of us users would find it very helpful to able to control that state, via the proposed function.
[I note that some programming languages such as COBOL, [but not Excel,] have other special functions to indicate implementation-specific constants. A common one is "HIGH-VALUE()", which indicates a "pseudo-infinity" - the highest value that can possible be stored within a numeric field [variable]; this is immensely preferable to arbitrarily hoping, and specifying in a formula, that one cannot possibly exceed "99", "99,999", or similar.]
Kenneth Barber commented
Not the best name for the function. It makes me think that you want a function that returns #NULL!, much like how NA() returns #N/A.
Chris S commented
Like many others, ended up here after searching for a way to generate automatic sequences of XY line segment values. Excel 2016 has this difficulty, like all previous versions. I also noted that the presence of text values in a scatter XY series seems to force the plot to NOT be a scatter plot. From my perspective, the graphing problem is the big one - almost all "cell contents" problems can be fixed with alternatives either involving IF, or conditional formatting.
The reason this has not progressed is it's not possible without making fundamental changes to the API.
You can already return a Null value within a formula using either IF(1,) or a udf that returns Empty. Both return TRUE when passed to ISBLANK(). The corresponding API values are xltypeMissing and xltypeNil neither of which can be returned to a cell as a result.
A (dirty) workaround is to use a udf that changes the value of another cell via the EVALUATE function but this will complicate the calculation dependencies and recalculation.
Nigel Makin commented
The only NULL() we get seems to be on progress! This must be getting really embarrassing for the Excel Team [MSFT] now that MS Windows have got their act together and produced a good product in Win10, managing to join together all that legacy software? Perhaps they could lend you some programmers? More seriously, how about setting a target date for a NULL() fix?
Steven Fellows commented
I agree, we need a Null() function. In the meantime, to tell the difference between data, formula, blank, and no value, why not use something like this:
if there is text in the field that also counts as a null value, use:
Bhuvnesh Chandra commented
any updates on proper NULL() worksheet function ? -
Which would make calculations and charts treat a cell containing a formula as a blank cell, not as a cell with a text string ("") or as a zero.
I NEED a NULL() function. Not a workaround. End of story.
Come on MSFT get this **** figured out already. This is basic computer science and your user base is demanding this feature.
Ryan MacGregor commented
It has been over one year since MSFT's response that this feature is "planned."
Levi Bailey commented
@Stilez as mentioned in the comments by others on this suggestion and elsewhere, the NA() function (which returns the #N/A error) can often satisfy that use case.
Just to add a voice - I found this page when I was trying to solve similar issues as an experienced Excel user. I use formulae to pick out data for charting and to build further data ranges for further spreadsheet processing. But when there isn't data for some cell, I want the content to actually be ignored/blank/null, so that the content isn't confused with actual data that might be zero or need charting. So I can't just use numeric 0 formatted as an empty string. This affects lookups, comparisons, calculations, as well as charting. A NULL() function would be insanely helpful!
Erik Berger commented
I would agree with Conor O'Dowd (March 29, 2016 23:59), except it might be dangerous to change behaviour of the existing function/constant NA()/#NA. I could also agree with Harlan Grove (June 03, 2016 20:41), that #MISSING might be clearer than #NULL.
But anyway, I think the plotting option dialog should offer to take care of error values!
Erik Berger commented
Why not simply add "invalid" to the graph option dialog for treating hidden and empty cells?? I.e. "hidden, empty and invalid cells" -> treat as zero, treat as gap or interpolate.
Though I agree, the ability to create a true blank using a formula would be appreciated (as well).
This is so much more important now that we're importing excel data into Power Pivot and there are no ways to create null values using formulas. I can't think of any valuable reason why a string with no length is not null after converting to values.
When using VLOOKUP, sometimes the cell I'm looking for is empty, and I receive a zero output. Instead of creating long boring IF statements, IFEMPTY function might help :)
The structure should be the same as IFERROR.
Cheers ! :)
I like this idea for same reason as June 11, 2016 comment:
I use scatter plot to make shapes, and if I want to plot two shapes (say two unconnected squares) on the same series using one formula I must currently delete the formula creating a gap in the chart. I would prefer that "" or NULL() or similar act like an empty cell to the chart, let NA() still not impact the chart (also useful).
Jonathan Turner commented
Excel has been crying out for this functionality for a loooong time.
It is good to see this website, as previously there has not been a satisfactory place to request such a function, although I tried several times. However none of those requests were as comprehensive as Jon Peltier's. Thank you Jon for championing this issue. One which eventually raises its head for many heavy users of Excel.