What does ARRAY_CONSTRAIN do in Google Sheets?

`ARRAY_CONSTRAIN(array, num_rows, num_cols)` trims any array or range to exactly the number of rows and columns you specify. It always takes the top-left portion of the array. If the source has fewer rows than `num_rows`, it returns however many rows exist without throwing an error - unlike `INDEX`, which returns `#REF!` in the same situation.

Why use ARRAY_CONSTRAIN with ARRAYFORMULA instead of just anchoring the range?

An anchored range like `A2:A50` breaks when source data grows or shrinks past 49 rows. `ARRAYFORMULA` inside `ARRAY_CONSTRAIN` keeps the computation dynamic (new data is automatically included in the inner formula) while keeping the output size fixed (the outer constraint ensures exactly N rows land in the model block). This matters when downstream formulas reference fixed row ranges, like a `=SUM(Summary!C2:C9)` in a returns analysis tab.

Does ARRAY_CONSTRAIN work with SORT and FILTER?

Yes, and this is the most common pattern. `SORT(FILTER(...))` returns a variable-length result depending on how many rows match the filter criteria. Wrapping in `ARRAY_CONSTRAIN` locks the output to a fixed size - useful for "top N by revenue" summaries in board packs or periodic reports. Example: `=ARRAY_CONSTRAIN(SORT(FILTER('P&L'!B:D, 'P&L'!A:A=Assumptions!$B$3), 3, FALSE), 10, 3)` always outputs exactly 10 rows and 3 columns.

What is the Excel equivalent of ARRAY_CONSTRAIN?

`TAKE()`, introduced in Microsoft 365 in 2022. `=TAKE(array, rows, cols)` works similarly but also supports negative row/column values to pull from the end of an array. `ARRAY_CONSTRAIN` doesn't support negative indexing. If you need to port a Sheets model to Excel, replace `ARRAY_CONSTRAIN(x, n, m)` with `TAKE(x, n, m)` - the argument order is identical for positive values.

Does ARRAY_CONSTRAIN slow down Google Sheets recalculation?

No - `ARRAY_CONSTRAIN` is a non-volatile, lightweight trim applied after the inner formula evaluates. It doesn't add meaningful recalculation overhead. Recalc performance is determined by the formula inside it, particularly any `SUMIFS` or `VLOOKUP` calls running across large ranges. In models with under 15,000 source rows, the full combination typically recalculates in 1-3 seconds. Performance issues at 100,000+ rows are attributable to the inner formula, not the constraint wrapper.

ARRAY_CONSTRAIN + ARRAYFORMULA in Google Sheets (2026)

Syntax: =ARRAY_CONSTRAIN(array_or_range, num_rows, num_cols)

That's the whole function. The power comes from what you wrap inside it.

Why Unbounded ARRAYFORMULA Output Is a Model Risk

ARRAYFORMULA returns as many rows as the source data has. When your source is a raw transaction tab with 4,200 rows this quarter and 3,800 last quarter, the output size fluctuates. Any formula downstream that references a fixed range - =SUM(Summary!B2:B9), say, or a named range anchored to 8 rows - breaks the moment your array spills past its expected boundary.

In a three-statement model, this matters. Your FCFF tab references the P&L tab's operating expense block. If that block is an ARRAYFORMULA that grows unconstrained each time the source data refreshes, your cash flow statement ties out one month and silently doesn't the next.

ARRAY_CONSTRAIN is the guardrail. Wrap it around the array and the output stays exactly the size you specified, regardless of what the underlying data does.

Basic Usage: Capping a FILTER Result

Say you're building a board pack summary that shows the top 5 cost centers by Q2 2026 spend, pulled from a raw P&L tab:

=ARRAY_CONSTRAIN(
  SORT(
    FILTER('P&L'!B:D, 'P&L'!A:A="Q2 2026"),
    3, FALSE
  ),
  5, 3
)

This filters Q2 rows, sorts descending by the third column (spend), then hands the result to ARRAY_CONSTRAIN which outputs exactly 5 rows and 3 columns. If Q2 has 47 cost centers, you get 5. If it somehow has 3 (mid-year spin-off, whatever), you get 3 - ARRAY_CONSTRAIN never pads with zeros or throws an error when the source is smaller than the constraint. It just returns what's there.

That last behavior is worth knowing. INDEX with an out-of-range row number returns #REF!. ARRAY_CONSTRAIN with num_rows larger than the array just returns the whole array. Safer for dynamic inputs.

