What it does
=ROW(B7) returns 7; without an argument you get the row number of the formula's own cell. It looks trivial on its own, but combined with other functions it makes position-based calculations easy.
Typed numbers such as 1, 2, 3 break when a row is deleted or the table is sorted. =ROW()-1 recalculates on every row and gives 1 on the first data row when the header is on row 1. If the header sits lower, subtract its row: =ROW()-ROW($A$3).
Combined with MOD it picks every second or third row; combined with rounding up it splits rows into groups. To count the rows of a range use ROWS; to generate a run of numbers in Microsoft 365 use SEQUENCE.
Syntax
=ROW([reference])=SATIR([başvuru])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
reference | Optional | The cell whose row number you want, e.g. B7. If omitted, the cell that holds the formula is used. In Microsoft 365, a multi-row range spills the number of each row. |
Examples
1Number rows so the numbering never breaks
The header is on row 1, so 1 is subtracted from each row number. The formula in A2 is filled down.
=ROW()-1| A | B | C | |
|---|---|---|---|
| 1 | No. | Item | Qty |
| 2 | 1 | Notebook | 40 |
| 3 | 2 | Pen | 120 |
| 4 | 3 | Binder | 25 |
| 5 | 4 | Eraser | 60 |
| 6 | 5 | Stapler | 8 |
- A2 (filled down to A6)
=ROW()-1
The first data row is sheet row 2, so A2 shows 1 and the last row shows 5. Delete row 4 and the rows below renumber themselves.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Put every three boxes on one pallet
Three boxes fit on a pallet. ROW()-1 gives each box a position starting at 1; dividing it by 3 and rounding up with CEILING.MATH gives the pallet number.
=CEILING.MATH((ROW()-1)/3)| A | B | C | |
|---|---|---|---|
| 1 | Box | Weight (kg) | Pallet No |
| 2 | B-01 | 18.0 | 1 |
| 3 | B-02 | 22.0 | 1 |
| 4 | B-03 | 19.5 | 1 |
| 5 | B-04 | 21.0 | 2 |
| 6 | B-05 | 17.0 | 2 |
| 7 | B-06 | 20.5 | 2 |
| 8 | B-07 | 23.0 | 3 |
- C2 (filled down to C8)
=CEILING.MATH((ROW()-1)/3)
The first three boxes go on pallet 1 and the next three on pallet 2. B-07 gets pallet 3; as you add boxes, the grouping carries on by itself.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Off by one
Cause:
=ROW()counts the header row too, so the first data row shows 2 instead of 1.Fix: Subtract the header's row number:
=ROW()-1or=ROW()-ROW($A$1).- #SPILL!
Cause: In Microsoft 365,
=ROW(B2:B6)returns five results and one of the cells below is not empty.Fix: Pass a single cell, or clear the cells the result spills into.
Good to know
- To number only the visible rows of a filtered list, use the pattern
=SUBTOTAL(3,$B$2:B2); ROW counts hidden rows as well (SUBTOTAL). - In conditional formatting,
=MOD(ROW(),2)=0shades every second row.
In Nexell
Get Nexell to do it from a sentence
Add a No. column at the start of the table, and keep the numbers in order when rows are deleted
Convert to tableBring your table into the tool and type this. Nexell adds a No. column at the start and shows it in a preview first; nothing changes until you approve.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I keep row numbers correct after deleting rows in Excel?
With the header on row 1, put =ROW()-1 in the number column and fill it down. When you delete rows or sort the table, the numbers recalculate. In Microsoft 365, =SEQUENCE(COUNTA(B2:B100)) produces all the numbers with one formula.