ARRAY_CONSTRAIN(input_range, num_rows, num_cols)One free generation or explanation per browser, shared across Excel and Google Sheets. Account usage limits apply afterward.
input_range:
The range of cells that you want to constrain to a specific number of rows and columns.
num_rows:
The number of rows to include in the constrained output from the input_range.
num_cols:
The number of columns to include in the constrained output from the input_range.
The ARRAY_CONSTRAIN function in Google Sheets is a useful tool that allows you to limit the size of an array or range to a specified number of rows and columns. This function is particularly beneficial when you want to work with a subset of data without altering the original dataset. It helps in creating dynamic ranges that can be used in other formulas or for data visualization.
In simpler terms, ARRAY_CONSTRAIN enables you to take a larger dataset and restrict it to a smaller, more manageable size, making it easier to analyze or present.
Imagine you have a dataset of sales data that includes multiple columns, but you only want to display the top 5 sales records.
| SaleID | ProductName | Quantity | Price | Total |
|---|---|---|---|---|
| 1 | Widget A | 3 | 25.00 | 75.00 |
| 2 | Widget B | 5 | 30.00 | 150.00 |
| 3 | Widget C | 2 | 15.00 | 30.00 |
| 4 | Widget D | 4 | 20.00 | 80.00 |
| 5 | Widget E | 1 | 50.00 | 50.00 |
| 6 | Widget F | 6 | 10.00 | 60.00 |
You want to display only the top 5 sales records based on the Total column.
To achieve this, you would use the ARRAY_CONSTRAIN function as follows:
=ARRAY_CONSTRAIN(SalesData!A:E, 5, 5)In this formula:
SalesData!A:E is the range of the original sales data.5 is the number of rows you want to include in the constrained array.5 is the number of columns you want to include in the constrained array.When you apply the ARRAY_CONSTRAIN formula to the Sales Data Table, the output would look like this:
| SaleID | ProductName | Quantity | Price | Total |
|---|---|---|---|---|
| 1 | Widget A | 3 | 25.00 | 75.00 |
| 2 | Widget B | 5 | 30.00 | 150.00 |
| 3 | Widget C | 2 | 15.00 | 30.00 |
| 4 | Widget D | 4 | 20.00 | 80.00 |
| 5 | Widget E | 1 | 50.00 | 50.00 |
The ARRAY_CONSTRAIN function effectively limits the output to just the top 5 records from the original dataset.
ARRAY_CONSTRAIN is beneficial because it allows you to focus on a specific portion of your data without modifying the original dataset. This can be particularly useful for reporting, data analysis, or when creating charts and graphs. It simplifies the process of working with large datasets by allowing you to create dynamic ranges that can be easily referenced in other formulas.
ARRAY_CONSTRAIN is an essential function for anyone working with data in Google Sheets, providing a powerful way to enhance data management and reporting capabilities.
Happy spreadsheeting!
You have a sales report with multiple columns, but you only want to display the top 10 sales figures. Use ARRAY_CONSTRAIN to limit the output to the first 10 rows of your sorted sales data, making it easy to focus on the highest performers without clutter.
Sign up for Better Analyst and get unlimited AI-powered formula generation, explanations, and advanced data analysis.
Browse our complete library of Google Sheets formula guides
Compare your options
Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose.
AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.