Feedback by UserVoice

Lori

My feedback

  1. 672 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    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]

    Lori commented  · 

    @Roy, Hilarious :) I don't deny a general NULL() function would be useful. Adding linus minus' suggestion to the list i compiled on the other user voice on this topic, applications include:

    - Functions (ISBLANK, COUNTA, ...) 
    - Objects ( Charts, Sparklines, PivotTables) 
    - Actions (Sorting, Filtering, ...)
    - Connectivity (Access, SQL Server, ...)
    - Formatting (Text overrunning cells) 
    - Selections (Ctrl+Arrow keys, Goto Special or Current Region) 

    But it seems MSFT have gone down a one way street that requires context specific workarounds as has been done for charts and sparklines.

    If you really want to be able to produce a general null result from a formula use google sheets instead who by many accounts made the wiser design decision in this respect.

    Lori commented  · 

    Jon Peltier says in a recent blog post that the recent update for Excel 365 largely solves the use case that this suggestion was intended to address (https://peltiertech.com/plot-blank-cells-na-in-excel-charts/)

    MSFT likely took this route as allowing a true blank cell return from a NULL() function (wrapping another function or not) probably adds more complications than it solves.

    Lori commented  · 

    @Roy: i don't see any problem with a built-in function that returns null / empty per se but it's of limited use as you can't currently return a null value as the end result of a formula eg for use in chart source data.

    There is a common assumption that if a cell is blank then it does not contain a formula eg goto > special > blanks. And for those of us that have to support sheets especially those developed by others we often value backward compatibility above new features.

    The OP may have had an alternative value in mind that would be rendered as blank by charts (as is now possible with #N/A) and is backward compatible. However it wouldn't behave like null in other formulas like COUNTA.

    Lori commented  · 

    Please don't do this - I don't get why this has so many votes. Clearly a design decision was made long ago not to allow null as a formula result (probably for lotus compatibility) and this is a fundamental part of the application design.

    So if A1 is empty entering =A1 in another cell returns 0 and not null.

    We can also easily create a null function from vba code with

    Function Blank(): End Function

    So that =ISBLANK(Blank()) is TRUE. When entered as a formula the null is just converted to zero in the result.

    Any change to allow null as a formula result would break many sheets. The recent update to show #N/A in charts covers the most needed case.

  2. 153 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Lori commented  · 

    @anonymous. The request is actually quite vague and has numerous interpretations. A NULL() function could be applied to many different contexts including:
    - Functions (ISBLANK, COUNTA, ...)
    - Objects ( Charts, Sparklines, PivotTables)
    - Connectivity (Access, SQL Server, ...)
    - Formatting (Text overrunning cells)
    - Selections (Ctrl+Arrow keys, Goto Special or Current Region)
    Many of these have been suggested in this and related forum posts.

    A general NULL() function would need to return a result identical to empty cell to apply to each context which is possible in Google sheets but not Excel.

    My main use would also be functions and the NAN() suggestion is a standard representation for a missing value that also preserves numeric type. Many functions would have to be modified whatever the value chosen to represent null, resulting in other possible issues.

    Lori commented  · 

    Introducing a NULL() function seems problematic as chart guru Roberto Mensa says further down.

    To be consistent Null would need to be returned from any formula that returns a reference to an empty cell as is done in Google sheets and also from =IF(1,).

    Excel allows nulls within the formula eg =ISBLANK(IF(1,)) returns TRUE but converts null to zero in the formula result. And changing this behaviour would not really be feasible for legacy reasons.

    As an alternative I'd be in favour of a NAN() worksheet function to represent a missing value.This would be displayed as 'NaN' (right-aligned), as is common in other languages like python. NaN can be returned from a udf (by returning a double with all bits set to one) and is compatible with power query. It is also the only value AFAIK that is shown as empty in charts (https://newtonexcelbach.com/2018/03/31/plotting-charts-with-gaps/)

  3. 18 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Interesting suggestion Jon, thanks for posting it. There’s some other suggestions around blank/null, so we’ll be taking a look at the space in general, and making particularly sure that we consider related functions that get a lot of votes. As always, the more votes something gets the more chance that a specific request gets in the execution pipe sooner. So I just want to encourage folks to keep voting for the ideas they like best!

    Cheers,
    John [MS XL]

    Lori supported this idea  · 
  4. 6 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Lori shared this idea  · 
  5. 9 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Lori commented  · 

    Lori commented · Just Now · Delete

    I've seen this requested many times and surprised it's still not there. There are several workarounds but none that are entirely satisfactory. (see http://forum.chandoo.org/threads/create-an-array-of-numbers.13433/)

    Lori supported this idea  · 

Feedback and Knowledge Base