Google Sheets – How to Block Automatic Row and Column Insertion

formulasgoogle sheetsgoogle-sheets-arrayformula

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?

Best Answer

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))
   )
)
Related Topic