EXPAND

What it does

EXPAND(array, rows, [columns], [pad_with])

EXPAND(array, rows, [columns], [pad_with]) returns an array of the requested dimensions whose top-left region is array and whose remaining cells are all pad_with.

The operation is one-directional: it grows, it never shrinks. The original elements keep their positions relative to the top-left corner, and every new cell — to the right, below, and in the bottom-right corner block — receives the same pad value. Microsoft documents the default pad_with as #N/A, which is a deliberate choice rather than a convenience: #N/A is the value model's "not available" marker, so an unpadded EXPAND produces a result whose synthetic cells announce themselves rather than masquerading as zeros or blanks.

EXPAND is the growing complement of TAKE/DROP and the shape-fixing tool for the whole dynamic-array family: when two arrays must be conformable — for HSTACK alignment, for an elementwise operation, for a fixed-size output block — EXPAND is how you make them so explicitly instead of relying on broadcasting.

Arguments

Two positions are commonly misread.

rows and columns are target dimensions, not increments. EXPAND(a, 3) means "make it three rows tall", not "add three rows". This is the opposite convention from DROP and TAKE, whose counts are increments, and the inconsistency is the most reliable source of error when the two are used in the same formula.

Omitting columns is not the same as passing the current width in one respect: it is shorter and it tracks a changing input, but the documented effect is the same — the column axis is left as it is. Passing an explicit width smaller than the current width is an error, not a truncation.

Try it

Quirks

Result and edge cases

Return kind: Array, of exactly the requested dimensions. The result spills; spilling is host-side adaptation (the call pipeline).

• rows or columns equal to the current extent is a no-op on that axis.
• pad_with may be any scalar: a number, text, a logical, or an error value. Passing "" is the common way to get visually blank padding, and it produces empty text, not the Empty kind — the value universe keeps those distinct, and downstream ISBLANK will disagree with your eyes.
• Whether pad_with may be an array is not established here. If it can, the padding semantics are no longer "one value everywhere".
• Element errors inside an array in array pass through unchanged; nothing collapses (error_collapse_profile: None). An error written directly, or held in a single referenced cell, is the answer for the whole call: EXPAND(Data!C1,2,2,"pad") with #N/A in Data!C1 is #N/A (FN-HSTACK-001).

Errors

Microsoft's page documents that EXPAND returns #VALUE! when rows or columns is smaller than the array's current row or column count. That is the characteristic error of this function and it follows from its one-directional design: there is no shrinking mode to fall back on.

The documented default pad_with of #N/A means an ordinary successful EXPAND routinely contains error values without being an error. That distinction matters downstream: an aggregate scanning the padded result will meet #N/A and, under the propagation rule in coercion and lifting, surface it. Supplying an explicit pad_with — 0, "" — is usually what a formula that feeds an aggregate actually wants.

An error value supplied as rows or columns propagates by the same universal rule.

How to implement

Notes for implementers

• The pad region is an L, not a rectangle. Growing both axes creates three new regions (right, below, bottom-right). Implementations that fill row-major over the whole output and copy the original in afterwards get this right by construction; implementations that fill "the new rows" and "the new columns" separately double-fill or miss the corner.
• Validate before allocating. The documented #VALUE! condition — a target smaller than the current extent — must be checked before any output buffer is sized, or a shrink request becomes a truncation bug.
• pad_with must be copied as a value of any kind, including error values, without coercion. An implementation that types the fill as a number cannot express the documented default.
• Large targets are a resource question. EXPAND is the easiest function in the family to ask for an enormous result from, since the size is an argument rather than derived from the input. Whatever ceiling the host imposes is the host's, not this function's, and the Handbook has not established what Excel does at that ceiling.
• The module is shared with the rest of the dynamic-array reshapers.

Rust code

16 expand_fn "EXPAND" (2, 4) [R "array", V "rows", V "columns", R "pad_with"] |s, a, sv| {
    source_error(a)?;
    let mut args = Vec::with_capacity(a.len());
    args.push(prepared_one(s, a, 0, sv)?);
    // Fn2 EXPAND reads a one-cell reference's scalar error before padding. An actual
    // one-cell array keeps its array origin; HSTACK/VSTACK retain reference errors as cells.
    if a[0].as_reference().is_some()
        && let Some(array) = args[0].as_array()
        && array.shape() == (ArrayShape { rows: 1, cols: 1 })
    {
        scalar_error(array.get(0, 0).expect("one cell"))?;
    }
    for index in 1..a.len() { args.push(prepared_one(s, a, index, sv)?); }
    // Preserve the previous value admission of a one-cell reference pad. Computed
    // arrays reach the kernel whole (w090-successor-00071-expand-arg4).
    if a.get(3).is_some_and(|value| value.as_reference().is_some())
        && let Some(array) = args[3].as_array()
        && array.shape() == (ArrayShape { rows: 1, cols: 1 })
    {
        args[3] = array.get(0, 0).expect("one cell").clone();
    }
    result(s, k::eval_expand_prepared(&args))
}

pub fn eval_expand_prepared(args: &[CalcValue]) -> Result<CalcValue, DynamicArrayReshapeEvalError> {
    let array = materialize_array_arg(&args[0]);
    let target_rows = parse_positive_integer(&args[1])?;
    let target_cols = if let Some(arg) = args.get(2) {
        parse_positive_integer(arg)?
    } else {
        array.shape().cols
    };
    if target_rows < array.shape().rows || target_cols < array.shape().cols {
        return Err(DynamicArrayReshapeEvalError::DimensionTooSmall);
    }

    let pad_cell = if let Some(arg) = args.get(3) {
        if matches!(arg.core_ref(), CoreRef::Array(_)) {
            return Err(DynamicArrayReshapeEvalError::Preparation(
                CoercionError::UnsupportedValueKind("scalar_pad"),
            ));
        }
        scalar_cell(arg)
    } else {
        CalcValue::error(WorksheetErrorCode::NA)
    };
    let mut cells = Vec::with_capacity(target_rows * target_cols);
    for row in 0..target_rows {
        for col in 0..target_cols {
            let cell = array
                .get(row, col)
                .cloned()
                .unwrap_or_else(|| pad_cell.clone());
            cells.push(cell);
        }
    }
    build_array(target_rows, target_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

• TAKE and DROP shrink where EXPAND grows, but note the argument-convention difference described above: their counts are increments, EXPAND's are targets.
• HSTACK and VSTACK pad implicitly with #N/A when the pieces do not conform. EXPAND makes that padding explicit and lets you choose the fill value — which is the usual reason to reach for it before stacking.
• IFERROR is the standard partner when the #N/A default is not wanted after the fact; supplying pad_with is the cheaper alternative.
• SEQUENCE builds an array of a target shape from nothing; EXPAND reshapes one you have.
• MAKEARRAY and the LAMBDA helpers construct padded results generatively, which is the route to take when the pad value depends on position rather than being constant.

Named variants

No named variants are recorded yet.