Excel ROW Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 74 of 105 · Full Excel course

The Excel ROW function returns the row number of a reference, or the row number of the cell containing the formula when no reference is given. ROW in Excel is a building block: it creates serial numbers that survive sorting and deletion, alternates shading, feeds INDEX and OFFSET, and generates the number sequences that array formulas depend on.

Table of Contents

ROW syntax

=ROW([reference])

Arguments

Argument	Required	Meaning
reference	Optional	The cell or range whose row number you want. If omitted, ROW uses the cell that holds the formula. For a multi-row range it returns the first row number, or an array of numbers in Excel 365.

Step-by-step example

=ROW(D10)

Excel reads the reference D10.
The cell is on row 10 of the worksheet.
The formula returns 10, wherever it is entered.

Entered in B5 with no argument, =ROW() returns 5. Fill it down and each cell reports its own row.

Practical use cases

1. Serial numbers that fix themselves

=ROW()-ROW($A$1)

Placed in A2 this returns 1, then 2, 3 … Delete a row and the numbering closes up automatically. Subtracting ROW($A$1) rather than a hard-coded 1 keeps it correct if the header moves.

2. Alternate row shading with conditional formatting

=MOD(ROW(), 2)=0

Use this as the formatting rule to colour every even row.

3. Sum every third row

=SUMPRODUCT((MOD(ROW(B2:B100)-ROW(B2), 3)=0)*B2:B100)

4. Return the row of a matched value

=MATCH(F2, A:A, 0)

MATCH gives the position; when the range starts at row 1 it is also the sheet row. For a range starting lower, add ROW(first_cell)-1.

5. Build an array of numbers (Excel 365)

=ROW(1:10)

Spills 1 to 10. In Excel 365 SEQUENCE(10) is cleaner, but ROW(1:n) works in every version inside INDEX, SUMPRODUCT or legacy array formulas.

6. Extract the nth word or character position

=MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)

Splits text into individual characters for further processing.

Common mistakes and errors

#REF! – the referenced row was deleted. Point the formula at a surviving cell.
Serial numbers that change when sorted – ROW() reflects position, so sorting reorders the data but the numbers stay 1, 2, 3 in place. That is usually what you want; if you need permanent IDs, paste the numbers as values.
Hard-coded offsets – =ROW()-1 breaks when you insert a row above the header. Use ROW()-ROW($A$1) instead.
Multi-cell reference in old Excel – ROW(A1:A5) in a single cell returns only 1 unless used inside an array-aware function.
Expecting cell content – ROW returns the number, not the value. Wrap in INDEX to fetch the value.

Tips and best practices

Anchor serial numbers to the header with ROW()-ROW($A$1) so inserting rows above does not shift the sequence.
Use ROW inside INDEX to pull every nth item: INDEX($B:$B, ROW()*3).
Prefer SEQUENCE in Excel 365 for number arrays; ROW(1:n) remains the compatible fallback.
Combine with MOD for banding in conditional formatting; it is lighter than a helper column.
Convert to values (Copy, Paste Special > Values) once serial numbers must become permanent IDs.