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.
Jon Peltier
shared this idea
![Excel Team [MSFT]](https://secure.gravatar.com/avatar/b634bb7fd5647ddb4ea275bcbe43d32a?size=40&default=https://assets0.uvcdn.com/pkg/admin/icons/user_70-62136f6de7efc58cc79dabcfed799c01.png){kind=icon}
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]
![Excel Team [MSFT]](https://secure.gravatar.com/avatar/b634bb7fd5647ddb4ea275bcbe43d32a?size=40&default=https://assets1.uvcdn.com/pkg/admin/icons/user_70-62136f6de7efc58cc79dabcfed799c01.png){kind=icon}
30 comments
-
First260
commented
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(ISBLANK(A1),TRUE,IF(ISFORMULA(A1),TRUE,FALSE))if there is text in the field that also counts as a null value, use:
=IF(ISBLANK(A1),TRUE,IF(ISFORMULA(A1),TRUE,IF(ISTEXT(A1),TRUE,FALSE))) -
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. -
Steve
commented
I NEED a NULL() function. Not a workaround. End of story.
-
Steve
commented
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.
-
Stilez
commented
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! -
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). -
Michael
commented
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.
-
Anonymous
commented
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 ! :)
-
PiRho
commented
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. -
Daniel Smith
commented
When plotting values on an xy chart with lines joining points , a completely blank cell in the y column causes a gap in the drawn line. This can be very useful, for instance when plotting a set of shapes or a map. However there is no way of making a formula in the cell cause a gap because the cell is then not completely empty. Suggestion is to fix this by providing a new worksheet function which returns a value which looks like an empty cell to the chart plotting logic.
-
Harlan Grove
commented
Since NA() becomes #N/A when converted to static value, what would NULL() become? Nothing? #NULL!?
I understand the terminological appeal of 'NULL', but MISSING() and #MISSING (need a constant to go along with the function) would be less ambiguous, conform to stats package terminology rather than DBMS terminology.
-
Conor O'Dowd
commented
Or... you could jut change the behaviour of NA() to be the same as the proposed NULL(). I always thought that was what NA() was supposed to be. It amazes me that it is interpolated in an X-Y or line plot, that's plain silly.
-
Lionel
commented
I really need this NULL() function to construct a database I will use with PowerPivot and build powerpivot tables.
If I use in column A the formula IF(isempty(B1), "", Month(B1)) for example, with dates in B1, it's OK for Excel to recognize the whole column A as numbers, but... not for PowerPivot !
Indeed, PowerPivot won't accept a column of numbers with some "", because it's a text value, not an integer value.
I am currently stuck with this problem every day. If I put 0 or NA() instead of "", it doesn't work neither in PowerPivot to recognize the column as numbers... so I have to copy and paste my formula as values and replace the "empty" cells by... nothing so that PowerPivot accept the values.
Any suggestions really appreciated :-) -
Anonymous
commented
Peltier really nailed it on the head. There a few work around for most of the scenarios where the null fxn would be used but many of them are incredibly tedious, unintuitive, and heavily vba based.