How to prevent formula from being filled to the 1,048,576 row?

[Ian Y]

I tested filling a formula into adjacent cells by using the Fill command. The problem is that, the worksheet I'm testing contains only 11 rows and the aforementioned method filled my formula down to the 1,048,576th row.

I don't mind if it fills my formula down to the 1,000th row, but filling 1,048,576 rows is too much and that caused a freeze of 2~3 seconds.

 

[Peter_SSs]

The first step in those instructions is to select the cell with the formula and the adjacent cells you want to fill.
It sounds like you might have selected the whole column, rather just the 11 cells you need the formula in.

 

[Peter_SSs]

I see that my suggestion must have been the issue, so glad that is resolved.
In case you are not aware of it, sometimes using the 'Fill Handle' may be more useful than using the 'Fill' menu. For example, in my sample sheet below, instead of selecting E1:E6 and using 'Fill Down' I can select the formula cell (E1) and then either ..

1. Click and drag the Fill Handle (little solid square at the bottom right of the selected cell) down to E6, or
2. Simply double-click that Fill Handle in E1 and the formula will fill down to E6. This double-click method is especially useful if the formula needs to copied down a few hundred or a few thousand rows as it is very fast.

 

[Ian Y]

I see that my suggestion must have been the issue, so glad that is resolved.
In case you are not aware of it, sometimes using the 'Fill Handle' may be more useful than using the 'Fill' menu. For example, in my sample sheet below, instead of selecting E1:E6 and using 'Fill Down' I can select the formula cell (E1) and then either ..

1. Click and drag the Fill Handle (little solid square at the bottom right of the selected cell) down to E6, or
2. Simply double-click that Fill Handle in E1 and the formula will fill down to E6. This double-click method is especially useful if the formula needs to copied down a few hundred or a few thousand rows as it is very fast.

View attachment 104574

Thank you! I knew the first method but didn't know the second one. It looks like the second method makes things easier and it does not unwantedly fill the formula down to the 1,048,576th row.