ROW function
ROW returns the row number of a cell or reference. It is handy for numbering lists or building offsets, and for a multi-row range it returns an array in dynamic-array versions.
Example
Read it as a sentence: give the row number of cell D5, which gives 5.
Excel shows 5
D
1
Qty
2
12
3
7
4
20
5
5
Items
Syntax
ROW([reference])
Arguments in [square brackets] are optional.
-
reference(optional) - Cell or range to inspect; omitted means the formula cell.
Returns: The row number of the reference.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=ROW() | 4161 | Own cell |
=ROW(Items!D5) | 5 | Single cell |
=ROW(Items!B2:B4) | 2 | Range spills |
=ROW(Data!3:3) | 3 | Whole row |
=ROW(Data!B:B) | #SPILL! | Whole column |
=ROW((Items!B1,Items!B3)) | #REF! | Multi-area |
The Items sheet these formulas read
B
D
1
Item
Qty
2
Boxer cartridge
12
3
Briar pipe
7
4
Candle
20
5
Cigar
5
Items
Watch out for
- For a multi-row range it returns an array in dynamic-array versions.
- ROW()-1 is a quick way to number rows under a header.
Where it works
- Excel
- ROW is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .