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]
142 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
-
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.
-
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.
-
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) -
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 "floating-point" 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.