Lookup function

Excel ROW function

Q: How do I automatically number rows in Excel?

If your data starts in row 2, type =ROW()-1 in A2 and fill down. In Excel 365, =SEQUENCE(COUNTA(B2:B100)) spills a numbered list that grows as you add rows.

Q: How do I sum every other row in Excel?

Use =SUMPRODUCT(--(MOD(ROW(C2:C9),2)=0),C2:C9) to add the even rows. Change =0 to =1 for the odd rows.

ROW returns the row number of a cell. With nothing in the brackets, =ROW() gives the row the formula sits in, and ROW(C5) gives 5. Give it a range and Excel 365 returns a list of row numbers, which is how formulas number a list or pick out every other row.

=ROW([reference])

Arguments

reference: Optional. A cell or range. Leave it out to get the row of the formula’s own cell. It must be a reference, not a number or text.

ROW examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C
1	Month	Line	Amount
2	Jan	Budget	1200
3	Jan	Actual	1135
4	Feb	Budget	1200
5	Feb	Actual	1310
6	Mar	Budget	1250
7	Mar	Actual	1198
8	Apr	Budget	1250
9	Apr	Actual	1222

Download this sheet as an Excel practice file (.xlsx)

=ROW(C5)
Result: 5
Returns 5, the row number of C5. What the cell holds does not matter.
=ROW(A2:A9)
Result: spills 8 rows × 1 column
2
3
4
5
6
7
8
9
A range gives one row number per row. In Excel 365 the numbers 2 to 9 spill down the column; older versions show only the first, 2.
=SUMPRODUCT(--(MOD(ROW(C2:C9),2)=1),C2:C9)
Result: 4865
Adds every other row. MOD(ROW(),2)=1 is TRUE on the odd rows 3, 5, 7 and 9, which hold the Actual amounts: 4,865 in total.
=ROW(C9)-ROW(C2)+1
Result: 8
Counts the rows from C2 to C9, both included: 8. ROWS(C2:C9) gives the same answer in one step.

How to use ROW

Click the cell for the result.
Type =ROW() for the row the formula is in, or =ROW(C5) for another cell’s row.
Press Enter.
To number a list that starts in row 2, type =ROW()-1 in the first row and fill down.

Common ROW mistakes

Using ROW numbers as fixed IDs

=ROW()-1 renumbers itself when you sort, insert or delete rows, so a record can get a different number tomorrow. For an ID that never changes, copy the numbers and paste them back as values.

Forgetting the header offset

ROW returns the sheet row, not the position in your list. A list that starts in row 2 needs =ROW()-1, or =ROW()-ROW($A$1), which keeps working if you insert rows above the table.

Mixing up ROW and ROWS

ROW(C2:C9) gives the row numbers 2 to 9. ROWS(C2:C9) counts the rows and returns 8.

ROW FAQ

How do I automatically number rows in Excel?

If your data starts in row 2, type =ROW()-1 in A2 and fill down. In Excel 365, =SEQUENCE(COUNTA(B2:B100)) spills a numbered list that grows as you add rows.

How do I sum every other row in Excel?

Use =SUMPRODUCT(--(MOD(ROW(C2:C9),2)=0),C2:C9) to add the even rows. Change =0 to =1 for the odd rows.

How do I highlight every other row in Excel?

Select the range, choose Home > Conditional Formatting > New Rule > Use a formula, and enter =MOD(ROW(),2)=0. Formatting the range as a table with banded rows does the same.

How do I get the row number of a match?

MATCH gives the position inside the range, so add the row the range starts on: =MATCH("Mar",A2:A9,0)+ROW(A2)-1 returns 6, the sheet row of the first Mar.

Related functions

Updated November 3, 2026