NULL Worksheet Function
This request is to add a new function, =NULL(), which returns "blank" or null, forcing the cell to act as if it truly is empty; not a cell with an empty text string "" or 0.
Be sure to vote here too:
The good news is that even after 3½ years, they STILL plan to do it, that hasn't been downgraded! Talk about slim threads but... Hope Lives!™©℠®♪
(Maybe that last one♪ will get the RIAA to take our side. Tenacious folk! Even Disney, the original Lawyers-Я-Us™©℠®♪, doesn't try throwing lawyers at THEM.)
Rather than changing 50 functions, or only someone's half dozen leaving the others to hobble on, we need a true NULL.
Just that, and it's all done.
If I will populate a region using formulas, then PASTE|SPECIAL|VALUES to clean the formulas out, but need cells to really be empty (NULL's), I currently have to use a string the data would never contain like "MMM" instead of "" and Find and Replace afterward with nothing. THEN I finally have a true NULL back in the cells. (Ugh, don't even get me started on how, when first doing this, decades ago, I figured the string "***" would NEVER be in any of my data... great, never was, but then the boss sent one of my spreadsheets to someone outside the company...)
I have needed this for DECADES now. Plural. I am not interested in tinkering with a function
or two, maybe half a dozen popular ones. I need this for sorting. I would have used more dynamic formulas a LOT earlier if this had not been an issue, but it's mainly for sorting.
Do you have the least idea how much time I've spent over the years JUST answering and explaining to users of the spreadsheets (bosses mostly) why sorting did not work out as expected, how to solve that, etc? Every second wasted and often left a suspicion in their minds that the spreadsheet was flawed, so it could be VERY flawed.
Not to mention the time wasted with the "MMM" and F&R.
But it's NOT limited to sorting something you've done and maybe could have done differently (somehow, when it's YOU telling ME how I should choose a different method, that's all fine, but if I tell YOU some other way is better, there might be a million reasons you would offer why not, but usually people settle on "I don't want to do it differntly, I want to do it this way," and that is sufficient... but it just meant I was stupid when I used it — not pointing fingers at anyone here!, just sayin').
No, not limited to those at all: Importing data. Nulls in, say a CSV or TXT file, come in as "" type entries. So EVERYONE importing anything in which that can occur, then adding data, ends up with the problem. Again, the sorting issue. Cell counting issues. How many items in a column. And so on.
Unless the import is sorted, column after column, the collection of NOT NULL's found, and deleted so you have true blanks, true Null's. Up for a little bit of work today boys? Importing 38 columns with this one. Sort and so on away me hearties!
So I need a true NULL available in formulas AND in the data import process. Once I have it, no function will give me any trouble. For anything legacy in all the spreadsheets out there, until it is upgraded (or until forever, eh?), there's still good old "". It is ALREADY in ALL those legacy works, so nothing will be broken.
Now, from that angle, I do understand the let's tinker with my favorite function approach. Instead of upgrading one's work, which admittedly might never happen, I imagine lots of folks would like their favorite don't-work-functions to suddenly work. Nice, for that I guess. Nightmare for most of us and not on point for the sorting and data import aspects at all. But I guess I get it from that slightly askance viewpoint.
Give me a real NULL, available in formulas, and as a value for a NULL in imported data. Give me PRECISELY NOTHING. That's all I ask for: PRECISELY NOTHING. And lots of it.
There is an issue inherent in Excel, where the line is blurred between the definitions of the words EMPTY, BLANK, and NULL. So as to be clear in this matter, I will make the words BLANK and NULL synonymous, both meaning TRULY BLANK, and EMPTY will mean empty string, such as ""
We all know that an empty string, being "", is NOT equivalent to a TRUE BLANK OR NULL CELL as per the ISBLANK() function. When a formula in lets say cell E3 returns an empty string as a value, based on a condition, for example in an IF() function, then, when cell E3 is Copied and then Pasted_Special_Values_Only into lets say cell E7, then cell E7 will NOT be truly blank, and Excel will consider cell E7 as containing a value, which in this case is an empty string. However, there is still confusion within Excel programming, since the COUNTBLANK() function does count empty strings "" as a blank cell, it is therefore counting TRUE BLANK CELLS AND an empty strings as blank in its count.
In other words, Excel treats a cell containing an empty string as blank in the COUNTBLANK() function, but does not treat it as a TRUE blank cell for everything else. Like when browsing through scattered visible values in the column of a long list using CTRL-DOWN/UP ARROW KEYS. When doing that, it will ignore the cells in that column containing actual visible text, we will call them visibly valued cells, as it will simply go strait from top row to bottom row when toggling between CTRL-UP and DOWN. If the cells in that column found between visibly valued cells were actually TRULY blank, then toggling between CTRL-UP and DOWN would go to previous visibly valued cell and then to the next and back and forth so on, as per designed.
Now, by design, is it intended for Excel to treat empty strings as different than truly blank cells? I think it is, and it should treat empty strings as different than truly blank cells, but that also means that they should either change the name of the COUNTBLANK() function to either something like COUNTBLEMP() (for blank and empty), or dichotomise the function into two separate functions, something like COUNTBLANK() for TRULY BLANK cells, and COUNTEMPTY() for cells containing empty string as value. Or even better, have all three functions available, so COUNTBLANK() for truly blank cells, COUNTEMPTY() for empty string cells, AND COUNTBLEMP() for counting both types of cells.
Are there any workarounds for this? Answer is YES, and I shall provide examples.
E2=IF(C2=2, "Cell C2 equals 2", "NULL")
Now if you use that kind of formula dynamically in a column to downfill a long list, like nested within a VLOOKUP() formula for instance, then you will see that column filled with the actual word NULL as text value mixed in with some scattered true data values. This way, it will be easy to convert the cells containing the word NULL as text by applying a FILTER to that column, selecting to view only the cells containing the word NULL as text, and then easily selecting all the cells containing the word NULL as text, and then hitting the DELETE key, rendering them TRULY BLANK. I use the word NULL as a personal preference, but one can just use any word they want, like TOEMPTY or whatever.
Is a NULL() or TRUEBLANK() function truly necessary? I say it couldn’t hurt to have it, BUT, the 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. There is a Skip blank option, but that does not work.
Chibi Beru commented
This request should be merge with the "Give us a proper NULL() worksheet function." request.
Null would be incredibly helpful in a number of situations, and would dramatically reduce the number of times I need to be writing "", which can be confusing.
@anonymous. The request is actually quite vague and has numerous interpretations. A NULL() function could be applied to many different contexts including:
- Functions (ISBLANK, COUNTA, ...)
- Objects ( Charts, Sparklines, PivotTables)
- Connectivity (Access, SQL Server, ...)
- Formatting (Text overrunning cells)
- Selections (Ctrl+Arrow keys, Goto Special or Current Region)
Many of these have been suggested in this and related forum posts.
A general NULL() function would need to return a result identical to empty cell to apply to each context which is possible in Google sheets but not Excel.
My main use would also be functions and the NAN() suggestion is a standard representation for a missing value that also preserves numeric type. Many functions would have to be modified whatever the value chosen to represent null, resulting in other possible issues.
@Lori I don't think you or Robero Mensa actually understood the original request properly.
Nobody is suggesting changing existing behaviour, but creating a worksheet function that returns a value that ISBLANK would perceive as an empty cell.
Your statement that "To be consistent Null would need to be returned from any formula that returns a reference to an empty cell as is done in Google sheets and also from =IF(1,)." would create many more problems than it solves. Worse, if that were the case, the NULL function wouldn't be needed to begin with, as you're outlining the main use case - to return from an IF statement as though the cell were empty to begin with.
The issue is that there's no standard way to return a "don't know" value from a formula. This is especially useful in scientific calculations, where you may have incomplete data sets or are trying to work with only partial data.
Introducing a NULL() function seems problematic as chart guru Roberto Mensa says further down.
To be consistent Null would need to be returned from any formula that returns a reference to an empty cell as is done in Google sheets and also from =IF(1,).
Excel allows nulls within the formula eg =ISBLANK(IF(1,)) returns TRUE but converts null to zero in the formula result. And changing this behaviour would not really be feasible for legacy reasons.
As an alternative I'd be in favour of a NAN() worksheet function to represent a missing value.This would be displayed as 'NaN' (right-aligned), as is common in other languages like python. NaN can be returned from a udf (by returning a double with all bits set to one) and is compatible with power query. It is also the only value AFAIK that is shown as empty in charts (https://newtonexcelbach.com/2018/03/31/plotting-charts-with-gaps/)
And consolidate the votes?
A.C. WILSON commented
Re Erik Berger's comment below - "Hm, then why not simply call it =EMPTY() ?"
My primary reason to NOT call this thing "EMPTY()" [nor "BLANK()"] would be that my life is simpler when the same functionalities in Excel and in Access have the same names. But, this would not be a deal-breaker for me.
What to name this thing is a problem primarily because Microsoft's writings have been very vague about both the vocabulary to describe, and the behavior of, a cell's "state" [my own imperfect term, not Microsoft's].
It appears to me that each Excel cell has four possible "states":
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.]
4) LOGICAL [In many respects, behaves like a variant of NUMBER]
When any Excel functionality, including a cell formula, "tests" a cell, how that functionality responds depends in part on which of the above 4 states it is in. Therefore, some of us users would find it very helpful to able to control that state, via the proposed function.
[I note that some programming languages such as COBOL, [but not Excel,] have other special functions to indicate implementation-specific constants. A common one is "HIGH-VALUE()", which indicates a "pseudo-infinity" - the highest value that can possible be stored within a numeric field [variable]; this is immensely preferable to arbitrarily hoping, and specifying in a formula, that one cannot possibly exceed "99", "99,999", or similar.]
Nigel Makin commented
See also "Give us a proper NULL() worksheet function." and add a vote!
A.C. Wilson commented
YES. I've wished for this for ~10 years. Access already has this.
Larry Caretto commented
I support the idea of a cell value that forces "the cell to act as if it truly is empty". I have had problems when I tried to use IF formulas that returned a null string ("") that got counted by COUNTA. I did not expect that. One possible problem is that there may be some users who do want COUNTA to count such cells. Would it be necessary to have a new or modified COUNTA that could give users the option of ignoring or including NULL cells in their count?
I fully support Chris Gross [MSFT] and the suggestions by Shirley Moreman, Daniel Smith, David Jansen, and John (among others below) all speak to the need to have a way for a formula to produce a null cell equivalent.
To those concerned that they would not be able to tell the difference between truly empty cells and those with formulas that return null, I suggest the following. I would think that in VBA, the HasFormula would return true, and to view which cells have formulas, use the Formulas toolbar and select `show formulas.'
Brian observed that though it adds complexity, in some cases it is the only solution, or the -by far - simplest solution.
I do not see a concrete objection other than making things more complex. [Things should be absolutely as simple as they can be..., but no simpler - A Einstein]
David Jansen commented
I would like text in one cell to write on top of an adjacent NULL() cell as though it were truly blank. Currently, if a formula results in an empty cell, Excel doesn't let text from a nearby cell write on top.
For me the value is in creating Gantt spreadsheets in Excel. I use conditional formatting to fill cells that are within a relevant date range for a task. I would like to label each of those tasks on the left-most cell; however, the cells are too narrow (column width = ~3) and this requires an equation in each cell. As a result, the label is restricted to 2 characters or so before it is cut off. (Frequently changing zoom prohibits chopping the text into adjacent cells)
Corey Becker commented
Daniel's suggestion is an issue with chart's. Charting should be updated to allow that behavior. I use the error technique personally and all my sums ignore errors. Adding a null function seems like a workaround to fix real issues. We should just fix the issues you're trying to workaround.
I agree with the last two comments that more ways to make a cell empty will add complexity. However, this is absolutely necessary in certain situations. Please see the comment by Daniel smith below.
Agree with others. It doesn't make sense in a spreadsheet. We will then have more options for "empty" cells, not less. It could be 0(formatted to not display), empty string, " "(Spacebar - people love to "delete" using spacebar), =NULL(), and actually empty.
Corey Becker commented
It seems that there are probably much simpler solutions to the examples listed below.