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.
Thanks,
John [MS XL]
145 comments

A.C. WILSON commented
Dear Microsoft:
It's been almost FIVE YEARS since Jon Peltier (for whom I have great respect, and you should, too) suggested this, and you responded favorably. In Access, you have already had this functionality for years/decades. So, WHY NO EXCEL ACTION YET?While you're at it, you could also actually clarify  DOCUMENT FOR US USERS  how Excel's four (apparent) "states" operate (unless you are too scared/embarassed to expose just how "kludge"ey Excel's innards might appear). As I wrote in this thread several years ago:
'It appears to me that each Excel cell has four possible [undocumented] "states" [my own imperfect term, not Microsoft's]:
'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.]
'3) NUMBER [I don't know whether NUMBER is always "floatingpoint" or sometimes "integer"]
'4) LOGICAL [In many respects, behaves like a variant of NUMBER]' 
Anonymous commented
I too would very much like this feature. Currently never used cells have useful properties that can be very valuable, for example the fact they always appear at the bottom of a sort. It is very frustrating that once a cell contains any formula at all it is impossible to regain those properties, even if the cell is set to "", etc.
As Roy pointed out below, it would be great to "...sort a set of data and NULLs appear at the bottom of the sort result just like never used cells would rather than as a separate grouping" above those.

Ben commented
@Ivan I think you missed the point: The ability to skip over cells that are NULL as the result of a formula computation is a desired behavior.

Ivan commented
@Ben, Hi Ben. I think this is correct behavior because those cell are not empty.
In formulas you can use COUNTBLANK()=1 to distinguish empty cells from blank.So i still think that my proposal just enough.
Because with new formula there will be 3 formulas:
1. =NULL()
2. =ISEMPTY()
3. =COUNTBLANK()So ISBLANK() should be just renamed to ISEMPTY() and ISBLANK() behavior changed to same as COUNTBLANK()=1
I think this functions can be converted automatically when saving for old excel versions. Like it made with dynamic arrays.

Christoph commented
re.: Excel Team [MSFT] (Product Owner, Office.com) commented · September 11, 2015 01:02 ·
MS is fast asleep at the wheel.

Christoph commented
re.: Excel Team [MSFT] (Product Owner, Office.com) commented · September 11, 2015 01:02 ·
MS is fast asleep at the wheel.

A.C. WILSON commented

A.C. WILSON commented

Anonymous commented
I wonder...
Excel user since Excel 1.4 for mac, (yes, this was a while ago, Windows did not exist yet), over 5 years ago, I suggested that the Excel developers should add to Excel a null function to permit avoiding to accumulate lines or rows of zeroes whenever we, simple minds, create a calculation result in a line or column and want this calculation result to only appear if the line (or column) to which my cell belongs is, filled in.
This should be easy to do in Excel. It is, in other languages.
But me no buts, no more buts. Indeed, I see instead a bunch of butting "experts" making silly suggestions, which have all in common of only working in the specific example these "experts" show as if their suggestion was universal (which it never is. Never has been, never is, never will be).
Experts who desire to tell us how to live without a null fonction, please shut up !
Microsoft, please stop procrastinating. Please give us a null function.

Chaminou ze Khrompire 
Nigel Makin commented
I am surprised that this is taking so long to sort out. The bottom line is that it is needed to stop unwanted line charts dropping the line to zero. Let's just get it done!

Ben commented
@Ivan Does CTRL+arrow skip over those cells as if they were as empty as they appear to be? No. That's part of why we need a function which returns true NULL.

Ivan commented
Hi all,
Maybe i misunderstood the issue so please correct me.1. Empty string  nothing inside.
2. Blank  cell with value ="" and prefix considered as empty.So for the 1st case we already have ISBLANK() which returns TRUE for empty strings.
For 2nd case we have COUNTBLANK()=1 to check if cell is blank.
It confusing but it works well.
So ISBLANK() should be just renamed to ISEMPTY() and ISBLANK() behavior changed to same as COUNTBLANK()=1
In order to not break backward compatibility those functions can be changed automatically upon file opening.

Ben commented
First260: that does not provide the functionality of using some formula to flag "interesting" rows (or columns) with others as blank, and using CTRL+arrow to skip over the blank ranges.

First260 commented
Ben, for reasons outlined below a 'proper NULL function' is likely to be destined to fail.
But now with dynamic arrays one can replicate some of the functionality required,
For example if you want to enter in C1:
=IF(ISBLANK(A1),NULL(),A1)
You could instead enter this formula in B1:
=IF(INDEX({1,1},ISBLANK(A1)),A1)
Then hide column B
This ensures columns A and C will always match even where column A contains blanks 
Ben commented
CTRL+arrow jumps to the "edge" of contiguous datacontaining cell blocks. You can use this to skip large contiguous blocks of empty cells to jump to what may be relative anomalies. If a cell has a formula setting it up to contain a value if a certain condition is met, and be blank otherwise, this trick doesn't work and you have to manually scan/scroll through. A proper NULL() function should allow those blank cells to be skipped over in bulk.

