ROW

What it does

ROW([reference])

ROW reports a row coordinate, in one of two quite different ways depending on whether it is given an argument.

1. ROW() — no argument — returns the 1-based row index of the cell containing the formula. It reads nothing from the sheet's contents; it reads the evaluation context. This makes ROW() one of the small set of caller-aware functions, alongside COLUMN() and CELL with an omitted reference. Two identical formulas in two cells return different answers.
2. ROW(reference) — returns the 1-based row index of the reference. For a single cell, that is one number. For an area spanning several rows, it is a vertical array of the row indices from top to bottom: ROW(A5:A8) is {5;6;7;8}.

That second behaviour is the one worth internalizing, because it is the engine behind a large family of array idioms. ROW(INDIRECT("1:"&n)) is the classic way to manufacture the sequence 1..n in a pre-SEQUENCE Excel, and ROW(range) - ROW(top_of_range) + 1 is the classic way to get positions relative to a range's top. Both are consequences of ROW of an area being an array rather than a scalar.

Arguments

reference — optional. A reference whose row index is wanted.

Three points about this single argument:

• Omission is meaningful, not a default. Omitting the argument does not substitute a value; it switches ROW into caller-aware mode. This is the Missing-versus-Empty distinction of the value universe doing real work: an omitted argument and an argument that resolves to a blank cell are not the same call.
• A multi-row area gives an array, not the top row. Readers expecting a scalar and getting a spill (or, historically, an implicit intersection) are meeting this rule.

Try it

Quirks

Result and edge cases

The return kind is a number for the no-argument and single-cell forms, and a vertical array of numbers for a multi-row area.

• Multi-area references (ROW((A1:A2,C1:C2))) are not described by the documentation and the Handbook has not verified their behaviour.
• What ROW() means outside a cell — in a defined name, in a conditional-format rule, in an add-in call — depends on the evaluation context the host supplies, and is host behaviour rather than function semantics.

Errors

• an argument that is not a reference, refused at entry rather than returning an error value;
• #REF! propagating from a reference that has been invalidated (deleted rows), which is reference resolution failing before ROW runs;
• #SPILL! at publication for an array result that cannot be placed — a host outcome, not a ROW outcome.

How to implement

Notes for implementers

• The two forms are different functions sharing a name. The no-argument form needs the caller's address from the evaluation context; the one-argument form needs no context at all. An engine that supplies caller context only when an argument is absent has to keep that seam explicit.
• The area form returns an array whose length is the reference's row span — including for whole-column references. Materializing that eagerly is a performance decision with visible consequences; refusing to materialize it changes the semantics.
• ROW must not dereference. Its answer is a property of the address, and a cell's contents — including whether it is blank — is irrelevant.
• The result of the area form is a column array. Getting the orientation wrong is invisible in a 1×1 case and wrong everywhere else.
• Caller-aware functions interact with anything that evaluates a formula away from a cell: defined names, array contexts, and add-in entry points each need a decided answer.

Rust code

eager(
    ROW,
    "ROW",
    CallableArityShape::range(0, 1),
    &[Parameter::value_or_reference("reference")],
    row,
)

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

• COLUMN is the exact transpose: same two forms, same caller-aware behaviour, horizontal array instead of vertical.
• ROWS answers the adjacent question — how many rows, rather than which — and always returns a scalar. Confusing the two is the single most common mistake here: ROW(A5:A8) is {5;6;7;8}; ROWS(A5:A8) is 4.
• SEQUENCE replaces the ROW(INDIRECT(...)) idiom for generating 1..n, without volatility and without abusing a coordinate function as a generator.
• CELL("row", ref) is another route to the same number, through a different and much broader function.
• OFFSET, INDEX, INDIRECT, ADDRESS are the rest of the reference family; ROW is a reference reader.

Named variants

No named variants are recorded yet.