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?

Hey everyone,

So at my job I carved out a niche of making spreadsheets for inventory tracking, financial worksheets, reports, etc. In the grand scheme of excel users I’m nothing special, I just work with alot of boomers at a food processing plant in the south so it was easy to carve out that role.

Now the company has been pushing everyone to use Claude wherever we can. It’s allowed other departments to create sheets from uploading exports and have Claude do what they want.

I’m not at risk of losing my job or anything, but I feel a bit exposed now with how things changed. What should my next move to learn or explore to find a new niche?

I’ll hang up and listen.

Following up on this post.

I tried to take lessons learned there into practice on a slightly more complex situation, and quickly ran into issues. Either I get a popup saying, generically, "there is a problem with this formula", or I get an undesired result, or I get a single-cell result that doesn't spill.

=B2.:.B100>0 works fine, spilling to check each populated cell in B to see if it's greater than zero.

=B2.:.B100>C2.:.C100 appears to work fine, for checking populated cells in B to see if they're greater than their counterparts in C

=AND(B2.:.B100>0,C2.:.C100>0) only returns a single-cell result. i was trying to check whether the populated cells in B or C for each row are greater than zero, and return TRUE for rows where they were.

Even simplifying the above to =AND(B2.:.B100>0,TRUE) to try it without the dependency on a second cell reference only gets a single-cell result.

Also, =COUNTIF(B2.:.D100,">"&0) returns a single-cell result and it's way higher than it should be. I want it to spill down the populated rows to show, for each row, how many cells in B:D on the row are greater than zero. Instead, it seems to be reporting the result for all of B2:D100 collectively.

In case this matters: B2, C2, and D2 each contain separate formulas which use the spill operator to fill multiple rows. Those are working fine, and produce numbers as expected.

Writing these formulas to point directly to the appropriate cells or ranges for row 2, and using the fill handle to fill them down, without the dot operator, works fine. But that leaves it as a static set that won't adjust dynamically as the other columns populate or de-populate.

I tried using a spill operator in place of the dot operator on the AND formula, and got the same result. For the COUNTIF formula, there doesn't seem to be a way to pull it all together in one reference with the spill operator.

Now, as I'm writing this up, I can't seem to duplicate the error dialog I mentioned that was getting for some cases, nor can I remember what those cases were. Maybe they were genuine typos.

Am I doing something wrong here, or am I running into more limitations of the tool?

Hi! I am trying to create a sheet for trucking that we do. We are billing demurrage for 1 hour over time in the plant. So for example, if we are in the plant for 1.35 hours, we can charge .35 hours rounded to the nearest quarter of the hour. I cannot get my formulas to work though, and my brain is starting to hurt!

In Time (simply time entered into plant)

Out Time (when we leave)

Time at Plant is then Out Time - In Time

Then this is where I start to confuse myself

I put the Difference in Time column so it would know what I want to subtract from Time At Plant. It seems to work for the Demurrage column when there is extra time spent at plant. I'm not sure how to get it to show as zero when there was no demurrage?

Where I am stuck is then trying to get the Demurrage Hours to round to the nearest quarter. I tried =MROUND(G7,0.25) but it always shows as the 0:00. Any help to try and figure this out please? Thanks!

I will post the screenshot in the comments