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]
Just to add a voice - I found this page when I was trying to solve similar issues as an experienced Excel user. I use formulae to pick out data for charting and to build further data ranges for further spreadsheet processing. But when there isn't data for some cell, I want the content to actually be ignored/blank/null, so that the content isn't confused with actual data that might be zero or need charting. So I can't just use numeric 0 formatted as an empty string. This affects lookups, comparisons, calculations, as well as charting. A NULL() function would be insanely helpful!
Erik Berger commented
I would agree with Conor O'Dowd (March 29, 2016 23:59), except it might be dangerous to change behaviour of the existing function/constant NA()/#NA. I could also agree with Harlan Grove (June 03, 2016 20:41), that #MISSING might be clearer than #NULL.
But anyway, I think the plotting option dialog should offer to take care of error values!
Erik Berger commented
Why not simply add "invalid" to the graph option dialog for treating hidden and empty cells?? I.e. "hidden, empty and invalid cells" -> treat as zero, treat as gap or interpolate.
Though I agree, the ability to create a true blank using a formula would be appreciated (as well).
This is so much more important now that we're importing excel data into Power Pivot and there are no ways to create null values using formulas. I can't think of any valuable reason why a string with no length is not null after converting to values.
When using VLOOKUP, sometimes the cell I'm looking for is empty, and I receive a zero output. Instead of creating long boring IF statements, IFEMPTY function might help :)
The structure should be the same as IFERROR.
Cheers ! :)
I like this idea for same reason as June 11, 2016 comment:
I use scatter plot to make shapes, and if I want to plot two shapes (say two unconnected squares) on the same series using one formula I must currently delete the formula creating a gap in the chart. I would prefer that "" or NULL() or similar act like an empty cell to the chart, let NA() still not impact the chart (also useful).
Jonathan Turner commented
Excel has been crying out for this functionality for a loooong time.
It is good to see this website, as previously there has not been a satisfactory place to request such a function, although I tried several times. However none of those requests were as comprehensive as Jon Peltier's. Thank you Jon for championing this issue. One which eventually raises its head for many heavy users of Excel.
Daniel Smith commented
When plotting values on an xy chart with lines joining points , a completely blank cell in the y column causes a gap in the drawn line. This can be very useful, for instance when plotting a set of shapes or a map. However there is no way of making a formula in the cell cause a gap because the cell is then not completely empty. Suggestion is to fix this by providing a new worksheet function which returns a value which looks like an empty cell to the chart plotting logic.
Harlan Grove commented
Since NA() becomes #N/A when converted to static value, what would NULL() become? Nothing? #NULL!?
I understand the terminological appeal of 'NULL', but MISSING() and #MISSING (need a constant to go along with the function) would be less ambiguous, conform to stats package terminology rather than DBMS terminology.
Conor O'Dowd commented
Or... you could jut change the behaviour of NA() to be the same as the proposed NULL(). I always thought that was what NA() was supposed to be. It amazes me that it is interpolated in an X-Y or line plot, that's plain silly.
I really need this NULL() function to construct a database I will use with PowerPivot and build powerpivot tables.
If I use in column A the formula IF(isempty(B1), "", Month(B1)) for example, with dates in B1, it's OK for Excel to recognize the whole column A as numbers, but... not for PowerPivot !
Indeed, PowerPivot won't accept a column of numbers with some "", because it's a text value, not an integer value.
I am currently stuck with this problem every day. If I put 0 or NA() instead of "", it doesn't work neither in PowerPivot to recognize the column as numbers... so I have to copy and paste my formula as values and replace the "empty" cells by... nothing so that PowerPivot accept the values.
Any suggestions really appreciated :-)
Peltier really nailed it on the head. There a few work around for most of the scenarios where the null fxn would be used but many of them are incredibly tedious, unintuitive, and heavily vba based.
Bob Jordan commented
Anyone doing statistical work has the need for a missing value and as noted none of the workarounds are good across plots and calcs. The only solution I have found is to have two columns - one to do the calculations and one for the plots. But this also has limitations in addition to the obvious duplication.
The NULL() is the obvious choice of all the error codes and i so strongly support this move.
I will post a comment through the Excel 'Send feedback' and suggest others do to.
I support this without any reservation, but have run out of votes.
Mark Burns commented
I would add that making something like this mirror the MS-Access NZ(<expression>, <optional-value-to-return-if-0/Null>) would not be a bad idea either. I have never understood why Excel lacks this basic function that M-Access has had for literally DECADES now.
Jon Falzon commented
This is a great suggestion. I have, like everyone else, just learned to live with using the other functions Jon mentioned, but none work as well as a function that makes the cell truly blank.
I voted for this. But I also wanted to comment: anyone that has worked with Excel for any extended period for engineering/science purposes (i.e. where charting is of prime importance) as opposed to accountancy (where it may not be) knows the pain that Jon lays out in detail below. Please give this suggestion some serious consideration.
Jon Peltier commented
Thank you for responding to my suggestion. I've been making it for over a decade, and I understand it's not a big screaming bug, but it does cause problems for users.
What a good NULL() function would do is have its parent cell behave as a blank cell, despite containing a formula.
In a chart, a NULL() value in a cell would suppress drawing a data point and its associated label in any type of chart, and a gap would connect the points on either side in a chart type that used lines and markers.
A blank cell meets these criteria. There is no marker in a line chart, and there is a gap in the line; also there is no data label in the column chart. The line chart with #N/A has no marker, but the line connects the points across #N/A, and the column chart has a label that says "#N/A".
For formulas, I would have almost every other formula treat the cell returning NULL() as a blank cell. If A1 returned NULL(), then ISBLANK(A1) would be TRUE, ISNUMBER(A1) and ISTEXT(A1) would both be FALSE. COUNT(A1) and COUNTA(A1) would both be 0, and COUNTBLANK(A1) would be 1. Any numerical or statistical calculations involving the cell contining A1 would treat it as it would have treated a blank cell.
In the only deviation I can think of from an actual blank cell, I would make ISFORMULA(A1) be TRUE.
Some workarounds to a good NULL() formula and their consequences are:
chart: omits marker (good) but does not leave gap in line (bad)
formula: further calculations affected by error (bad)
appearance: #N/A is ugly in a cell (bad)
chart: plotted as zero (bad), also data label shows "0" (also bad)
formula: may produce unexpected results (bad)
appearance: looks blank in a cell (good)
(3) VBA to clear contents of cell
chart, formula, appearance: just like a blank, because it is (good)
have to rebuild any deleted formulas if precedents change (bad)
(4) VBA to step through a chart and format a point to be invisible (e.g. a bar or column, a marker and line, data labels), but the chart would have to be restored if data changes.
(5) There are complicated ways to structure a chart's source data that produce gaps where NA() would appear, and I've blogged about a few, but these are tedious, fragile, manual workarounds.
You can search the web and find many people hunting for something like this NULL() function:
Here are a few articles in my blog:
There is a lot of confusion and a good many workarounds, but nothing that feels right.
Jon Peltier commented
Anonymous - NA() is not a complete solution. It stops plotting of a marker, but if the series has connecting lines, they connect the points on either side of the NA(). Also, NA() suppresses plotting of points for line and scatter charts, but not for column charts and other types.
Cathy Harley (EXCEL PM) commented
Can you provide more context to scenarios where you'd need this function?