ROW in Google Sheets returns a cell’s row number. Use =ROW() for the formula’s own row or =ROW(A5) for a specific reference. You can also use ROW to number records automatically.
Return the Formula Cell’s Row Number
Enter this dataset starting in A1. Leave cells marked “(blank)” empty.
| Item |
|---|
| Pens |
| Paper |
| Folders |
| Tape |
Enter this formula in E2:
=ROW()The formula in E2 returns 2. It reports the sheet row, not the item’s position in the list. A formula in row 3 returns 3, regardless of where the dataset starts.
ROW Function Syntax
=ROW([cell_reference])The reference is optional. If supplied, it must identify a cell or range. Without ARRAYFORMULA, a range such as A2:A5 returns the first row number, 2.
Do not confuse ROWS with ROW. =ROWS(A2:A5) counts four rows; =ROW(A2:A5) returns position 2. Row 1 is not necessarily a header; that depends on your layout.
Get the Row of a Reference or Text Address
=ROW(A5)This returns 5. The location of the formula does not matter because A5 is supplied explicitly. If your address is stored as text, first convert it with INDIRECT.
Enter this dataset starting in A12. Leave cells marked “(blank)” empty.
| Text address |
|---|
| E5 |
| B8 |
| C3 |
| D10 |
=ROW(INDIRECT(A13))The text E5 becomes a reference, so the result is 5. Filling down returns 8, 3, and 10 for the remaining addresses. An invalid address must be corrected before ROW can inspect it.
Number a List Starting at 1
=ROW(A2)-1Enter this alongside the first record in row 2, then fill down. The first four results are 1, 2, 3, and 4. Subtracting the header offset changes sheet positions into list numbers.
For a list starting in row 25, use =ROW(A25)-ROW($A$25)+1. The anchored starting reference keeps the first number at 1 when the formula is filled down.
New rows still need formulas. Existing ROW formulas respond to row changes, but inserting a blank row does not guarantee a copied formula appears in it. Fill the formula into that row if needed.
Mark Alternating Rows with MOD
=IF(MOD(ROW(A2),2)=0,"Even","Odd")Row 2 returns Even; row 3 returns Odd. MOD tests the remainder after division by 2. This labels alternating sheet rows and can supply the logic for striped formatting.
Changing the divisor to 3 creates a repeating three-row cycle. A remainder of zero identifies every third sheet row; it does not create consecutive three-row groups.
Spill a Numbered Sequence with ARRAYFORMULA
=ARRAYFORMULA(ROW(A2:A5)-ROW(A2)+1)Enter this in a clear column with four available cells. The result is 1, 2, 3, 4 vertically. Subtracting the starting row and adding 1 makes numbering independent of the header offset.
To leave a number blank when its corresponding source cell is blank, use:
=ARRAYFORMULA(IF(A2:A5="","",ROW(A2:A5)-ROW(A2)+1))This hides numbers for blank records but retains positional gaps. For a sequence defined by a count, start value, and step instead of cell positions, use SEQUENCE.
Other Google Sheets articles you may also like