To dynamically exclude the last empty rows and columns from formulas, we can use functions such as ARRAY_CONSTRAIN, INDIRECT, and OFFSET in Google Sheets.

I will come to that. Let’s first know why one should want to dynamically exclude blank rows and columns from the end of a range.

Assume our data range is A2:I8, and it may grow/expand horizontally and vertically in the future.

So in formulas, we may refer to A2:Z to open the rows (infinite rows) or A2:1000 to make the columns open (infinite.)

Another preferred choice is opening both ends using indirect("A2:"&rows(A2:A))

All these infinite ranges can cause issues in some formulas, such as the FLATTEN and MMULT. Such an open range can affect the performance of your sheet or return formula errors.

For example, when we FLATTEN an open range, it may insert several rows in the sheet. So one of the best solutions is to use a dynamic array constrain formula.

By dynamically excluding the last empty rows and columns from the formula ranges, we can feed the FLATTEN or any other formula the exact range A2:I8 but will include future entries.

As a side note, you can permanently delete rows and columns in Google Sheets. For example, if your sheet contains 1000 rows and you delete ten rows, the left rows in your sheet will be 990 rows. It’s not the case in Excel.

So, if you don’t want to follow this method, you can manually delete rows and columns outside your existing range.

Dynamically Excluding Last Empty Rows and Columns from a Formula Range

The below sample data is in the tab named “Sample.”

To dynamically exclude the last empty rows/columns in Sheets, I have three formula options. All of them use almost the same logic. Here are them.

1. Dynamic ARRAY_CONSTRAIN in Google Sheets

When it comes to resizing a data range in Google Sheets, ARRAY_CONSTRAIN is the dedicated function. It can constrain columns, rows, or both from the end of an array/range.

Syntax: ARRAY_CONSTRAIN(input_range, num_rows, num_cols)

What about one sample Google Sheets formula?

=ARRAY_CONSTRAIN(Sample!A2:I8,7,9)

The above formula is not dynamic as we have specified the number of rows (num_rows) and columns (num_cols) manually.

We will use expressions in num_rows and num_cols to make the constraining dynamic.

The first step is to find the row number of the last non-empty cell ignoring blanks in the first column.

Number of Used Rows (num_rows):

=ArrayFormula(MATCH(2,1/(Sample!A2:A<>""),1))

The next step is to find the last non-empty column in a row. For that, we can consider row#2, which contains the field labels.

Number of Used Columns (num_cols):

=ArrayFormula(match(2,1/(Sample!A2:2<>"")))

Using the above, now we can code our Dynamic ARRAY_CONSTRAIN formula.

=ArrayFormula(ARRAY_CONSTRAIN(Sample!A2:Z,MATCH(2,1/(Sample!A2:A<>""),1),match(2,1/(Sample!A2:2<>""))))

The above is our first formula to dynamically exclude the last empty rows and columns from an infinite range in Google Sheets.

To test it, below the populated result, type any value in the blank rows and columns. The formula won’t return the #REF error.

For example, type any value below the name “Roger” in cell B10 and right to the date 8/6/21 in K3. The table would be stable!

Additional Tips

What if I want to exclude the header row from the range?

It may be a most common requirement when you are not using DATABASE functions or QUERY.

You require to do two changes.

1. Change the input_range from A2:Z to A3:Z.
2. Change num_rows from MATCH(2,1/(Sample!A2:A<>""),1) to MATCH(2,1/(Sample!A3:A<>""),1)

What if I want to exclude the header row and the first column from the range?

1. Again change the input_range from A2:Z to B3:Z.
2. Change num_cols from match(2,1/(Sample!A2:2<>"") to match(2,1/(Sample!B2:2<>"")

2. Dynamically Exclude Last Empty Rows and Columns Using INDIRECT

This INDIRECT approach is much simpler to code than the above.

Generic Formula: indirect(first_cell_in_range&last_non_empty_column_letter&last_non_empty_row_number)

Formula:

=indirect(ArrayFormula("Sample!A2:"&REGEXEXTRACT(address(1,match(2,1/(Sample!2:2<>"")),4),"[^\d]+")&match(2,1/(Sample!A:A<>""))))

Here to exclude first row, replace Sample!A2: with Sample!A3: and to exclude first column replace the same to Sample!B3:

When you use the above formula to dynamically excluding the last empty rows and columns, you may happen to one issue. What is that?

The reference Sample!A2: is fixed. So when you insert columns before the first column in the “Sample” sheet, it may cause issues.

3. The OFFSET Way to Resize an Open Range Dynamically

You have already learned dynamic ARRAY_CONSTRAIN and INDIRECT ways to resize an open range dynamically in Google Sheets. Here is the last method using OFFSET.

It follows the same dynamic ARRAY_CONSTRAIN method.

Syntax: OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

cell_reference (the first cell in the range): A2

offset_rows (number of rows to offset): 0

offset_columns (number of columns to offset): 0

height (height of rows): =ArrayFormula(MATCH(2,1/(Sample!A2:A<>""),1))

width (width of columns): =ArrayFormula(match(2,1/(Sample!A2:2<>"")))

Now to the coding part.

=offset(Sample!A2,0,0,ArrayFormula(MATCH(2,1/(Sample!A2:A<>""),1)),ArrayFormula(match(2,1/(Sample!A2:2<>""))))

How to Exclude Header Row and First Column?

Here you can follow the dynamic ARRAY_CONSTRAIN method.

To exclude the header row, make the following two changes.

1. Replace Sample!A2 with Sample!A3
2. Replace ArrayFormula(MATCH(2,1/(Sample!A2:A<>""),1)) with ArrayFormula(MATCH(2,1/(Sample!A3:A<>""),1))

To exclude first column;

1. Replace Sample!A3 with Sample!B3
2. Replace ArrayFormula(match(2,1/(Sample!A2:2<>""))) with ArrayFormula(match(2,1/(Sample!B2:2<>"")))

That’s all.

Thanks for the stay. Enjoy!