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
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
-
First260
commented
Indeed, well from having worked closely with the C API, i have already tried to outline in previous comments why no function can return a true NULL currently and why more fundamental changes are necessary.
"Painting is very easy when you don't know how, but very difficult when you do" ~ Edgar Degas
-
Roy
commented
Oh yeah, no doubt. Two things would be nice:
1) A format one could set on a cell such that, if the cell were blank, either literally (even if used in the past it's now blank) or formulaicly (it's result is an empty cell, i.e., it displays nothing), then the cell would present to the rest of Excel as a true NULL, the same as a never-used cell presently does.
2) A function one could wrap arounda cell address or subformula elements that, if the data at that cell address, or the result of the subformula, were a blank/empty value, it would return a true NULL as its result either for the final result or as input into whatever other formula elements wrap it.
The first is what I figure would be the most useful for many things I do, especially for sorting. But the second would have a ton of uses too.
Unfortunately, I think they have to change something pretty low level in addition to anything else that seems logically likely. Consider: what process exists that leaves once used, but no longer used, cells marked somehow as having different contents (i.e.: having some contents even if actually empty vs. simply being actually empty) than a cell that has never been used? And variations in between, of course. And think of how many functions maybe rely on this difference for what they do. In fact, the reason for persisting this unfortunate circumstance might even be that some important functions would fail, as currently implemented, if the difference went away. Well, maybe.
But the two things above would go a long way toward solving every problem I ever had involving this sad "feature."
Just realized, subject to actual trial, but pretty likely, that SORT() on a cell address or a subresult in a formula seems likely to not be useful if the results could sometimes be non-empty and plural (an array of subresults) as it might actually sort them which might be undesirable in some circumstances, probably most circumstances. Still, even with SPILL functionality that might not be too often, unless they do get around to enabling lots of presently "unable to SPILL" functions.
-
First260
commented
An update for charting was provided 3 years back (see link further down.)
For formulas the goal should be to allow null results in cells like Google sheets does (ie a null data type). With the recent updates to the calc engine, now seems like a good time if this is ever going to happen.
-
Roy
commented
Say folks, not a socially connected fellow here so I have to ask: what's the present "type" for a five year anniversary? Isn't it "silverware" now? Like, knives would be silverware... The "Excel Team [MSFT] (Product Owner, Office.com)" comment telling us this is "PLANNED" (you know, like we "plan" to solve the Year9999 problem before 9999 AD, but... not tomorrow...) is coming up on its five year anniversary as the last lie we've been told. We don't want to miss that.
Not complaining... five years of silence after a lie is much better than occasional further mendacity aimed at stringing us along. MS's idea of "nothing"... which is still somehow different from a true NULL kind of nothing in which they wouldn't have told the initial lie either.
Yeah, looked it up. Silverware.
-
First260
commented
@Roy Maybe the opportunity to implement a blank return has finally arrived with the new Dynamic Array evaluation. Since an array containing blanks could only be returned to the sheet in Office365 it should not cause major backward compatibility issues.
For example both SORT(,) and UNIQUE(,) return {TRUE} when inserted into ISBLANK(.) and could therefore return a blank value to the sheet.
-
Roy
commented
Nope, sadly, it does not.
-
Anonymous
commented
mmm
Try using LEFT(1) of an empty cell it seems to work.
e.g.
=IF(C612="",LEFT($Z$1,1),DATE(RIGHT(C612,4),MID(C612,4,2),LEFT(C612,2))) -
Anonymous
commented
Support this request it's a real pain. In sheet it works someone told me.. I move data from sheet to excel because sheet don't meet some other needs and I would have to export the data from excel to sheet to benefit from that function and then back to excel before a final export to sheet for input into a google map what a pain. he REAL issue is within the PASTE_SPECIAL_VALUES_ONLY functionality. A checkbox option named like “Copy empty string values as blank cells” should be added. Thank you
-
Malcolm
commented
It should interface properly with Power Pivot which does have a null type, please.
-
TOGR
commented
PLANNED ·
ADMIN
Excel Team [MSFT] (Product Owner, Office.com) responded · November 05, 2015
Guys, is that planned since 2015?
Does it mean MS will never do that? -
Farid Tahery
commented
It doesn't have to be a function; it can be a predefined value similar to TRUE and FALSE that when assigned to a cell, clears its contents.
The fact that it was marked as "Planned" five years ago and hasn't been implemented yet, suggests there may be some technical obstacles. Here is an alternative suggestion to get a similar result:
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/40648261-add-a-void-worksheet-function -
Anonymous
commented
When I Import Excell table into Access I got message that not all records could be imported due to data format mismatch. To be more precise - Access assumes that "" is not a number and generates an error and a ImportErrors table with about 23,000 records (from 3 Excell fields). Also it imports 10,000 records even though most of the records i Excell contain formulas which result in "" and I don't want those records to be imported into Access.
-
Haden McAfee
commented
@Anonymous
If all you want is the APPEARANCE of a null value, you can solve your problem easily using conditional formatting. Create a conditional formatting rule that applies the custom number type of ";;;" (without the quotes) to any cell within the desired range that is equal to zero. This tells Excel to display a blank value. P.S. Can everyone stop antagonizing the developers? I really do want this feature, and I can't imagine us all coming across as a bunch of ***** will help with that. -
Anonymous
commented
It seems to me the latest comment by Microsoft development team intends to tell us why we don't need this null() function and can rely instead on what's already in Excel. As an initial Excel 1.3 user (yes excel 1.3 was for mac, maybe 20 ago or so), I believe I should be capable to do what the developers tell us. If the developers just tell me.
So please, developers, tell us: How do I do when I create a table where lines are records, and there can be many of them, and I plug in an intermediate result column a calculation result stating the price for something belonging to the line. But I want the cell to appear blank, rather than containing an ugly 0 if the line est not yet in use.
Today,when I want lt plug in such intermediate result cell and want such cell to be blank,I set in this cell that the result is "" if my test cel in this linel is blank. The test cell contains data if the line is in use, and nothing if the line is not yet in use.
This fails miserably when I want to use this intermediate result cell in a further calculation, because "" is not blank and Excel does not believe that it contains a 0. It just is not 0. So the calculation using a "" cell fails, in Excel. Bad luck.
So I plug in a 0 instead of a "" and state that the price format in this cell is price for accountants. Hence the cell will contain - instead of 0 if the calculation result is indeed 0. That works in further calculations, but is not a blank cell. It is ugly.
I want a blank cell, not a lousy - .
Excel developers, please tell me How to do without a null() result to my calculation.
Thanks.
-
First260
commented
For the record, MSFT did release an update in Sep 2017 that enabled #N/A to be treated as an empty cell in charts ( https://techcommunity.microsoft.com/t5/excel-blog/create-a-chart-in-excel-that-recognizes-n-a-or-blank-cells/ba-p/108453 )
Jon Peltier said the update fulfilled his main interest when raising this request ( https://peltiertech.com/plot-blank-cells-na-in-excel-charts/ )
The issue with the general request is that blank cells cannot contain formulas by design in Excel and, barring a breaking change to formula calculation, formulas that return blanks always get converted to values (0,"", or FALSE depending on context).
-
Vishwa Jay
commented
Come on... close to 5 years after hitting "planned" and it's still not here?
Just an =NULL() function name with a cell.ClearContent call if it should return as empty?
Doesn't seem that complicated, and it would be EMINENTLY useful for graphing functions and averages you don't want to count as a zero value.
-
Anonymous
commented
What the heck. When is this going to be added!?!?!?!?!?!
-
Roy
commented
A new thought on the value of a true null available to formulas...
That ignorant SPILL error. One doesn't always want a 12,000 element result to be fully displayed... Criminy.
One might just want the single "head of the beast" displayed so people know where it is, or perhaps 10 or 20 cells of it. One might place a blocking formula to end that display.
The idea being that the formula would work as intended, display what it can, put a little green triangle in the corner of the cell, and let us move on. I just created a SPILL formula that could fill 12,000 cells, or 37... do you think I didn't notice it didn't? Geez...
So... two types of blocking cells to consider:
1) Cells that, unfortunately, are just in the way. One COULD place an activator cell by the SPILL formula, like "Type "Show" to see full list." and if a person types "Show" (or, say, anything at all, just not an empty cell there anymore), then all the identified blocking cells could have been noted and fixed with an IF() wrapped around them that gives this NULL result and the SPILL formula would no longer be blocked, instead would fill there and right on past.
One can dress up cells around that which would distract with the hoary IF() with a "" result, but NOT those in the way of a SPILL formula. But a TRUE NULL could.
2) The other kind could be the opposite, not legacy or just awkward material but rather intentionally placed to limit the SPILL. Say I want the first 14 of those 12,000 cells to show. Or 33, Or however many someone types in a nearby cell for the purpose. Perhaps I place formulas below (or to the right, whatever is appropriate) which watch that cell with an IF() that results in perhaps a blank (just the formula with the "" result), thereby stopping the SPILL SPEW™, but that can shift if an appropriate entry is made in the activator (um, deactivator I guess) cell, shift to that true NULL so now the results can SPILL onward.
Various ways to implement that, from a few hundred cells under it with the formula, and X-number of them NULL out letting the display SPILL into them if "X" (value X) is typed in the cell, to slick ones. Even brutal approaches like 12,000 formulas and you go down to the row you want things to end at and type an "X" one column over. Slick or brutal though, the point is, when the condition is met, a given cell would be treated as a true NULL by Excel so suddenly the number of the results that are displayed would increase (or decrease back when cleared).
After all, by the way, MS stresses that the only place with an actual formula is THE cell the formula was entered in. The rest is just display. Hmm... even Conditional Formatting could have helped here if they'd only asked me first. But no, they had to pretend I didn't exist and just bull their way through...
But yeah, a true NULL could allow formulas that give a benefit by acting for all that Excel's engine knows, as if they didn't exist in a given cell. And that'd be handy with these SPILL functions.
-
First260
commented
@AC Wilson: There are various links that describe worksheet structure, one I mentioned before, another is here:
https://docs.microsoft.com/en-us/office/client-developer/excel/excel-worksheet-and-expression-evaluationTechnically Excel uses a variant structure (or tagged union), xloper, as specified through the C API. Note that by design nulls are not supported as formula results, but instead are converted to numeric zero.
There was also an update relating to nulls in charts since this request which is likely what the MSFT response is referring to and a blog post by Jon Peltier confirms this covers his main use case.
-
A.C. WILSON
commented
Dear Microsoft,
"So tired
Tired of waiting
Tired of waiting for YOU"
(written by Ray Davies, by/of/for The Kinks)