First260 commented
Roy, I think you replied to a post that i retracted after some further investigation. It seems the bottom line is Excel team botched implementing Blank/Empty in the first place and there's really no good way out of the mess. In all versions of Excel the following all return TRUE when A1 is blank:
=A1=FALSE
=A1=0
=A1=""
=A1=IF(1,)
=A1=NaN()But the right hand sides of these equalities are not equal to each other which has the side effect that we have to handle each case separately in formulas. Does it really make sense to add a new value NULL() which is also equal to blank but yet another different value under the hood?
Note: NaN() should be equivalent to the IEEE special number NaN and can be defined as a VBA udf as:
Function NaN() As Double
On Error Resume Next
NaN = 0 / 0
End Functionbut actually displays as a number. Also mathematically speaking the equals operator is nontransitive and hence not an equivalence relation due to this definition of blank.

Roy commented
A null value would be different than the current blank.
"Blank" being what you get if you, for example:
1) Enter ="" in A1, the copy and paste values back on it, or to some different cell
2) Have a formula in A1 that returns "" as its result.
3) Have some value in A1, then select it and hit Delete or Clear Contentsand some other things.
Anyway, Excel is currently able to distinguish between a cell that has a history but is currently blank in the English language sense of blank: literally nothing entered in the cell, and those cells which have never, ever, had a value in them. So it should currently have no issues distinguishing, in program, between types of "blank" or "empty" it encounters.
Alright, you have A1 which has never had an entry, ever. Presently, "=A1" would return a 0 in whatever cell or formula it is in. "=ISBLANK(A1)" would return TRUE. Put "=F10" into A1 and now A1 would return 0, =A1 would return 0, but ISBLANK(A1) would return FALSE (after all, there's a formula there now). References to any of these cells would produce currently expectable results.
Now delete the entry in A1 and you're back to the original. However, Excel can still see that though it is blank, it has been used and will act accordingly, saving information for it and doing things like sorting it into a different place than it would if it had never been used. (Ever delete the last 10,000 rows of a data set and find CtrlEnd still sends you to the last cell in them? That is Excel still realizing they've been used. Just blank, not empty.)
Now close all that and open a new spreadsheet, then put the formula I mentioned in B1 (assuming a NULL() function now exists). Excel would return a truly empty cell result in B1, not a zero. That result would be precisely what is thinks about A1 itself since we have never entered anything there and it's really empty. So =A1=B1 would return TRUE as the value of the never used A1 and the NULL() function's result of a true null is the very same thing. If you enter =B1 in D1, D1's result would be a true null (as in "empty, never used") as well. So "empty" from your choice of that or zero.
Notice the effect of the formula was to pass onward a null value for B1 even though it has a formula in it. Of course, there are functions in Excel, like FORMULATEXT(), that need to return the underlying material, but they already are programmed to ignore the result and evaluate the underlying material. A Table would also have to look at it this way, else one might have odd effects if a formula in the first cell of a row looked utterly unused to the Table. Or maybe not. But whacky things like that could crop up with issues. Don't mind a wait while things like that are gotten right. DO mind a wait if it's just "you're only 671 people out of our 800,000,000 person user base so..."

Roy commented
Well, first off, the current situation has sucked for 30 years. I'm completely willing to spend the next wrapping "=A1" to get "=IF(ISBLANK(A1),NULL(),A1) and have a result that Excel treats as it does never used cells. NOT A PROBLEM dude.
Second, Most of us asking for this almost certainly do NOT have any misunderstanding about the return and therefore how we'd have to use it.
Third, part and parcel of any real solution would be for MS to treat any blank as it treats never used cells. Presumably, it stores only a cell address and value (in a simplified version of things) and would simply have to not store such if blank. "Blank" = empty: either never used, or contents deleted or cleared. "0" would need to be treated as an actual value, not all the absences of a value as well as an actual entry of "0". Since what's stored likely includes more information than I imagine, things used internally, not just formatting and values and such, the simple version is unlikely and Excel would have to make provision for a value of NULL and handle it however works best for them. Then deleting/clearing contents would set that value, not just leave no value at all.
But fourth, we'll all of us be pretty happy if they just work out the FUNCTION asked for rather than a fuller solution. Nice if they did the whole enchilada but if we could just have the function and Excel acted properly (or at least "as expected") on its use, we'd be really happy.
"Properly" ("as expected") would mean things like sort a set of data and NULLs appear at the bottom of the sort result just like never used cells would rather than as a separate grouping, above those.
Also, considering sorting, it is clear Excel can already distinguish between current NULLs (never used cells) and blanks, but also it can clearly already distinguish between 0's and blanks as well. So since it can already distinguish all three "values" and so MIGHT be able to already do the underlying work the function would need.
No one's even asking for perfection, just better, by this much.

First260 commented
While this would be nice in theory I think a lot of people have missed the fundamental problem that the formula =A1 returns zero if A1 is blank which means there is no consistent / backward compatible way to have a formula return a blank or null.
Google sheets on the other hand will return empty in this case which opens up that option.In effect you'd need one return for null and another for blank/empty which would just add more confusion if it displayed as empty as well.

DS commented
I want a proper NULL() so that if I have a formula in cell B2, text from A2 will spill into B2 instead of being cut off. I need this for a technique where sheet A does calculations and Sheet B refers to Sheet A with formatting and conditional display of data, but blank cells don't behave properly, hence the need for NULL()