ROW Function in Excel — Complete Guide (2026) | Sheets & Cells
INFORMATION FUNCTION

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.

=ROW([reference])

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.

CategoryInformation
IntroducedExcel 97
Modern altSEQUENCE (365)

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.

The auto-numbering pattern: Put =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()-1

Result: 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) = 0

Result: 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

Result is off by one

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.

#REF!

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.

Array formula returning single value

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.

Numbers don't reflow when rows are inserted/deleted

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

ApproachBest forTrade-off
ROWRow-based patterns; auto-numbering; array formulas1-indexed; needs offset for lists starting mid-sheet
SEQUENCEGenerating number seriesExcel 365/2021+ only
Hard-coded numbersNever (breaks on row insert)Doesn't reflow
Table row numbersReports where you want visible positionNot a formula — visible only via formatting

Version compatibility

Excel 365✓ Full
Excel 2024✓ Full
Excel 2021✓ Full
Excel 2019✓ Full
Excel 2016✓ Full
Excel Online✓ Full
Excel Mac✓ Full
Google Sheets✓ Full

Download the practice workbook
Auto-numbering templates, banded-row formatting, and array-formula patterns using ROW.

📥 row-practice.xlsx (coming soon)

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 →