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]
62 comments
-
thedoctrine101
commented
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 COUNTBLANKANDEMPTY(), 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 COUNTBLANKANDEMPTY() 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.
-
Russell Richter
commented
I have a formula that evaluates to TRUE/FALSE/null in Excel. That Excel file is linked to Power BI. Power BI reads that field as Boolean, and converts all the nulls to FALSE, which is not correct. Nulls in a Boolean field are neither TRUE nor FALSE. So, the more we feed other systems with Excel files, the more we need a NULL value.
-
Jaime Segura
commented
Have you noticed that COUNTBLANK and ISBLANK react different with cells containing ="" ?
-
Ed Hansberry
commented
PowerApps has a BLANK() function as well. It would seem to make sense to add this to Excel for consistency, and perhaps a NULL() in both, though in PowerApps, it treats null and blank as the same.
-
Lz.
commented
Yep, can an Admin merge this suggestion with https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/17265644-null-worksheet-function ? Please
-
Chibi Beru
commented
Please merge this request with the "NULL worksheet function" one. This would raise its votes number
-
Anonymous
commented
How can this NOT exist? Excel has been around for 82913487293847 years, and it is amazing that I am still having this problem! An XY Chart treats a "" cell as zero not NULL. Null is the functionality I am looking for and need (simply by deleting the contents of the cell). Ugh...
-
anon
commented
I just spent hours fighting with this exact problem. I just need a way to make a cell return NULL so when I copy a range of cells (some with values, some without), those pseudo-blank cells don't break my DATEDIF formula referencing those cells when I paste as values.
So something like creating some sort of null function like this: =IF(ISBLANK(D2),NULL(),"F")
-
Anonymous
commented
I'm in the same boat as David Johnson - I want to be able to put a formula in a cell that, when it comes back with a NULL result, will not block text from an adjacent cell.
-
Anonymous
commented
And for Excel for Mac OS, please!
-
Anonymous
commented
It's been three years - where is our function?
-
Lori
commented
@Roy, Hilarious :) I don't deny a general NULL() function would be useful. Adding linus minus' suggestion to the list i compiled on the other user voice on this topic, applications include:
- Functions (ISBLANK, COUNTA, ...)
- Objects ( Charts, Sparklines, PivotTables)
- Actions (Sorting, Filtering, ...)
- Connectivity (Access, SQL Server, ...)
- Formatting (Text overrunning cells)
- Selections (Ctrl+Arrow keys, Goto Special or Current Region)But it seems MSFT have gone down a one way street that requires context specific workarounds as has been done for charts and sparklines.
If you really want to be able to produce a general null result from a formula use google sheets instead who by many accounts made the wiser design decision in this respect.
-
Roy
commented
Especially to Lori, but in general:
The USE CASE is needing a cell without data to BE a cell without data to EVERYTHING.
Until that is the case, nothing has been solved.
And if you want to use the result of "nothing" as valuable data to what you've written, then don't add the NULL() wrap.
Or... with luck, the null would be a complete, true, default in all cases blank and you would set your flag to have it be non-blank... sigh... no, that last would break tens of millions of in use spreadsheets. But the rest of us deserve this and future spreadsheets could use it instead of old workarounds thereby spreading world peace and allowing all children to go to bed by 9:30pm so the beauty contestant queens could finally be happy and choose other answers. This would benefit the world!
-
linus minus
commented
I constantly struggle with date format columns that need to stay true dates so that the data filters work correctly. If the dates are provided by a lookup, then whenever there is no matching date, we have to either insert a zero or a double-double quote (what I'll call fake-null). Neither are useful. The fake-null makes the data filter treat the column as text, the zero makes sorting a pita now that we have lots of 1900s gumming up the works. And don't get me started on conditional formatting please and macros are not an option either. The NULLIFY MOVEMENT begins HERE!
-
Lori
commented
Jon Peltier says in a recent blog post that the recent update for Excel 365 largely solves the use case that this suggestion was intended to address (https://peltiertech.com/plot-blank-cells-na-in-excel-charts/)
MSFT likely took this route as allowing a true blank cell return from a NULL() function (wrapping another function or not) probably adds more complications than it solves.
-
Roy
commented
@Lori:
He wants a FUNCTION(), something we can wrap other functions in and produce a NULL if the output of the wrapped formula is "" (with perhaps an option in the NULL() function itself to do so if it is "0" also?), OR to give as an element of something like the IF() function.
NOT looking for a general underlying change to Excel so that all such give a true null as the default. Everyone understands a 30 year base cannot permit that kind of approach.
Actually, a middle between the nice first part and the rather extreme general default might be a page option (why can't "pages" be generally formatted directly rather than by selecting all first?) that works like the display 0's option. Then charts could act upon the data as displayed (blanks treated as nulls, if so selected), regardless of the literal cell non-content. Or not. An option in setting up the chart.
(That could also be done solely in the chart's options, but as a page display option, it could let Excel treat non-content cells as nulls WITHOUT having to wrap results in a NULL() function. If one cannot use that due to 30 year base constraints, one simply wouldn't.)
-
Lori
commented
@Roy: i don't see any problem with a built-in function that returns null / empty per se but it's of limited use as you can't currently return a null value as the end result of a formula eg for use in chart source data.
There is a common assumption that if a cell is blank then it does not contain a formula eg goto > special > blanks. And for those of us that have to support sheets especially those developed by others we often value backward compatibility above new features.
The OP may have had an alternative value in mind that would be rendered as blank by charts (as is now possible with #N/A) and is backward compatible. However it wouldn't behave like null in other formulas like COUNTA.
-
Roy
commented
@Lori:
How would this break anything for you? YOU simply would not use it. The rest of us would.
Instead, you'd have the rest of us contort ourselves, making UDF's and having to overcome VBA-resistance in many places we send spreadsheets to? Making poor folk doing charts continue to use workarounds for the reason that you'd have to use it because it existed instead of simply... not using it yourself?
I am clearly missing the logic here.
-
Lori
commented
Please don't do this - I don't get why this has so many votes. Clearly a design decision was made long ago not to allow null as a formula result (probably for lotus compatibility) and this is a fundamental part of the application design.
So if A1 is empty entering =A1 in another cell returns 0 and not null.
We can also easily create a null function from vba code with
Function Blank(): End Function
So that =ISBLANK(Blank()) is TRUE. When entered as a formula the null is just converted to zero in the result.
Any change to allow null as a formula result would break many sheets. The recent update to show #N/A in charts covers the most needed case.
-
Paul van der Wagt
commented
Any news? I still can't find it in excel 2016