Forum Discussion
How do I turn off the autofill of the Spill Range Operator?
Hi!
I work with a lot of arrays. Whe selecting a range for my array, often, e.g., A1:A20, is replaced by A1#.
I am aware of the function of this operator. However, I do not use it and I do not want to use it. Writing A1:A20 provides more control that I do need.
Is there any way that I can prevent Excel from autmatically using A1#? It actually does drive me a little crazy sometimes.
Thank you!
- So it has been a couple days and no one else has answered either because I suspect there is no control to turn off that feature. That automatic replacement of a range by the "equivalent" xx# version only happens when you specifically select that exact full range. If you select 1 cell more or less they it doesn't replace it. You can also manually override that 'suggestion'. That all said I really can't see any 'good' cases where "Writing A1:A20 provides more control". Basically the only cases are if that range must stay the same even if the actually spill range changes which seems rare to me or you are doing some text parsing using FORMULATEXT(), which I can't imaging is efficient. You probably already know if you want only the first 20 items you could use TAKE(A1#, 20) but your whole point is that it is easier to select A1:A20 and have it not autocorrect to A1#. Sorry I'm not any help but would like to know if you have other cases where you don't want to use xx# notation (maybe we can give a alternative option that could help in that case).
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:A1001
