ROW Function in Excel
ROW returns the row number of a cell reference. Sounds trivial, but it's the workhorse behind auto-numbering, array formulas, and dozens of clever patterns — especially when combined with SEQUENCE, MOD, and INDEX. Available in every Excel version.
What it does: Returns the row number of reference. If reference is omitted, returns the row number of the cell containing the formula. If reference is a range, returns an array of row numbers.
What ROW does
The basic use is trivial: =ROW(A5) returns 5. Where ROW earns its keep is in patterns that need row numbers as data — auto-numbered lists, running totals with position offsets, and array formulas that iterate over positions.
The optional argument makes ROW two-in-one: with no argument, it returns the row of the formula's own cell (useful for numbering lists). With a range argument, it returns an array of all the row numbers — the foundation of many pre-dynamic-array formulas.
=ROW()-1 in cell A2 and drag down. You get 1, 2, 3, 4, ... automatically — and if you insert or delete a row, the numbers reflow. Beats hard-coded numbers. In Excel 365, SEQUENCE(N) is often cleaner for fresh lists, but ROW is universal.
Syntax breakdown
reference Optional
The cell or range whose row number you want. If omitted, ROW returns the row number of the cell containing the formula. Can be a single cell, a range (returns an array of row numbers), or a named range.
5 real-world examples
Example 1: Auto-number a list
In cell A2 (assuming header in A1), put:
=ROW()-1Result: 1. Drag down through A100 to get 1, 2, 3, ... 99. Insert a row anywhere and the numbers reflow automatically — no manual renumbering.
Example 2: Get an array of row numbers
Generate the numbers 1 through 10 as an array:
=ROW(1:10)Result: An array of {1;2;3;4;5;6;7;8;9;10}. Foundation of many array formulas. In Excel 365, =SEQUENCE(10) does the same more clearly.
Example 3: Every other row (banded)
In conditional formatting, highlight every other row:
=MOD(ROW(), 2) = 0Result: TRUE for even-numbered rows, FALSE for odd. Apply as a conditional formatting formula to a range to get zebra striping. Every third row: =MOD(ROW(), 3) = 0.
Example 4: Find all matching row numbers
Get every row where column A contains "East":
=FILTER(ROW(A2:A100), A2:A100="East")Result: Spilled array of row numbers matching the criterion. Requires Excel 365 or 2021+. In older Excel, use SMALL + IF as an array formula.
Example 5: Reference the "current row" in a Table column
In a Table, use ROW to build formulas that refer to positions relative to the current row:
=INDEX(Sales[Amount], ROW()-ROW(Sales[#Headers]))Result: The Amount value for the current row of the Table. Handy when you need explicit position lookups inside a Table without relying on implicit intersection.
Common errors and how to fix them
If your data starts at row 2 (header in row 1) and you want position 1, 2, 3..., you need =ROW()-1. The offset depends on where your data starts. Adjust the subtraction to match your header configuration.
Very rare with ROW — occurs if the referenced cell is deleted. Usually caused by another formula's error propagating. Check the argument you passed to ROW.
In pre-2021 Excel, =ROW(1:10) in a single cell returns only the first value (1). To get the full array, enter as an array formula (Ctrl+Shift+Enter) or spill in Excel 365. Use SEQUENCE for the modern equivalent.
You may have hard-coded numbers instead of ROW-based ones. Replace with =ROW()-N where N is the offset from your desired starting number. Now inserts and deletes automatically renumber.
ROW vs alternatives
| Approach | Best for | Trade-off |
|---|---|---|
| ROW | Row-based patterns; auto-numbering; array formulas | 1-indexed; needs offset for lists starting mid-sheet |
| SEQUENCE | Generating number series | Excel 365/2021+ only |
| Hard-coded numbers | Never (breaks on row insert) | Doesn't reflow |
| Table row numbers | Reports where you want visible position | Not a formula — visible only via formatting |
Version compatibility
Download the practice workbook
Auto-numbering templates, banded-row formatting, and array-formula patterns using ROW.
Related functions
Frequently asked questions
What's the difference between ROW and ROWS?
ROW returns a row number (e.g., 5). ROWS counts how many rows are in a range (e.g., 10 rows in A2:A11). Similar names, opposite purposes. ROW is about position; ROWS is about size.
Should I use ROW or SEQUENCE?
SEQUENCE for generating fresh number series in Excel 365 / 2021+ — cleaner syntax. ROW when you need the actual row number of a cell (e.g., for auto-numbering that reflows on insertion), or when maintaining backward compatibility with older Excel.
Why doesn't my auto-numbering reflow?
Because the numbers are hard-coded (typed as literals), not computed. Replace with =ROW()-N where N is the offset needed to get your desired starting number. Inserts and deletes will now update automatically.
Can ROW give me the row number of a value found by MATCH?
Not directly — MATCH returns a position within a range, not a sheet row. Combine: =MATCH(value, A2:A100, 0) + ROW(A2) - 1. Or use ADDRESS + INDIRECT for the actual sheet reference.
Is ROW volatile?
No. ROW is non-volatile — recalculates only when its input changes or the row structure changes. Safe to use extensively.
How do I get the row number of the current cell inside a formula?
Just =ROW() with no arguments. It returns the row number of whatever cell contains the formula. Move the formula to a different row and the result changes automatically.
Can I use ROW in a Table to get the row within the Table?
ROW returns the sheet row number, not the position within a Table. For Table-relative position: =ROW() - ROW(TableName[#Headers]) gives you 1 for the first data row, 2 for the second, etc.
Build reflowing lists and array formulas — with the Sheets & Cells AI Add-in
The Add-in recognizes when you need ROW-based patterns for auto-numbering, banded formatting, or array iteration — and writes the formula with the right offset. All inside Excel.
Learn about the Add-in →