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]
@Roy, Hilarious :) I don't deny a general NULL() function would be useful. Adding linus minus' suggestion to the list i compiled on the other user voice on this topic, applications include:
- Functions (ISBLANK, COUNTA, ...)
- Objects ( Charts, Sparklines, PivotTables)
- Actions (Sorting, Filtering, ...)
- Connectivity (Access, SQL Server, ...)
- Formatting (Text overrunning cells)
- Selections (Ctrl+Arrow keys, Goto Special or Current Region)
But it seems MSFT have gone down a one way street that requires context specific workarounds as has been done for charts and sparklines.
If you really want to be able to produce a general null result from a formula use google sheets instead who by many accounts made the wiser design decision in this respect.
Especially to Lori, but in general:
The USE CASE is needing a cell without data to BE a cell without data to EVERYTHING.
Until that is the case, nothing has been solved.
And if you want to use the result of "nothing" as valuable data to what you've written, then don't add the NULL() wrap.
Or... with luck, the null would be a complete, true, default in all cases blank and you would set your flag to have it be non-blank... sigh... no, that last would break tens of millions of in use spreadsheets. But the rest of us deserve this and future spreadsheets could use it instead of old workarounds thereby spreading world peace and allowing all children to go to bed by 9:30pm so the beauty contestant queens could finally be happy and choose other answers. This would benefit the world!
linus minus commented
I constantly struggle with date format columns that need to stay true dates so that the data filters work correctly. If the dates are provided by a lookup, then whenever there is no matching date, we have to either insert a zero or a double-double quote (what I'll call fake-null). Neither are useful. The fake-null makes the data filter treat the column as text, the zero makes sorting a pita now that we have lots of 1900s gumming up the works. And don't get me started on conditional formatting please and macros are not an option either. The NULLIFY MOVEMENT begins HERE!
Jon Peltier says in a recent blog post that the recent update for Excel 365 largely solves the use case that this suggestion was intended to address (https://peltiertech.com/plot-blank-cells-na-in-excel-charts/)
MSFT likely took this route as allowing a true blank cell return from a NULL() function (wrapping another function or not) probably adds more complications than it solves.
He wants a FUNCTION(), something we can wrap other functions in and produce a NULL if the output of the wrapped formula is "" (with perhaps an option in the NULL() function itself to do so if it is "0" also?), OR to give as an element of something like the IF() function.
NOT looking for a general underlying change to Excel so that all such give a true null as the default. Everyone understands a 30 year base cannot permit that kind of approach.
Actually, a middle between the nice first part and the rather extreme general default might be a page option (why can't "pages" be generally formatted directly rather than by selecting all first?) that works like the display 0's option. Then charts could act upon the data as displayed (blanks treated as nulls, if so selected), regardless of the literal cell non-content. Or not. An option in setting up the chart.
(That could also be done solely in the chart's options, but as a page display option, it could let Excel treat non-content cells as nulls WITHOUT having to wrap results in a NULL() function. If one cannot use that due to 30 year base constraints, one simply wouldn't.)
@Roy: i don't see any problem with a built-in function that returns null / empty per se but it's of limited use as you can't currently return a null value as the end result of a formula eg for use in chart source data.
There is a common assumption that if a cell is blank then it does not contain a formula eg goto > special > blanks. And for those of us that have to support sheets especially those developed by others we often value backward compatibility above new features.
The OP may have had an alternative value in mind that would be rendered as blank by charts (as is now possible with #N/A) and is backward compatible. However it wouldn't behave like null in other formulas like COUNTA.
How would this break anything for you? YOU simply would not use it. The rest of us would.
Instead, you'd have the rest of us contort ourselves, making UDF's and having to overcome VBA-resistance in many places we send spreadsheets to? Making poor folk doing charts continue to use workarounds for the reason that you'd have to use it because it existed instead of simply... not using it yourself?
I am clearly missing the logic here.
Please don't do this - I don't get why this has so many votes. Clearly a design decision was made long ago not to allow null as a formula result (probably for lotus compatibility) and this is a fundamental part of the application design.
So if A1 is empty entering =A1 in another cell returns 0 and not null.
We can also easily create a null function from vba code with
Function Blank(): End Function
So that =ISBLANK(Blank()) is TRUE. When entered as a formula the null is just converted to zero in the result.
Any change to allow null as a formula result would break many sheets. The recent update to show #N/A in charts covers the most needed case.
Paul van der Wagt commented
Any news? I still can't find it in excel 2016
Zahra Badaroudine commented
Any news on this?
Youssef Wagdy commented
Frankly I would love to simply see the option to return an NULL value similar to how you can use FALSE or TRUE in a logical formula.
Nigel Makin commented
So sloooooow and no news ???
Any progress on this? I would like to do the same thing. Could use a simple term to enter a null value into a cell so it doesn't get graphed. Using an IF statement, any value entered in the cell returns a graphable result - even text. I've had some luck with #N/A but it only works if there are a string of cells with that value. it doesn't work correctly when alternating cells with that result because it will graph all of them. Thanks.
David Johnson commented
My want for this function would be to have a return value from a formula that doesn't 'chop off' the text from the cell to the left.
... 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.