ARRAY_CONSTRAIN Function in Google Sheets

Limit the number of rows and columns in an array using the ARRAY_CONSTRAIN function.

ARRAY_CONSTRAIN Function

The ARRAY_CONSTRAIN function in Google Sheets is used to limit the number of rows and columns of an array to a specified size.

Syntax

ARRAY_CONSTRAIN(array, num_rows, num_cols)
  • array: The range or array to be constrained.
  • num_rows: The maximum number of rows to display.
  • num_cols: The maximum number of columns to display.

Examples

  1. Basic Example

Constrain an array in range A1:D10 to only 5 rows and 2 columns:

=ARRAY_CONSTRAIN(A1:D10, 5, 2)
  1. Full Array Constraint

If you have a dynamic array in B2:F6 and want to show only the first 3 rows and 3 columns:

=ARRAY_CONSTRAIN(B2:F6, 3, 3)

Notes

  • This function is useful when working with large datasets but needing to display or operate on a smaller subset.
  • CHOOSECOLS: Select specific columns from an array.
  • CHOOSEROWS: Select specific rows from an array.