Skip to Content

How to Use the ARRAY_CONSTRAIN Function in Google Sheets

If you are regularly working with arrays in Google Sheets, there are a number of built-in ARRAY functions that are useful to know about.

One such function is the ARRAY_CONSTRAIN function.

In this tutorial, I will show you how to use the ARRAY_CONSTRAIN Function in Google Sheets

Table of Contents

ARRAY_CONSTRAIN Syntax

The ARRAY_CONSTRAIN function will return an array according to the specified number of rows and columns. It constrains the array to a specific size. This is useful if you only want to return a portion of the data.

The syntax of the ARRAY_CONSTRAIN function is:

=ARRAY_CONSTRAIN(input_range, num_rows, num_cols)

  • input_range – this is the original data range that you want to return only a specific number of rows and columns from
  • num_rows – this is the number of rows to return
  • num_cols – this is the number of columns to return

Using ARRAY_CONSTRAIN

Using ARRAY_CONSTRAIN is simple. Select the cell where you want the constrained version of your input range to return and then put in the input range, the rows, and the columns that you want returned:

ARRAY_CONSTRAIN-1

The formula I used in this example is =ARRAY_CONSTRAIN(A1:E9,5,5).

This will return 5 rows and 5 columsn from my input range of A1:E9

ARRAY_CONSTRAIN-2

Closing Thoughts

ARRAY_CONSTRAIN is a quick way to return a specific range of data. It is most often used in combination with other functions that returns arrays when you only want to work with a specific portion of the data.

More Google Sheets Tutorials:
How to Use ARRAYFORMULA
How to Use the MMULT Function