SORT

What it does

SORT(array, [sort_index], [sort_order], [by_col])

SORT permutes an array along one axis. It returns an array of exactly the same shape as its input — no rows are added, removed or merged — whose rows (or, with by_col set, whose columns) have been reordered so that the values in one designated column (or row) are in ascending or descending order.

Stated precisely: let A be an m × n array. With by_col false, SORT computes a permutation π of 1..m such that the sequence A[π(1), k], A[π(2), k], … is ordered, where k is sort_index, and returns the array B with B[i, j] = A[π(i), j]. The whole row moves with its key; that is the entire point, and it is what distinguishes SORT from sorting a single column in isolation.

Two facts follow from "permutation of the same shape" that are worth stating because readers routinely expect otherwise:

• SORT does not deduplicate. Equal keys keep all their rows. That is UNIQUE's job.
• SORT does not filter. Blanks are values and get a position in the order; they are not dropped. A range with trailing blank rows sorts those blanks somewhere, usually visibly.

The comparison order over mixed value kinds — numbers against text against logicals against blanks — is the part the documentation does not state, and it is the part the Handbook has not verified. Everything else about SORT is shape arithmetic.

Arguments

Microsoft documents SORT(array, [sort_index], [sort_order], [by_col]).

The argument most often misread is by_col, because it changes what sort_index indexes. With by_col false (the default), sort_index names a column and rows are permuted. With by_col true, sort_index names a row and columns are permuted. The flag and the index are coupled; changing one without the other silently sorts by the wrong key.

sort_index is relative to array, not to the worksheet. If array is C2:F20, then sort_index 1 means column C.

Try it

Quirks

Result and edge cases

The return kind is an array with the same dimensions as array, published as a dynamic-array spill.

• Ties. Whether the permutation is stable — whether rows with equal keys keep their original relative order — is not stated by the documentation. It is observable, it matters for reproducibility, and the Handbook has not pinned it.
• Mixed types in the key column. SORT needs a total order across value kinds. Microsoft documents such an order for MATCH (numbers, then text, then FALSE, then TRUE); whether SORT uses the same one is an open question here.
• Text ordering is locale-dependent. Sorting text means collation, and collation depends on the locale. This is the one part of SORT that cannot be characterized by a locale-free vector suite.
• sort_index out of range is expected to be an error rather than a clamp; the specific error is not documented.
• 1×1 and single-row/single-column arrays are trivially sorted but are exactly where array-versus-scalar shape mistakes surface — see the ROWS page for why a 1×1 result must stay an array.

Errors

Microsoft's page does not enumerate error conditions for SORT itself; it mentions #REF! arising in cross-workbook dynamic-array scenarios when the source workbook is closed and the formula is refreshed, which is a dynamic-array behaviour rather than a SORT behaviour.

Beyond that, the errors a reader will actually meet are #SPILL! when the result cannot be placed (a publication outcome, not a function outcome) and whatever SORT returns for an out-of-range sort_index, which the Handbook has not verified. Error values inside array are carried along as values — SORT selects and reorders, it does not evaluate — but where they sort to is unverified.

How to implement

Notes for implementers

• Sorting must move whole rows, not just the key column. An implementation that sorts the key and reassembles by index is fine; one that sorts each column independently is catastrophic and produces plausible-looking output.
• The omitted-slot call shape (SORT(a,,-1)) must normalize to the same default path as an absent trailing argument. This is a real, recorded defect class in this family.
• Stability is a decision, and an undecided one is a source of nondeterminism across implementations. Record whatever choice is made.
• The cross-kind comparison order is shared with every other ordering surface in the engine (SORTBY, LARGE, ordered MATCH, XMATCH's nearest modes). It should live in one place.
• Text collation is locale-dependent, which means SORT cannot be portable-reproducible without a declared collation; that declaration belongs in the flavour's contract, not in the kernel.

Rust code

3 sort_fn "SORT" (1, 4) [R "array", R "sort_index", R "sort_order", V "by_col"] |s, a, sv| {
    sort(s, a, sv)
}

/// SORT: by the sort_index columns (rows, `by_col`), each with its order; an array of indices
/// with an array of orders, or one order for all.
fn sort(site: &Site<'_>, a: &[CalcValue], sv: &mut EvalServices<'_>) -> Step<CalcValue> {
    let args = prepared(site, a, sv)?;
    let array = as_array(&args[0]);
    let by_col = flag(site, args.get(3))?;
    let ArrayShape { rows, cols } = array.shape();
    let (len, width) = if by_col { (cols, rows) } else { (rows, cols) };
    let indices: Vec<CalcValue> = match args.get(1) {
        Some(value) => match value.as_array() {
            Some(list) => list.iter_row_major().cloned().collect(),
            None => vec![value.clone()],
        },
        None => vec![CalcValue::missing()],
    };
    let orders: Vec<CalcValue> = match args.get(2) {
        Some(value) => match value.as_array() {
            Some(list) => list.iter_row_major().cloned().collect(),
            None => vec![value.clone()],
        },
        None => vec![CalcValue::missing()],
    };
    if orders.len() > 1 && orders.len() != indices.len() {
        return fail(WorksheetErrorCode::Value);
    }
    let mut keys = Vec::with_capacity(indices.len());
    for (n, index) in indices.iter().enumerate() {
        let at = match integer(site, Some(index))? {
            None => 0,
            Some(i) if i >= 1 && (i as usize) <= width => i as usize - 1,
            Some(_) => return fail(WorksheetErrorCode::Value),
        };
        let down = descending(site, orders.get(if orders.len() == 1 { 0 } else { n }))?;
        let key: Vec<CalcValue> = (0..len)
            .map(|i| {
                let (r, c) = if by_col { (at, i) } else { (i, at) };
                array.get(r, c).cloned().unwrap_or_else(CalcValue::empty)
            })
            .collect();
        keys.push((key, down));
    }
    let order = sorted_indices(site, len, &keys)?;
    reordered(&array, &order, by_col)
}

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

• SORTBY is the sibling that sorts by external keys: one or more arrays parallel to the data but not part of it, each with its own direction. SORT is the special case where the key lives inside the array. SORTBY also supports multi-level sorts, which SORT does not.
• UNIQUE removes duplicates; SORT(UNIQUE(x)) is the standard composition and the two are frequently confused because both "tidy up a list".
• FILTER selects rows; SORT reorders them. SORT(FILTER(...)) is the usual pipeline.
• LARGE / SMALL / RANK answer order questions about a single vector without materializing a sorted copy.
• The legacy equivalent is the Data ▸ Sort command, which mutates the sheet. SORT is the formula form: the source is untouched and the result recomputes.

Named variants

No named variants are recorded yet.