TRANSPOSE

What it does

TRANSPOSE(array)

TRANSPOSE exchanges an array's axes. For an m × n input A, the result B is n × m with

B[j, i] = A[i, j]     for all i ∈ 1..m, j ∈ 1..n

That is the complete definition, and it is one of the very few worksheet functions whose mathematics is exactly the mathematics of the same-named operation in linear algebra. A row becomes a column, a column becomes a row, and a rectangle turns on its side. No values change, none are added or removed, and the operation is its own inverse: TRANSPOSE(TRANSPOSE(A)) = A.

The historical importance of TRANSPOSE is out of proportion to its simplicity. Before dynamic arrays it was one of the very few functions whose natural result was an array, which made it the standard teaching example for Ctrl+Shift+Enter array entry — you had to select the target rectangle first, with the dimensions swapped, before committing the formula. On a modern Excel it simply spills, and that entire ritual is gone.

Arguments

TRANSPOSE(array) — exactly one argument, required, no options.

array — the range or array to transpose. A reference is admissible and is resolved to its values; unlike OFFSET or ROW, TRANSPOSE has no interest in the address.

The only thing regularly misunderstood about the argument is what comes back: readers who pass a single column expect a single column with a different orientation on screen, which is exactly what they get, and readers who pass a rectangle sometimes expect a rotation rather than a transposition. Transposition is a reflection across the main diagonal, not a 90-degree rotation: the top-left element stays put, and the anti-diagonal order is not reversed.

Try it

Quirks

Result and edge cases

The return kind is an array with the input's dimensions exchanged.

• Values are moved, not interpreted. Errors, text, logicals and blanks all survive transposition unchanged. TRANSPOSE does not coerce.
• What happens to blanks is the one genuine question. A blank cell in a range has no value of its own, and the general model says Empty is admitted at the raw-return boundary but not at the published-result boundary, normalizing to zero on the way out (the value universe). A transposed blank cell that publishes as 0 is therefore expected behaviour under the shared model rather than a TRANSPOSE quirk — but the Handbook has not verified it for this function.
• A 1×1 input transposes to a 1×1 array, and must remain array-shaped.
• A single-row or single-column input is the common case and exercises none of the interesting index arithmetic; a rectangular input does.
• Very large arrays hit the grid limits at publication.
• Dynamic-array publication and #SPILL! are host-side adaptation, described in the call pipeline.

Errors

Microsoft's page for TRANSPOSE does not enumerate error conditions, which is consistent with a total function on arrays: every rectangular array has a transpose.

The errors a reader meets in practice come from elsewhere — #SPILL! when the swapped dimensions do not fit, #VALUE! on a legacy grid when an array result was not array-entered, and whatever error values were already inside the array and are simply carried across.

How to implement

Notes for implementers

• The operation is index arithmetic and nothing else. Any implementation that copies values through a coercion step is doing more than the function asks and can only introduce divergence.
• Row-major storage makes transposition a strided copy; for large arrays the cache behaviour differs sharply between the naive and the blocked implementation, but the result must not.
• TRANSPOSE is an excellent test instrument for other functions precisely because it is exact and self-inverse: TRANSPOSE(TRANSPOSE(f(x))) = f(x) is a metamorphic check that needs no oracle.
• Blank handling should follow the engine's general empty-cell rule rather than acquiring a local convention.

Rust code

8 transpose_fn "TRANSPOSE" (1, 1) [R "array"] |s, a, sv| { transpose(s, a, sv) }

fn transpose(site: &Site<'_>, a: &[CalcValue], sv: &mut EvalServices<'_>) -> Step<CalcValue> {
    let g = grid(site, a, 0, sv)?;
    if !g.held {
        return Ok(a[0].clone());
    }
    let mut rows = Vec::with_capacity(g.cols);
    for c in 0..g.cols {
        let mut row = Vec::with_capacity(g.rows);
        for r in 0..g.rows {
            let value = g.cells[r * g.cols + c].clone();
            // A blank cell transposes as 0, as an array formula over a blank cell gives.
            row.push(if matches!(value.core_ref(), CoreRef::Empty) {
                CalcValue::number(0.0)
            } else {
                value
            });
        }
        rows.push(row);
    }
    if rows.len() == 1 && rows[0].len() == 1 {
        return Ok(rows.remove(0).remove(0));
    }
    CalcArray::from_rows(rows)
        .map(CalcValue::array)
        .ok_or_else(|| site.not_modelled(None))
}

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

• TOCOL / TOROW flatten rather than transpose; the pair are related by TOROW(x, i, s) = TRANSPOSE(TOCOL(x, i, s)), which makes TRANSPOSE the bridge between the two flattening directions.
• VSTACK / HSTACK are similarly related through transposition: HSTACK(a, b) = TRANSPOSE(VSTACK(TRANSPOSE(a), TRANSPOSE(b))).
• MMULT, MINVERSE, MDETERM are the rest of the matrix family; TRANSPOSE is the one member that is pure index arithmetic with no floating-point content, and therefore the one member with no accuracy question at all.
• Paste Special ▸ Transpose is the destructive command form: it writes values, does not recompute, and is what TRANSPOSE replaces for live data.
• WRAPROWS / WRAPCOLS reshape rather than reflect; a reader wanting m × n from n × m data in a different element order wants those, not this.

Named variants

No named variants are recorded yet.