TL;DR —
ROW([reference])returns the row number of a cell, andCOLUMN([reference])returns its column number — a position, not the value stored there.ROW(A10)is10;COLUMN(C1)is3. Leave the argument out and each returns the coordinate of the cell that holds the formula:=ROW()in cell A5 is5. That one idea — these functions read an address, not a value — powers everything useful they do: serial numbers that renumber themselves when you insert a row, zebra striping withMOD(ROW(),2), and the position feeds insideINDEXand array formulas.
=ROW() ' -> 5 the row this formula sits in (here, row 5)
=ROW(A10) ' -> 10 the row number of A10 (not its contents)
=COLUMN() ' -> 3 the column this formula sits in (col C)
=COLUMN(D1) ' -> 4 D is the 4th column
=ROW()-ROW($A$2)+1 ' -> 1,2,3 self-healing serial number, anchored at A2
=MOD(ROW(),2)=0 ' -> TRUE/FALSE every other row, for striping
ROW and COLUMN are the two functions people use without ever quite deciding to learn
them — you copy a serial-number formula off the internet, it works, you move on. But they
answer a question no other function does: where is this cell, and how big is this
range? Separate "the position of a cell" from "the value in a cell" in your head, and a
whole category of dynamic, self-adjusting formulas opens up.
What you'll learn
- The mental model: ROW and COLUMN return coordinates, not cell contents
- Why bare
ROW()returns the formula's own row — and why that's the useful part - The number-one confusion:
ROW(C10)is10, not the value in C10 - Self-healing serial numbers with
ROW()-ROW(anchor)+1 - Striping and every-n-th-row logic with
MOD(ROW(), n) - COLUMN's letter-vs-number trap, and ROW as the hidden engine inside
INDEX
The mental model: coordinates, not contents
Almost every Excel function reads what is inside cells — SUM adds their values,
VLOOKUP finds a value, TEXT reformats one. ROW and COLUMN are the odd pair that
ignore the contents entirely and report the address: which row, which column. Think
of them as a cell asking "where am I on the grid?" rather than "what do I hold?"
Both take an optional reference. ROW(A10) returns 10 because A10 is on row 10;
COLUMN(C1) returns 3 because C is the third column. Omit the argument and the
function reports its own location — =ROW() typed into A5 returns 5, and it will
return 6 the moment you drag it down a cell. That self-reference is not a quirk to work
around; it is the whole point, and it is what makes ROW useful as a counter.
One reassurance up front: unlike OFFSET and INDIRECT, ROW and COLUMN are not volatile. They don't recalculate on every keystroke, so you can sprinkle thousands of them across a model without the slowdown those two reference functions bring.
Bare ROW() returns the formula's own row
Type =ROW() into any cell and you get the number of the row it lives in. Drag it down
and the results count up — 2, 3, 4, … — because each copy reports its own new row.
This is the mechanism behind almost every "number my rows automatically" recipe.
=ROW() ' in A2 -> 2, in A3 -> 3, in A4 -> 4 (each copy reports its own row)
=COLUMN() ' in B1 -> 2, in C1 -> 3, in D1 -> 4 (COLUMN counts across)
The catch you'll hit immediately: if your data starts on row 2 (because row 1 is a
header), =ROW() gives you 2, 3, 4 — off by one. That is not a bug in ROW; it's ROW
telling you the literal truth about where it is. The fix is to anchor it, which is the
next section.
The #1 confusion: ROW(reference) is a position, not a value
Here is the mistake that sends people to search engines: they write =ROW(C10) expecting
the contents of C10 and get 10 instead.
=ROW(C10) ' -> 10 the ROW NUMBER of C10, never what's stored in C10
ROW was asked "what row is C10 on?" and answered honestly: row 10. It never looks
inside the cell. If you want the value at a computed position, that is
INDEX's job — INDEX takes a row number (often one that ROW or
MATCH produced) and returns the contents. Keep the division
sharp: ROW/COLUMN find the coordinate; INDEX turns a coordinate into a value. Nearly
every "ROW doesn't return what I expected" question dissolves the moment you hold that
line.
Self-healing serial numbers: ROW() minus an anchor
The single most valuable use of ROW is a serial number that survives inserted and
deleted rows. Hard-typed 1, 2, 3 breaks the instant someone inserts a row in the
middle; a ROW-based number simply renumbers itself.
=ROW()-ROW($A$2)+1 ' in the row where data starts (A2), this is 1; A3 -> 2; A4 -> 3
=ROW()-1 ' shorthand when your header is exactly row 1
Read it through the mental model: ROW() is this row, ROW($A$2) is the fixed row
where your list begins, and subtracting gives the offset from the start; +1 makes it
1-based. Because the anchor is an absolute reference ($A$2), it stays put when you copy
down, and because ROW re-reads the live position, inserting a row anywhere in the list
re-numbers everything below it automatically. That is the failure mode it exists to
kill: manual serials that quietly go 1, 2, 3, 3, 4 after an insert and no one notices
until an audit.
Striping and grouping: MOD(ROW(), n)
Feed ROW into MOD and it becomes a rhythm generator — a way to do something every n-th row. The classic is zebra striping in conditional formatting:
=MOD(ROW(),2)=0 ' TRUE on even rows -> shade every other row
=MOD(ROW()-2,3)=0 ' TRUE on every 3rd row, counting from the data start (row 2)
=INT((ROW()-2)/3)+1 ' -> 1,1,1,2,2,2,3,3,3 group number, 3 rows per group
MOD(ROW(),2) cycles 1,0,1,0…; testing it =0 gives you every other row for banding.
The failure mode here is the same off-by-one as serial numbers: if your table doesn't
start on the row you assumed, subtract an anchor (ROW()-2) so the pattern begins where
your data does, not where the worksheet does. Get the anchor right and one formula stripes
or groups a table of any length.
COLUMN: the sideways twin, and the letter-vs-number trap
Everything above holds for COLUMN, rotated 90°. COLUMN() reports the formula's own
column; COLUMN(D1) is 4. Its headline use is a serial that counts across as you
drag right — for building horizontal sequences or feeding an incrementing index:
=COLUMN()-COLUMN($B$1)+1 ' dragged right from B: 1, 2, 3, 4 ...
The trap that catches everyone: COLUMN returns a number, not a letter. Column C is
3, not "C". If you actually need the letter (to build a text address, say), COLUMN
alone won't do it — reach for ADDRESS, which can hand back
$C$1, and strip the parts you want. Confusing the column number with the column
letter is the source of most COLUMN frustration; they are different data types for
different jobs.
ROW as an array engine for INDEX and SMALL(IF())
Give ROW a range instead of a single cell and it returns an array of row numbers,
one per row — ROW(A1:A5) is {1;2;3;4;5}. That spilled array is the quiet engine inside
a lot of advanced formulas.
=INDEX($D$2:$D$100, ROW()-1) ' pull the Nth list item, row by row
=SMALL(IF(status="Open", ROW(status)), k) ' the row number of the k-th "Open" record
=SEQUENCE(ROWS(data)) ' the modern spill-friendly equivalent
The SMALL(IF(condition, ROW(range)), k) idiom — collect the row numbers where a
condition is true, then pick the k-th smallest — was for years the way to "return every
match, not just the first" before dynamic arrays. On Excel 365 you'd usually reach for
FILTER or SEQUENCE
instead, but you'll still meet the ROW-array pattern in inherited workbooks, and knowing
that ROW(range) is generating a list of positions is what makes those formulas
readable rather than magic.
The judgment call
The syntax is trivial — ROW for a row number, COLUMN for a column number, drop the
argument to mean "here." The judgment is knowing you're in a position problem, not a
value problem. The tell: whenever you're about to hard-type a sequence (1, 2, 3…),
a "shade every other row" rule, or an incrementing index that a coworker will inevitably
break by inserting a row — that's a ROW/COLUMN job, and anchoring it to an absolute
reference makes it self-heal. And the moment you catch yourself expecting ROW(C10) to
give you the contents of C10, stop: you want INDEX to turn that position into a value.
Hold the position-versus-value line and these two small functions quietly make your
sheets robust.
How ExcelMaster helps
Anchoring is exactly the fiddly bit that ROW formulas get wrong. Ask
ExcelMaster for "a row number column that stays correct when I insert rows" and it
writes =ROW()-ROW($A$2)+1 anchored to your actual header row, not a copy-pasted one
that's off by the wrong amount. Ask it to "shade every third row" and it hands back the
right MOD(ROW()-anchor, 3) rule for a conditional-formatting range that starts where
your data does. And when you describe a value problem in position language — "get the
value 5 rows down" — it recognizes that's an INDEX job and doesn't hand you a ROW that
returns a number you didn't want.
Frequently asked questions
What does the ROW function do in Excel?
ROW([reference]) returns the row number of a cell. =ROW(A10) returns 10. If you omit
the reference, it returns the row number of the cell containing the formula, so =ROW()
in cell B7 returns 7. It reports a position on the grid — it never returns the value
stored in the cell.
How do I get the row number of the current cell?
Type =ROW() with no argument into the cell; it returns that cell's own row number.
Likewise =COLUMN() returns its own column number. To number a list starting at 1
regardless of where it begins, subtract an anchor: =ROW()-ROW($A$2)+1 returns 1 in the
row containing A2, 2 in the next row, and so on.
Why does ROW return a number instead of the cell's value?
Because that's its job — ROW reports where a cell is, not what's in it. =ROW(C10)
returns 10 (C10 is on row 10). To fetch the value at a calculated position, pass the row
number to INDEX, e.g. =INDEX(D:D, ROW(C10)) returns the contents of D10.
What's the difference between ROW and ROWS?
ROW (singular) returns a position — the row number of a cell. ROWS (plural) returns
a count — how many rows are in a range: ROWS(A1:A10) is 10. Singular is a coordinate;
plural is a size. See Excel ROWS & COLUMNS.
How does COLUMN return a letter instead of a number?
It doesn't — COLUMN always returns a number (column C is 3, not "C"). To get the
column letter, use ADDRESS, e.g. =SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "")
extracts the letter part. See Excel ADDRESS.
Tested in
Tested in: Excel 365 (Windows 11) — last verified 2026-07-13.
Related guides: Excel ROWS & COLUMNS · Excel HYPERLINK · INDEX & MATCH · Excel ADDRESS · Excel OFFSET