While playing around with array formulas, "sometimes" when the result has more rows and columns than the previously available in the sheet, Google Sheets automatically adds the row needed. I said "sometimes" because I don't figured out yet why sometimes Google Sheets automatically add new rows but other times don't.
To make things worst, like when using MMULT with open ended references (A:A), the recalculation time takes minutes.
Let say that I have a formula with open ended references like the following
If the sheet has 5,000 rows the result should have 25,000,000 cells. Sometimes it just returns the following error message:
The resulting array was too large.
Other times, Google Sheets add a lot of rows, and the recalculation time could take minutes.
Besides using close ended references (A1:A10) instead of open ended references(A:A), Is there a way to tell to Google Sheets to do not insert rows or columns?