Combining With ARRAYFORMULA for Calculated Columns

The more common pattern in financial models is using ARRAYFORMULA to derive a calculated column, then constraining it to the exact rows your model expects.

Here's a contribution margin calculation across SKUs, constrained to 12 rows for a fixed summary block:

=ARRAY_CONSTRAIN(
  ARRAYFORMULA(
    SUMIFS('Revenue'!D:D, 'Revenue'!B:B, 'SKU Master'!A2:A, 'Revenue'!C:C, Assumptions!$B$3)
    - SUMIFS('COGS'!D:D, 'COGS'!B:B, 'SKU Master'!A2:A, 'COGS'!C:C, Assumptions!$B$3)
  ),
  12, 1
)

This calculates contribution margin per SKU for the period in Assumptions!$B$3 (say, "Q2 2026"), then clamps the output to 12 rows. If SKU Master has 18 active SKUs, you still get 12 - which matches the 12-row block the Returns Analysis tab references. The other 6 SKUs exist in the source but don't touch the model block.

Without ARRAY_CONSTRAIN, adding 2 new SKUs to SKU Master mid-quarter would push the array output into row 14, right through whatever's sitting there.

The Performance Question

ARRAY_CONSTRAIN itself is computationally cheap - it's a post-processing trim on an already-evaluated array, not an additional computation pass. According to Google's Sheets documentation (as of July 2026), it's classified as a non-volatile function, meaning it doesn't recalculate on every sheet change the way NOW() or OFFSET() does. The expensive part is whatever you put inside it.

That said, wrapping a large ARRAYFORMULA that calls SUMIFS across 50,000 rows in ARRAY_CONSTRAIN doesn't make the SUMIFS faster. It just limits how much of that result gets rendered. If recalc time is your bottleneck, the fix is the inner formula, not the constraint.

For typical FP&A workloads - 5,000 to 15,000 transaction rows, 8 to 15 linked tabs - the combination runs in under 3 seconds on recalculation in testing. Models pushing 200,000+ rows start showing 8-12 second recalc times regardless of whether ARRAY_CONSTRAIN is involved.

ARRAY_CONSTRAIN vs. INDEX for Slicing Arrays

Both can cap an array's rows. The difference is behavior at the boundary.

Scenario	`ARRAY_CONSTRAIN`	`INDEX`
Source has more rows than limit	Returns first N rows	Returns first N rows
Source has fewer rows than limit	Returns all rows (no error)	Returns `#REF!`
Source is empty	Returns empty	Returns `#REF!`
Syntax complexity	Lower	Higher

For dynamic sources where the row count might drop below your expected limit - think runway models where headcount assumptions change, or SKU catalogs where products get deprecated - ARRAY_CONSTRAIN is safer. INDEX is better when you specifically need to assert that N rows must exist (because a #REF! will surface the data gap instead of silently hiding it).

Excel Equivalent

ARRAY_CONSTRAIN doesn't exist in Excel. The equivalent since Microsoft 365's 2022 dynamic array update is TAKE():

=TAKE(SORT(FILTER(Revenue[Amount], Revenue[Period]="Q2 2026"), 1, -1), 5)

TAKE accepts negative values to pull from the end of an array, which ARRAY_CONSTRAIN can't do without SORT preprocessing. If your model lives in both Sheets and Excel, this is one of the sharper edge cases to flag - the functions are similar but not interchangeable, and the Sheets version predates Excel's by several years.

Where This Actually Shows Up in Models

The highest-value use cases I've seen:

Board pack summaries. A fixed 10-row "Top Customers by Revenue" block that always stays 10 rows even when the CRM data has 340 customers. The block is referenced by the deck template; it can't change size.

Sensitivity tables. A ARRAYFORMULA computing IRR across 8 leverage scenarios, constrained to 8x1. The scenario labels are hardcoded in column A; the formula block has to match exactly.

Rolling period displays. A 13-week cash flow display that always shows exactly 13 columns regardless of how many weeks of actuals exist in the source tab.

In each case, ARRAY_CONSTRAIN is doing exactly one job: making a dynamic formula output a predictable size. That predictability is what lets the rest of the model reference it safely.

ModelMonkey can write and update formulas like these directly inside Sheets. If you're maintaining a board pack where the constraint logic changes quarterly (different top-N cutoffs, different period filters), having an AI rewrite the inner FILTER and SORT arguments without touching the ARRAY_CONSTRAIN wrapper is faster than editing nested formulas by hand.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.