Range reference (:)

What it does

A:B

A : B takes two references and returns the smallest rectangular reference that contains both — the bounding box, not the pair.

This is the most-used function in Excel and one of the least described. A1:B10 is not notation; it is a call. Chapter 03 says so explicitly: every evaluable operator is a function with its own identity in the same catalog, and : is FUNC.OP_RANGE_REF.

Three properties follow from "smallest containing rectangle":

2. The operands are corners, not endpoints of a path. A1:B10 covers 20 cells, not the cells "between" A1 and B10 in reading order.

The contract records the admitted operand shapes as A1-style cells, areas, whole rows and whole columns that parse through the local A1 reference substrate. Whole-row and whole-column forms (1:1, A:A) are this same operator with row-only or column-only operands.

Arguments

Arity is exactly 2; no optional arguments, no defaults. Both operands must be reference-shaped: a number, text or logical operand is not a reference and has no bounding box with anything.

The subtlety readers miss is that the operands need not be literal addresses. Reference- returning expressions — INDEX in its reference form, OFFSET, INDIRECT, a defined name — can appear on either side, which is how idioms like A1:INDEX(A:A,n) build a growing range without a volatile function. That works only because : receives references rather than values.

Try it

Quirks

Result and edge cases

• Non-reference operands. No bounding box exists; the result is an error, not a coercion.
• Cross-sheet operands. Sheet1:Sheet3!A1 is the three-dimensional reference shape of chapter 01. Whether that is this operator applied to sheet names, or a separate grammar form, is not settled in this Handbook's sources, and the contract's admitted slice does not cover mixed-prefix composition.
• Result shape. Always a single rectangular area. : cannot produce a multi-area reference; that is FUNC.OP_UNION_REF.
• Deleted operands. If either corner is invalidated (its rows or columns deleted), the reference is no longer valid and #REF! is the documented error for that state (chapter 01).
• Coercion and lifting do not apply. This operator sits outside the scalar coercion story; there is nothing to convert and nothing to broadcast.

Errors

How to implement

Notes for implementers

• Normalize the corners. Store an area canonically (top-left, bottom-right) so that B2:A1 and A1:B2 are the same value, and so that downstream equality of references works.
• Keep the operands as references all the way in. An implementation that dereferences before applying : cannot support A1:INDEX(...) at all, and that idiom is common enough to be a correctness requirement rather than a nicety.
• Absolute/relative markers ($) and sheet qualification belong to the reference value, not to this operator. : composes whatever it is given; it must not normalize away anchoring.

Rust code

/// `left:right`.
pub(super) fn range(left: &CalcValue, right: &CalcValue) -> CalcValue {
    let operands = [left.clone(), right.clone()];
    let references = match references(&operands) {
        Ok(references) => references,
        Err(value) => return value,
    };
    let mut areas = references.iter().flat_map(|reference| reference.areas());
    let first = *areas.next().expect("a reference holds an area");
    let (mut top, mut left, mut bottom, mut right) = bounds(first);
    for area in areas {
        let (t, l, b, r) = bounds(*area);
        top = top.min(t);
        left = left.min(l);
        bottom = bottom.max(b);
        right = right.max(r);
    }
    CalcValue::reference(ReferenceLike::area(area(first, top, left, bottom, right)))
}

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

• FUNC.OP_UNION_REF — the comma; combines areas without merging them into a bounding box.
• FUNC.OP_INTERSECTION_REF — the space; the meet operation to this operator's join.
• FUNC.OP_SPILL_REF — a different way to name a region, by anchor rather than by corners.
• INDIRECT, OFFSET, INDEX (reference form) — the reference-producing functions that can supply either operand.
• ROW, COLUMN, ROWS, COLUMNS, AREAS, CELL — the reference-inspecting functions that consume the result without dereferencing it.
• Structured references (Table1[Amount]) and defined names denote areas without using this operator; they are separate reference shapes in chapter 01's table.

Named variants

No named variants are recorded yet.