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.
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.
John [MS XL]
Corey Becker commented
It seems that there are probably much simpler solutions to the examples listed below.
I NEED a NULL() function. Not a workaround. End of story.
Come on MSFT get this **** figured out already. This is basic computer science and your user base is demanding this feature.
Kenneth Barber commented
I disagree with the "as if it is truly empty" part. Then you'll have a harder time knowing that that cell actually contains something. People making the font colour match the background is bad enough. We don't need a formula to hide itself so thoroughly.
roberto mensa commented
null value in excel does not exist. A function that returns emprty is unacceptable. How can we distinguish between an empty cell and containing a formula? In the charting it would be handy to have a leap in the lines when the value is "" ... I would not add a new function, would change the behavior in chart object.
I have speed hours identifying and working around problems detecting empty/blank cells in excel and excel vba. A reliable null() function will definitely help productivity.
Ryan MacGregor commented
It has been over one year since MSFT's response that this feature is "planned."
Charting is not my need for a NULL() function. Complex formulas are. I often read data from databases etc where there are missing data. To create formulas based on other formulas etc and there's a blank entry I have to create unwieldy IF formulas that create and compare empty strings. This is necessary to avoid errors showing up. I make extensive use of Excel to prepare data tables for JMP. The presence of #err codes messes things up. In short, the absence of NULL() makes things complicated, frustrating, and error prone. JDI (just do it :) ) - please.
My reason for wanting the NULL function is the same as Shirley's. Why can't MS just add the function?
I often refer to the same cell with a variety of string numeric and testing functions where some are resolved by an empty string ("") others by zero (0) and yet others by a forced error, but none of these resolves all of my other functions. This results in the need to create helper columns, overly complex formulas or VBA solutions (VBA is not allowed in many business environments).
A proper NULL function or constant would resolve every one of these issues in a large proportion of my projects and noticably reduce the size, complexity and calculation speed of said projects
I agree with Shirley. The NULL function (or even a global constant - like Power Query) would provide a more useful solution when using aggregate functions, filtering and other operations where Excel makes a distinction between a truly blank cell and a cell with a zero-length string.
Stephen Eddleston-McGrath commented
I also fully support this idea. It is a much more comprehensive solution than just correcting chart behaviour. Shirley's example below is just one of the other uses that this could have. As mentioned by Daniel, allowing null values to work as in calculations (and return a null value) would hugely improve average calculations in complex financial models. Having to programmatically account for division by zero errors is a ballache at the best of times. If there is no data, the calculation should be allowed to return null.
Erik Berger commented
Hm, then why not simply call it =EMPTY() ?
BygAuld Byrd commented
Fully support suggestions by Shirley Moreman and Daniel Smith.
Eugene Kow [MSFT] commented
Agree w/ Shirley as well, I would prefer to us COUNTA and currently use COUNTIFS with multiple conditions as a workaround + a combination of conditional formatting to hide the cell text...
Joseph Byrne commented
I agree w/ @Shirley.
Daniel Smith commented
The main use of this is in charts, where it is currently impossible to (via formulas only) create a single x/y series where some values do not plot and do not have a line drawn spanning them (if the x/y chart has lines connecting points). The only current way to do this is to have a cell that is completely blank, and the presence of a formula in the cell currently makes that impossible.
More generally, it would be very useful to have a value that a formula could return that:
Does not evaluate as zero (messing up column averages etc)
Is not an error (messing up column sums)
Is not text, so does not violate checks for numbers e.g. when setting up a power pivot table.
This null function would achieve this.
Shirley Moreman commented
As well as the separate chart use of NULL this would be useful for COUNTA. At the moment a cell with a formula is included in COUNTA even if the formula result is "". E.g. If my formula is =IF(A1=1,"ONE","") the result is included in COUNTA. If I could put this as =IF(A1=1,"ONE",NULL) I would expect COUNTA to ignore the NULL. Hope that makes sense! Shirley
Chris Gross [MSFT] commented
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.
Levi Bailey commented
@Stilez as mentioned in the comments by others on this suggestion and elsewhere, the NA() function (which returns the #N/A error) can often satisfy that use case.