Forum Discussion

JuanjoB's avatar
JuanjoB
Copper Contributor
May 20, 2026

Please allow this foundational concept for algorithms

Hello dear friends of Excel.

Please let me begin by thanking you all for giving us the excellent tool that Ms Excel is.

I've being working practically my whole Computer Engineer career (~18 years) with this awesome tool, and I cannot imagine doing my work without it.
It really is excellent, and has been enhancing a lot in the last years.

 

In this occasion I would like to ask you to consider including in Excel, native support for "empty arrays", that is, arrays that have "0" (zero) items.

This feature would help a lot in the following type of algorithms:

  • When you have to scan a range of cells that hold the items of a set named "S", and
  • from that set you have to select only the items that satisfy a given condition { ALL i | such that CONDITION(i) = TRUE }, and
  • with those selected items (i) you have to construct a new vector named "V"
  • In this case you usually need to begin with an "empty array", that is the initial value before adding any selected item, and
  • at some point as you process the algorithm, maybe you will find the 1st. item to add to the "empty array"
  • Maybe you won't find any item that satisfy the condition, and at the end of the algorithm, you will end up with an "empty array" as a result.

To illustrate this type of algorithm, let me put an example:

=LET(
     vector; SEQUENCE(10);
     new_vector; REDUCE(
        0;
        vector;
        LAMBDA(building_vector;vector_i;
                        IF(RESIDUO(vector_i;2)=1;
                            VSTACK(building_vector; vector_i);
                            building_vector
        )
        )
     );
     INDEX(new_vector;SEQUENCE(ROWS(new_vector) - 1) + 1)
  )

  • This "algorithm", scans a set S = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }
  • from S, it selects only the "odd numbers" { ALL i | such that "i is odd" },
  • it takes every odd number and adds it to the array named "building_vector",
  • "building_vector" begins as a non-empty-array, holding "1 sentinel item" at the beginning of the algorithm (the number "0"),
  • at the end of the algorithm, I have to do a work-around, to remove manually the 1st. sentinel item from the array

The cons of this approach are:

  • sometimes the resulting array will not have any selected item, that is, it will be an "empty array", and as such, Excel will generate an error showing #CALC!
  • as Excel doesn't currently support empty arrays, I have to do this workaround to add an initial sentinel item at the beginning of the algorithm, and then I have to remember to remove it at the end of it

Dear friends of Excel, I consider empty arrays to be a "foundational concept for algorithms". It's like the "zero" for numbers, but for arrays.

It would be nice to be able to write this kind of algorithms with the aid of Excel, being able to natively support "empty arrays" without me having to do the extra work of handling a simulated situation of an empty array.

I present you this request, and I ask you to please consider the importance of this feature.

 

Thanks in advance!

developer
excel

2 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    May 20, 2026

    This idea has been suggested before and the Excel team has commented on it:

    Give us a proper NULL() worksheet function. · Community

    The best way to get Microsoft's attention is to up-vote the discussion and comment. I think it's a good idea and I'd like to see it supported. Though it's further down my wish list. The change that's a higher priority for me is opening up the calculation engine and supporting nested arrays (or adding a native function that can efficiently unwrap thunks).

    • JuanjoB's avatar
      JuanjoB
      Copper Contributor
      May 21, 2026

      Hi Patrick2788​. Thanks for your response and for the link you passed.

      I think the referred thread deals with a very similar topic, that is a BLANK CELL, but different to the concept of an EMPTY ARRAY.

      Let me define it this way:

      • a BLANK CELL is a worksheet cell that contains no value and no formula.
      • an EMPTY ARRAY (though this concept it is not implemented in Excel nowadays) would be a dynamic array with zero elements (size = 0).


      The core difference between the 2 is that:

      • in the case of the BLANK CELL, the cell exists, it's just that it contains no value inside of it, like 1 cardboard box that is empty inside
      • in the case of the EMPTY ARRAY, the array doesn't have any cells, like a bag that contains no cardboard boxes inside of it. If the bag contained boxes, at least the boxes could have the priviledge to be empty inside (like blank cells), but that isn't even a real possibility, since there is none.


      Thanks for the suggestion dear Patrick. I will wait with high hopes for the friends of Excel to read this post.