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
Thanks for your support for this suggestion. As some of the comments have concluded, there are a few complex technical issues and considerations that have delayed this from being implemented.
We do think that this suggestion has merit but we don’t think that we’ll be able to devote time to it in the near future.
One of the primary reasons for this request was to have a better way to handle formula cells in line charts, and this can be accomplished using the option to show #N/A as an empty cell. You can use the NA() function in your formulas as appropriate, and choose the option to “Show cells with #N/A as an empty cell”. To find the option for your chart, go to the Select Data dialog for the chart, and click the Hidden and Empty Cells button.
We know there are other scenarios where a proper NULL() function would be useful, and we’d like to keep hearing your comments. We’ll keep this suggestion open for voting as well.
Thanks,
Steve [Excel team]
133 comments
-
Joe
commented
> there are a few complex technical issues and considerations that have delayed this from being
> implemented.Delayed for 5 years. Sure, MS, take your time.
-
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!?!?!?!?!?!
-
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.