How can we improve Excel for Windows (Desktop Application)?

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.

470 votes
Sign in
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Jon Peltier shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    planned  ·  AdminExcel Team [MSFT] (Admin, responded  · 

    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]


    Sign in
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      • Anonymous commented  ·   ·  Flag as inappropriate

        Frankly I would love to simply see the option to return an NULL value similar to how you can use FALSE or TRUE in a logical formula.

      • Thomas commented  ·   ·  Flag as inappropriate

        Any progress on this? I would like to do the same thing. Could use a simple term to enter a null value into a cell so it doesn't get graphed. Using an IF statement, any value entered in the cell returns a graphable result - even text. I've had some luck with #N/A but it only works if there are a string of cells with that value. it doesn't work correctly when alternating cells with that result because it will graph all of them. Thanks.

      • David Johnson commented  ·   ·  Flag as inappropriate

        My want for this function would be to have a return value from a formula that doesn't 'chop off' the text from the cell to the left.

      • First260 commented  ·   ·  Flag as inappropriate

        @A.C.Wilson. The basic sheet structure is (see ecma openxml documentation):

        - Cells are either constants or formulas that return values (number, text, logical, error) or else are empty (blank).

        - Formulas are strings of operators, functions, names, values, or other derived data types (array, reference, missing).

        Introducing a new data type (Null) in addition to these eight would very likely break compatility and involve redesign of many existing functions and features.

        Adding an error value such as #MISSING! (#NULL! already exists) is also possible but adds complexity by introducing another null representation in addition to blank / missing.

        Alternatively, other options could be added for how to handle #N/A or zero length string just as you have for blank cells in chart options.

        [For +Infinity (2^1024 in terms of doubles):
        Function inf() As Double
        On Error Resume Next
        End Function]

      • A.C. WILSON commented  ·   ·  Flag as inappropriate

        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.]
        3) NUMBER
        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.]

      • Kenneth Barber commented  ·   ·  Flag as inappropriate

        Not the best name for the function. It makes me think that you want a function that returns #NULL!, much like how NA() returns #N/A.

      • Chris S commented  ·   ·  Flag as inappropriate

        Like many others, ended up here after searching for a way to generate automatic sequences of XY line segment values. Excel 2016 has this difficulty, like all previous versions. I also noted that the presence of text values in a scatter XY series seems to force the plot to NOT be a scatter plot. From my perspective, the graphing problem is the big one - almost all "cell contents" problems can be fixed with alternatives either involving IF, or conditional formatting.

      • First260 commented  ·   ·  Flag as inappropriate

        The reason this has not progressed is it's not possible without making fundamental changes to the API.

        You can already return a Null value within a formula using either IF(1,) or a udf that returns Empty. Both return TRUE when passed to ISBLANK(). The corresponding API values are xltypeMissing and xltypeNil neither of which can be returned to a cell as a result.

        A (dirty) workaround is to use a udf that changes the value of another cell via the EVALUATE function but this will complicate the calculation dependencies and recalculation.

      • Nigel Makin commented  ·   ·  Flag as inappropriate

        The only NULL() we get seems to be on progress! This must be getting really embarrassing for the Excel Team [MSFT] now that MS Windows have got their act together and produced a good product in Win10, managing to join together all that legacy software? Perhaps they could lend you some programmers? More seriously, how about setting a target date for a NULL() fix?

      • Steven Fellows commented  ·   ·  Flag as inappropriate

        I agree, we need a Null() function. In the meantime, to tell the difference between data, formula, blank, and no value, why not use something like this:

        if there is text in the field that also counts as a null value, use:

      • Bhuvnesh Chandra commented  ·   ·  Flag as inappropriate

        any updates on proper NULL() worksheet function ? -
        Which 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.

      • Steve commented  ·   ·  Flag as inappropriate

        Come on MSFT get this **** figured out already. This is basic computer science and your user base is demanding this feature.

      • Levi Bailey commented  ·   ·  Flag as inappropriate

        @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.

      • Stilez commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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!

      ← Previous 1

      Feedback and Knowledge Base