How to block automatic row and column insertion in Google Sheets?

Question

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

=ARRAYFORMULA(MMULT(N(ROW(A2:A)>=TRANSPOSE(ROW(A2:A))),SIGN(ROW(A2:A))))

If the sheet has 5,000 rows the result should have 25,000,000 cells. Sometimes it just returns the following error message:

Error
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?

Answer 1

Short answer

Use ARRAY_CONSTRAIN(array,row_limit,column_limit)

Explanation

ARRAY_CONSTRAIN function could return the number of rows and columns that you want, just set the row_limit and column_limit parameters accordingly. To automatically set this parameters you could use a function like COUNT or COUNTA.

To calculate the number of rows/columns considering the data range by using built-in functions,

for rows use a formula like the following

=MAX(IF(LEN(A2:A)>0,ROW(A2:A)-1,0))

for columns use a formula like the following

=MAX(IF(LEN(1:1)>0,COLUMN(1:1)-1,0))

The sample formula in the question will look like the following:

=ARRAYFORMULA(
   ARRAY_CONSTRAIN(
     MMULT(N(ROW(A2:A)>=TRANSPOSE(ROW(A2:A))),SIGN(ROW(A2:A))),
     MAX(IF(LEN(A2:A)>0,ROW(A2:A)-1,0)),
     MAX(IF(LEN(1:1)>0,COLUMN(1:1)-1,0))
   )
)