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.

298 votes
Sign in
Check!
(thinking…)
Reset
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 →
    Anonymous shared a merged idea: Add IFEMPTY() function (IFERROR is feeling alone.. :) )  ·   · 
    Daniel SmithDaniel Smith shared a merged idea: Return really blank value from formulas  ·   · 
    planned  ·  Excel Team [MSFT]AdminExcel Team [MSFT] (Admin, Office.com) 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.

    Thanks,
    John [MS XL]

    26 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • 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.

      • Bob JordanBob Jordan commented  ·   ·  Flag as inappropriate

        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.
        Bob J.

      • Mark BurnsMark Burns commented  ·   ·  Flag as inappropriate

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

        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.

      ← Previous 1

      Feedback and Knowledge Base

      How it works

      • Create a user account if you want to add a new suggestion or vote on existing suggestions.
      • Select one of the feedback forums listed.
      • Check out the ideas others have suggested and vote on your favorites.
      • Be sure to search for your suggestion but if you can’t find something similar enough, you can submit your own.
      • Keep suggestions focused on a single idea per post and limited to 25 words or less.
      • When you post an idea to our forum, others will be able to subscribe to it and make comments.