Forum Discussion
How do I turn off the autofill of the Spill Range Operator?
Hi mtarler!
Thank you for the detailed reply.
That all said I really can't see any 'good' cases where "Writing A1:A20 provides more control".
I am an advanced user of Excel and use it in the context of the treatment of scientific data. In contrast to my colleagues, I prefer Excel over Python or ORIGIN. I preprocess the data in Java, but then use Excel to combine and explore the data (Most are surprised how powerful Excel can be, especially for figures when using the right formatting options). There I build quite elaborate worksheets that take my data and process it. In the creation of these tools I really need the control. I am currently using the trick, you described to avoid this spill operator. I can also give an example:
When having a fixed range in an formula, such as $A$1:$A$1001, and I would like to use a different input column, like $B$1:$B$1001. I move the column, but then it changes, depending on other factors, to B1#. I really do not want this to happen. I like to turn this case around: In my use case, there are NO 'good' cases for the spill operator. It is just an annoyance and I never use it. I want to specify exactly when my range ends and no Excel guessing anything.
I even have seen it happening in figures! But then the figure does not work and I need to manually change it back to the normal range notation. But this is rare.
So, I really hope that Microsoft could provide an option to turn this off...
- so I see your example but you didn't explain the why. So why don't you want it to expand/contract with the spill range? There are cases but I was interested in your case. In the above why do you want exactly 1001 elements? and why is that spill just coincidentally also 1001 elements but for a DIFFERENT reason than the why you want 1001 elements?
BTW, one hack you could use might be A1:A2:A1001Hi!
Lets suppose I have a formula that takes in many different columns, or ranges, as is the case quite often. For some reasons, I would like to use a differen range. BEFORE the introduction of this feature, I would just by hand move the highlighted range from one location, e.g. column "A" to column "B". Any '$' symbols would stay and I would be happy. Now, doing the same, $A$1:$A$1001, gets replaced by B1#. This removes the "$" operator, that I do need when copying or moving the formula. Also, chnaging other parts of the worksheet than confuses the spill operator and suddenly my formula does not work anymore - without any changed to its inputs!
1001 elements was just an example. The reason in my case is that I normally operate over 0...n elements, which is n+1 elements.
The automatic appearance of the spill range operator annoys me on the daily basis and correcting it is a real hassle for me. I attached a quick example.- First off, ty for the example and details (not that I have any power to help). But now I see there are what I would consider 2 bugs in addition to the missing option to not auto use the xx# feature:
a) when in cell edit mode, if you move a range box with fixed reference indicators ($) to a spill range that spill range reference should also carry the fixed reference indicators ($) accordingly (e.g. $A$1:$C$3 moved to 3x3 spill range @ D1 should be $D$1#). Rules for mixed reference would be needed (e.g. in the above example if original ref was mixed like $A1:C$3 should it be $D$1# or D1# or something else?). Interestingly if you move a 'fixed' spill range box (e.g. $A$1#) to another area it WILL convert to a normal range reference WITH fixed indicators, but if you move back to original location the original fix indicators get lost.
b) when you copy a formula with a spill range reference and that new location is NOT a spill range it should revert to the corresponding range. So when you click on the formula and you drag the BOX representing the spill range operator to a range not defined by that spill range operator it WILL convert back to a normal range reference (as noted in comment above) but a cell copy operation does not do that equivalent.