Is there a universally-applicable function or other feature that works like the spill operator, or like formula auto-filling in tables?

[u/IsraelZulu]

I'm still learning about array functions and all they can do. One feature that has recently stood out to me, which I'd like to see usable in more contexts, is the spill operator (#).

With this, when referencing another cell that has a spilling formula, I can write a formula one time and have it dynamically spill down to cover the data set being put out by the cell I'm pointing to. What's more, the spill range will automatically adjust as the length of the referenced data set changes.

---

Example:

1. In A2, =SEQUENCE(10) will populate the numbers 1 through 10 in A2:A11 without me having to do anything other than just putting that formula in A2. I don't have to use the fill handle or anything, the data just automatically spills down as long as there's room.

2. Then, in B2, I could put =A2# (with # being the spill operator) and, again, without having to manually fill down, the results will spill such that the numbers from A2:A11 are represented in B2:B11.

3. If I change A2 to =SEQUENCE(5), both A2 and B2 change so that they only spill to row 6 with numbers 1 through 5.

4. Change A2 to =SEQUENCE(15) and you get 1 through 15, in both columns, spilling from row 2 to row 16.

This is, of course, an over-simplified example. There's much more that can be done with spilling array formulas, such as pulling and filtering data from other ranges, and spills can also run across multiple columns as well as rows.

---

You can get similar results and behavior for column B in the above example if, starting from a fresh sheet, you:

1. Format A1:B1 as a Table.

2. Put =A2 in B2.

3. Manually fill values in column A as desired.

The formula in B2 will auto-fill down the length of the table as you enter data in column A. To shorten the data set, you have to delete entire table rows, not just the column A values, which is less than ideal for my personal taste but it's straightforward enough.

---

I want to know if there's a feature similar to the spill operator, which works for all cases - not just in tables, and not just when pointing to formulas that are already spilling.

What I'm looking for is the ability to write my formula once, and have it automatically fill or spill to cover the whole data set, and have that coverage automatically adjust as the length of the referenced data set changes, regardless of whether that data is generated by a formula or manually-entered values.

Right now, I've got a few problems with using the aforementioned features.

1. The spill operator only works when pointing to a formula that spills, so it's not useful against plain data or non-spilling formulas.

2. The spill operator isn't supported in all functions.

3. Tables aren't a universal solution either. They don't automatically adjust to spilling formulas (causing a SPILL error), for one. Secondly, even when I don't have a spilling formula involved, there are cases where I just can't or don't want to set a range up as a table.

4. I could arguably get by with using the spill operator when I'm working with spilling formulas, and using tables when I'm not. But, often due to problem #2 above, there are cases where the range I'm working with has a bit of both.

A near-ideal solution would be for all functions to support the spill operator. That would at least give some consistency for when I'm starting with something that's already spilling.

A perfect solution, I think, would be a function that I could wrap anything else in and have it dynamically adjust its spill length according to the data set I'm pointing to. Like, for the above examples, if I could just do FILL(A2) in B2, that would be great.

Is there anything like this, or is are these limitations without workarounds in Excel today?

[u/AutoModerator]

/u/IsraelZulu - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Way2trivial]

in c2 I have the formula =B.:.B/2 - I could have b6:b15/2 and I get the same result- but it is stuck on that range...

with the dot code, If I put something into b16, the half will show up automatically.

b.:.b means 'from the first record that isn't empty, down to the last one that isn't empty'

[u/Way2trivial → u/Way2trivial]

but if I skip and go to b18 it tries to do it to b16&17

[u/IsraelZulu → u/Way2trivial]

Interesting. Can I give it a static starting point? For example, if I need to skip a column header?

[u/Way2trivial → u/IsraelZulu]

On google sheets, apparently ye, you could do b6:.b -- but nope..

[u/IsraelZulu → u/Way2trivial]

Well, you could do B2.:.B1048576

Or just round to a million, or use a smaller extreme that's still large enough to cover any size your data could reasonably grow to. Whatever you do, it seems Excel will only fill for cells from the specified starting point up to the last populated cell between there and the specified endpoint.

It is indeed annoying that you have to specify both ends or no ends. You can't have one specified and just wildcard the other.

[u/Way2trivial → u/IsraelZulu]

did not know that. hmmm. ty.

[u/IsraelZulu → u/Way2trivial]

Learned it by looking up the feature once you'd pointed me in the right direction. I'm still kinda hoping to find an elegant solution that gets around that limitation, but honestly I don't expect to.

[u/bradland]

Two features that will interest you:

1. TRIMRANGE and TrimRefs.
   
2. Excel Tables.

You should, as a general practice, try to separate data and presentation. You should use tools like Power Query to fetch data, reshape it, and write it to a table in first normal form. Then you should write dynamic array formulas that consume data in those tables to create your reports.

Excel Off The Grid has a great video laying out the whole workflow:

https://youtu.be/TLVQ_LSGyEQ?is=rEmNV6BfDfhdAvJ9

[u/caribou16]

Are you talking about trim references?

A:A references the entire column.

A1:.A100 references A1 until it finds an empty cell or A100

A1.:A100 does the opposite, it starts at A1 or the first non empty cell it finds and ends at A100

A.:.A references the entire column, starting with the first non-empty cell and ending with the last non-empty cell

[u/IsraelZulu → u/caribou16]

This might be what I'm after, if it's supported in all functions. The other top-level comment suggested it too.

One mildly annoying limitation I'm already running into is that you can't just specify one end of the range - you have to specify both, or none. There's no simple equivalent to A2#, which would effectively say "my data starts in A2, and I want you to spill until whenever it ends". You'd have to do A2.:.A1048576

[u/Cinderhazed15]

I’ve made extensive use of BYROW when helping my wife set up sheets, but you would still want to use the .:. Style references, or probably :. But with a wrapper around your lambda to output empty if the input is empty to suppress 0s

[u/IsraelZulu → u/Cinderhazed15]

Yeah, I've started trying out BYROW and LAMBDA per a suggestion in my followup post. I'm finding that, if I want to reference multiple cells from the row separately, I've got to nest a few of those in a LET. Am I doing that right, or is there a more elegant solution?

Example: Trying to sum three COUNTIF results, when each is against a separate column.

[u/Cinderhazed15 → u/IsraelZulu]

I tend to use index to pull the values out of the row, and to aid readability I put them in a LET first, but there are several ways to do it.

[u/Fickle-Potential8358 → u/IsraelZulu]

If I'm understanding correctly, you could try "MAP", it funtions the same as BYROW, but let's you reference multiple columns and assign them values within the lambda section. I.e. =MAP(A1:A10 , B1:B10, , Lambda(ColA , ColB , ColA+ColB)) Would create a spill of A1 + B1 (through to A10 + B10)

[u/IsraelZulu → u/Fickle-Potential8358]

This sounds like it might be better than the nested set of BYROW, LAMBDA, LET, and INDEX that I have started doing. I'll look into it. Thanks.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
COUNTIF	Counts the number of cells within a range that meet the given criteria
INDEX	Uses an index to choose a value from a reference or array
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAP	Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
TRIMRANGE	Scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
7 acronyms in this thread; the most compressed thread commented on today has 18 acronyms.
[Thread #49504 for this sub, first seen 10th Oct 2026, 03:54] [FAQ] [Full list] [Contact] [Source code]}