Array Constrain Excel Arrays with TAKE or INDEX

=TAKE(
    FILTER('P&L'!C5:O250,'P&L'!B5:B250="Base Case"),
    12,
    13
)

This returns the first 12 matching rows and 13 columns from the P&L tab. It is Excel's closest equivalent to Google Sheets ARRAY_CONSTRAIN, although the syntax and edge cases aren't identical.

As of July 2026, Microsoft documents TAKE for Microsoft 365, Excel for the web, Excel for Mac, and Excel 2024. Workbooks that must survive Excel 2021 or older desktop installations still need an INDEX fallback.

What is the Excel equivalent of ARRAY_CONSTRAIN?

Google Sheets uses:

=ARRAY_CONSTRAIN(source_range,number_of_rows,number_of_columns)

The direct Excel pattern is:

=TAKE(source_range,number_of_rows,number_of_columns)

For example, a bank syndicate DCF might contain 60 projected periods across 15 scenarios, while the credit memo needs only the first 20 quarters and the first 6 output columns:

=TAKE('DCF Output'!B6:P65,20,6)

TAKE is better than wrapping the output in IF, blanking unwanted cells, or pointing a chart at an oversized range. It constrains the spilled array itself, so downstream formulas see a 20-row by 6-column result rather than a larger range decorated with empty strings.

Microsoft defines TAKE as returning “a specified number of contiguous rows or columns from the start or end of an array” in its TAKE function documentation.

Array constrain Excel options compared

TAKE wins when compatibility isn't a concern. INDEX remains the safer choice when treasury, lenders, or portfolio-company teams may open the workbook in older desktop versions.

Manual ranges are acceptable when the report architecture is genuinely fixed. They become dangerous when “fixed” means somebody quietly inserts 2 months before the quarterly board meeting.

How to constrain Excel arrays by rows and columns

The second argument controls rows. The optional third argument controls columns.

=TAKE('Returns Analysis'!B8:Q67,12,8)

That formula returns 12 rows and 8 columns from the upper-left corner.

Negative arguments count from the bottom or right:

=TAKE('Cash Flow'!B6:M65,-12,-4)

This returns the last 12 rows and last 4 columns. It is useful for a rolling liquidity view when the underlying cash-flow output grows each month.

The dimensions remain explicit. If a returns analysis contains 15 scenarios across 60 periods, TAKE(...,12,8) always spills 96 cells. That makes the output easier to audit than a chain of IF(COLUMN()<=8,...) tests copied across a presentation tab.

There is one trap: 0 isn't a request for no rows. Microsoft's documentation says, “Excel returns a #CALC! error to indicate an empty array.” A row-count input sourced from assumptions therefore needs a floor if zero is possible:

=TAKE('Operating Model'!B7:P66,MAX(1,Assumptions!$B$12),8)

How to constrain a filtered Excel array

TAKE becomes more useful when it sits outside FILTER, SORT, or UNIQUE. The inner function builds the result set; TAKE caps what reaches the reporting tab.

Suppose an 186-SKU contribution-margin schedule feeds a quarterly board pack. Management wants the 10 lowest-margin SKUs, not the entire operating table:

=TAKE(
    SORTBY(
        FILTER(
            'SKU Economics'!A6:H191,
            'SKU Economics'!H6:H191<Assumptions!$B$18
        ),
        FILTER(
            'SKU Economics'!H6:H191,
            'SKU Economics'!H6:H191<Assumptions!$B$18
        ),
        1
    ),
    10,
    8
)

If Assumptions!$B$18 contains a 38.5% contribution-margin threshold, the formula filters below-threshold SKUs, sorts them from worst to best, and returns at most 10 rows across 8 columns.

The order matters. Applying TAKE before SORTBY would select the first 10 source rows and then rank only those rows. That answers a different question, and it can survive review because the output still looks perfectly tidy.

This pattern also works for multi-tab date filtering:

=TAKE(
    FILTER(
        'P&L'!B:C,
        ('P&L'!B:B>=Assumptions!$B$3)*
        ('P&L'!B:B<=Assumptions!$B$4)
    ),
    12,
    2
)

For an aggregate rather than a returned array, keep the conventional cross-tab formula:

=SUMIFS('P&L'!C:C,'P&L'!B:B,">="&Assumptions!$B$3)

Constraining the displayed data doesn't change the aggregation logic. It controls presentation and downstream spill dependencies, not the underlying source population.

How to array constrain Excel without TAKE

For a dynamic-array-compatible fallback, combine INDEX and SEQUENCE:

=INDEX(
    'DCF Output'!B6:P65,
    SEQUENCE(MIN(20,ROWS('DCF Output'!B6:P65))),
    SEQUENCE(,MIN(6,COLUMNS('DCF Output'!B6:P65)))
)

The 2 MIN tests prevent the requested dimensions from exceeding the source dimensions. That matters when a sensitivity output sometimes contains only 4 populated scenarios rather than the expected 6.

For older array behavior, use INDEX to define the lower-right corner of a range:

='DCF Output'!B6:INDEX('DCF Output'!B6:P65,20,6)

This creates a reference from B6 through the cell at row 20, column 6 of the source range. It is compact and works well inside functions that accept a reference:

=SUM('DCF Output'!B6:INDEX('DCF Output'!B6:P65,20,6))

It does not behave exactly like a modern spilled array in every context. Test it in the workbook versions used by the actual recipients, not merely the version installed on the model owner's laptop.

When DROP should sit beside TAKE

TAKE selects an edge of an array. DROP removes an edge. Together, they produce a window from the middle.

A 5-year forecast might contain 12 historical months followed by 60 projected months. To return forecast year 2, drop the first 24 columns and take the next 12:

=TAKE(DROP('Monthly Model'!C10:BZ45,,24),,12)

Microsoft's DROP function documentation confirms that negative values exclude rows or columns from the end of an array. That makes DROP useful for removing trailing terminal-value columns before constraining a DCF exhibit.

A quarterly board pack might use the same pattern to isolate 8 quarters from a monthly model:

=TAKE(
    DROP('Quarterly Summary'!B5:AZ30,,Assumptions!$B$20),
    26,
    8
)

Here, Assumptions!$B$20 controls the starting column. The output remains 26 rows by 8 quarters as the reporting window rolls.

Why constrained arrays matter in financial models

Excel worksheets support 1,048,576 rows and 16,384 columns, according to Microsoft's Excel specifications and limits. Those limits aren't an invitation to feed entire-column dynamic arrays through every tab.

A full-column formula can examine more than 1 million cells when the real schedule contains 4,800 transactions. Repeat that across 20 scenarios, and the workbook is doing a great deal of work to preserve the author's reluctance to define a range.

Constraining arrays also reduces spill collisions. Microsoft's dynamic array guidance explains that blocked spill ranges return #SPILL!. A fixed 12-row output for a dashboard is easier to reserve than an uncapped filter whose height jumps from 9 to 43 rows after a data refresh.

The non-obvious benefit is model governance. If a valuation summary is designed to show 8 comparable companies and a 14.2x median EBITDA multiple, capping the display at 8 rows forces an explicit ranking rule. An uncapped list lets the exhibit drift while preserving the comforting appearance of automation.

When not to constrain an Excel array

Don't use TAKE to hide incomplete source logic. If a runway sensitivity excludes new-hire cohorts because the source range stops 6 rows early, constraining the polished output merely conceals the break.

Don't cap an audit schedule where completeness is the point. Debt balances, intercompany eliminations, and three-statement checks should include the whole controlled population. A prettier spill range isn't worth a balance sheet that misses $4.2M of lease liabilities.

Be careful with top-N outputs too. TAKE(FILTER(...),10) returns the first 10 qualifying records in source order. If the decision requires the largest exposures, weakest margins, or earliest maturities, sort explicitly before taking the first 10.

For related Google Sheets spill patterns, see the ARRAYFORMULA reference for financial models. The design principle carries across platforms: control the shape of the calculation deliberately, even when the functions differ.