excelgoogle-sheets

Excel spill range reference in Google sheets

Let's say I have a spreadsheet with the following data in it:

       A    B

1      1    1
2      1    1

Now, in cell C1 I am going to add in the formula =A1:A2. Now my data looks like this:

       A    B    C

1      1    1    1
2      1    1    1

In cell D1 I enter in =C1 it will give me the value 1, for example:

       A    B    C     D

1      1    1    1     1
2      1    1    1

And in cell E1 if I enter in =C1# it will give me the dynamic reference contained in that cell, rather than the value of the cell itself, giving the following:

       A    B    C     D     E

1      1    1    1     1     1
2      1    1    1           1

How would I do that same thing in Google Sheets since it doesn't allow the reference of # suffix to reference the range contained in that cell?

I really couldn't find good documentation on this from Microsoft so here is the best reference I could find: https://www.xelplus.com/excel-hash-sign-formulas/.

Related: https://www.reddit.com/r/excel/comments/vmtuzf/does_google_sheets_have_a_spill_reference_method/

Another example:

enter image description here

Solution

  • Google Sheets spills array formula and array expression results, but the hash sign notation is not supported currently (December 2024.)

    To get a multi-cell range, use an { array expression }, like this:

    ={ D7:F9 }

    To get a subset of an array, use filter(), array_constrain(), chooserows() or choosecols(). For example, to get the last column in the range D7:F9, use this:

    =choosecols(D7:F9, -1)

    See arrayformula(), array_constrain(), filter(), chooserows() and choosecols().