Skip to content

Excel function · Lookup & reference

Excel ROW function

ROW returns the row number of a cell, or of the cell that holds the formula. It is mostly used for running numbers that do not break when rows are deleted and for groups that repeat every N rows.

In Turkish Excel: SATIR

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

English Excel=ROW([reference])
Turkish Excel=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

ArgumentRequiredDescription
referenceOptionalThe 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.

A2fx=ROW()-1
Number rows so the numbering never breaks: example table
ABC
1No.ItemQty
21Notebook40
32Pen120
43Binder25
54Eraser60
65Stapler8
  • 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.

C2fx=CEILING.MATH((ROW()-1)/3)
Put every three boxes on one pallet: example table
ABC
1BoxWeight (kg)Pallet No
2B-0118.01
3B-0222.01
4B-0319.51
5B-0421.02
6B-0517.02
7B-0620.52
8B-0723.03
  • 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()-1 or =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)=0 shades every second row.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add a No. column at the start of the table, and keep the numbers in order when rows are deleted

Convert to table

Bring 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.

Related functions

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free