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.

375 votes
Sign in
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Jon PeltierJon Peltier shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    planned  ·  Excel Team [MSFT]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
      I agree to the terms of service
      Signed in as (Sign out)
      • First260First260 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 MakinNigel 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 FellowsSteven 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 ChandraBhuvnesh 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.

      • SteveSteve 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 BaileyLevi 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.

      • StilezStilez 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 BergerErik 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!

      • Erik BergerErik Berger commented  ·   ·  Flag as inappropriate

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

      • MichaelMichael commented  ·   ·  Flag as inappropriate

        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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        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 ! :)

      • PiRhoPiRho commented  ·   ·  Flag as inappropriate

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

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

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

        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'DowdConor O'Dowd commented  ·   ·  Flag as inappropriate

        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.

      • LionelLionel commented  ·   ·  Flag as inappropriate

        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 :-)

      • Anonymous commented  ·   ·  Flag as inappropriate

        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.

      ← Previous 1

      Feedback and Knowledge Base