Lookup & Reference
ROW and ROWS: create deletion-proof line numbers and count table height
Distinguish position index ROW(reference) from range height ROWS(array) so inserted or blank rows never break numbering.
ROW gives grid position; ROWS gives block height
ROW([reference]) returns the 1-based worksheet row number of a reference (ROW(A2) = 2; omitting reference returns the formula cell's own row). Subtracting the locked header row—ROW(A2) - ROW($A$1)—produces 1, 2, 3... line numbers that re-sequence automatically when rows are deleted.
ROWS(array) counts how many rows a range spans regardless of whether those cells are populated or blank: ROWS(A2:B5) = 4.
| Task (A) | Owner (B) | Line Number ROW(A)-ROW($A$1) |
|---|---|---|
| Spec review | Ana | 1 |
| API schema | (blank) | 2 |
| QA signoff | Leo | 3 |
| Release note | Mia | 4 |
Compare ROWS vs COUNTA when rows have blanks
In B2:B5, B3 is blank: COUNTA(B2:B5) returns 3 owners assigned, whereas ROWS(B2:B5) returns all 4 task rows.
=ROWS(A2:B5)Relative line number from locked header
For row 4 (QA signoff), ROW(A4) - ROW($A$1) evaluates 4 - 1 = 3.
=ROW(A4) - ROW($A$1)Try the example
Use the original values or change them to test your understanding.