TAKE

What it does

TAKE(array, rows, [columns])

TAKE returns a contiguous corner block of an array.

Given an m × n array A, a row count r and a column count c, the result is the sub-array formed by:

• the first r rows if r > 0, or the last |r| rows if r < 0;
• the first c columns if c > 0, or the last |c| columns if c < 0.

The sign is the direction, and the magnitude is the count. That is the whole rule, and it is what makes TAKE(A, -1) — the last row — one of the most useful short formulas in modern Excel.

Counts larger than the array are the interesting case: TAKE(A, 100) over a five-row array returns five rows. TAKE clamps rather than padding or erroring, which is what makes it safe to use with a computed count. (EXPAND is the function that pads.)

Arguments

Microsoft documents TAKE(array, rows, [columns]).

The subtlety readers most often miss is the empty slot in the second position. TAKE(A, , 2) — with the row count written as nothing between two commas — means "all rows, first two columns". The signature marks rows as required, and it is required in the sense that you cannot write TAKE(A); but the omitted-slot spelling is legal and meaningful, and it is the idiomatic way to slice columns only.

That record exists because an implementation treated the empty slot as a missing required argument and returned an error. It is the clearest available illustration of why the value universe's Missing-versus-Empty distinction is not academic.

Try it

Quirks

Result and edge cases

The return kind is an array — always, including when it has one element.

• A count of zero produces an empty array. Microsoft documents that Excel returns #CALC! to indicate an empty array when rows or columns is 0.
• Counts exceeding the array clamp to the array's extent rather than padding.
• Non-integer counts are expected to truncate; the Handbook has not verified the direction or the resulting error for a non-numeric count.
• Errors and blanks inside the array are carried through as values. A direct error as array is the answer for the whole call, even with a count of zero: TAKE(#N/A,0) is #N/A (FN-HSTACK-001).

Errors

Documented by Microsoft:

#SPILL! arises at publication when the result cannot be placed — a host outcome rather than a TAKE outcome. Error values arriving in rows or columns propagate under the ordinary coercion rules.

How to implement

Notes for implementers

• Sign is direction; magnitude is count. Implementing negative counts as "count from the end, exclusive" is off by one and produces plausible output.
• Clamping is the documented behaviour for oversized counts — not an error, not padding.
• The empty-slot form in the leading required position must normalize to "all rows". This is a recorded defect class, verified against live Excel on 2026-04-10.
• The zero-count case has a specific outcome (#CALC!) and needs an explicit branch; producing a genuinely empty array and letting it leak downstream is not the same thing.

Rust code

6 take_fn "TAKE" (2, 3) [R "array", V "rows", V "columns"] |s, a, sv| {
    source_error(a)?;
    match sub_area(s, a, true)? {
        Some(reference) => Ok(reference),
        None => run(s, a, sv, k::eval_take_prepared),
    }
}

pub fn eval_take_prepared(args: &[CalcValue]) -> Result<CalcValue, DynamicArrayReshapeEvalError> {
    let array = materialize_array_arg(&args[0]);
    let row_count = match args.get(1) {
        Some(arg) if matches!(arg.core_ref(), CoreRef::Missing) && args.get(2).is_some() => {
            array.shape().rows as isize
        }
        Some(arg) => parse_integer(arg)?,
        None => return Err(DynamicArrayReshapeEvalError::InvalidCount),
    };
    let col_count = if let Some(arg) = args.get(2) {
        parse_integer(arg)?
    } else {
        array.shape().cols as isize
    };
    let (row_start, row_end) = take_span(array.shape().rows, row_count)?;
    let (col_start, col_end) = take_span(array.shape().cols, col_count)?;
    let rows = row_end - row_start;
    let cols = col_end - col_start;
    let mut cells = Vec::with_capacity(rows * cols);
    for row in row_start..row_end {
        for col in col_start..col_end {
            cells.push(array.get(row, col).expect("validated slice").clone());
        }
    }
    build_array(rows, cols, cells)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• DROP is the exact complement: DROP(A, k) removes what TAKE(A, k) keeps, and the two share the same sign convention and the same empty-slot behaviour. TAKE(A, 3) and DROP(A, 3) partition A's rows.
• CHOOSEROWS / CHOOSECOLS select arbitrary, non-contiguous indices; TAKE is the contiguous-from-an-edge special case.
• EXPAND is the growing counterpart: TAKE clamps down, EXPAND pads up.
• INDEX with omitted arguments can extract whole rows or columns and predates all of these; TAKE is the readable modern form.
• TRIMRANGE answers a different question — remove the blank margin, whatever size it is — where TAKE needs the count in advance.
• Readers confuse TAKE(A, -1) (the last row) with TAKE(A, 1) (the first). The sign carries a lot of meaning in a very small symbol.

Named variants

No named variants are recorded yet.