Excel Formula for Row Number: ROW and ROWS Explained

To return the worksheet row number of the cell containing a formula, use =ROW(). To return the row number of a specific cell, use =ROW(A5); it returns 5 wherever you enter that formula.

Those two formulas answer slightly different questions: one reports where the formula is, and the other reports where a reference points. If you want a tidy sequence such as 1, 2, 3 beside a list, a row-counting formula may be a better fit. Choosing the right one depends on whether you need actual worksheet coordinates, a running count, or numbering that responds to filters.

What the ROW function returns

ROW returns the row number of a reference. Excel numbers worksheet rows from 1 downward, so a cell in the first row has row number 1, a cell in the second has row number 2, and so on. In current Excel worksheets, the last available row is 1,048,576.

With the formula entered in cell D7, =ROW() returns 7. The parentheses are required even though the reference is omitted. That omission tells Excel to use the cell containing the formula. This makes the function handy when you need to identify a formula’s current position without manually typing a row number.

With a reference included, the result describes that reference instead. If a formula in D7 refers to A5, =ROW(A5) returns 5, not 7. The formula can be entered in any cell and still returns the referenced row. If you refer to a range that spans several rows, ROW can return multiple row numbers as an array in modern Excel; older versions may handle array results differently.

ROW versus ROWS

The names are similar, but the functions answer different questions. ROW returns a row position. ROWS counts how many rows are in a reference. For example, if a selection covers A2 through A6, it contains five rows, so ROWS returns 5. It does not tell you that the selection begins on worksheet row 2.

With a list occupying A2:A6, enter =ROWS(A2:A6) in an empty cell to count its five rows. The result is 5, including any blank cells within that range. This distinction matters when building numbering: ROW is based on a worksheet address, whereas ROWS can count from a chosen starting point.

A quick way to remember the difference is to ask whether you need a coordinate or a quantity. Use ROW for a coordinate such as row 12. Use ROWS for a quantity such as 11 rows in a selected range. Neither function checks whether a row contains useful data; both operate on the reference you give them.

Create sequential numbers beside a list

If your data has a header in row 1 and starts in row 2, a direct row-based formula can show 1 beside the first record, 2 beside the second, and so on. With the first record on row 2 and the numbering formula in A2, use =ROW()-ROW($A$1). It returns 1 in row 2 because the formula’s row number is 2 and the reference to A1 has row number 1.

Copy the formula down using the fill handle, the small square at the lower-right corner of the selected cell. The absolute reference to A1 stays fixed, while ROW() changes as the formula is copied. If the data starts in row 5, with its heading in row 4 and numbering in the first data row, use =ROW()-ROW($A$4); the first result is 1.

This method is useful when numbering should reflect the worksheet’s position relative to a fixed heading. It also makes the first number easy to adapt: a different starting row or heading simply calls for a different reference. But if you insert a row above the list, the numbering follows the worksheet layout and may no longer begin where you expect. Check the anchor reference after major layout changes.

For a sequence based on how many rows have been filled from the beginning of a list, ROWS is often more intuitive. With the first item in A2 and the first number in B2, enter =ROWS($A$2:A2). It returns 1. Fill it down and the ending reference expands to A3, A4, and so on, producing 2, 3, and subsequent counts.

This approach counts the range from the fixed starting cell to the current row. It is especially clear when the list is a simple contiguous block. It does not test whether the corresponding item cell is populated, however: if the formula is copied into a row with no item, it still produces the next count. For a list with gaps, choose whether gaps should receive numbers before selecting a formula.

Number rows in an Excel table

Excel tables add and extend rows more conveniently than ordinary ranges, but =ROW() still returns the worksheet row, not the record’s position within the table. If the table begins on row 6, for example, its first data row may return 7 even if you would prefer a table number of 1.

Suppose the table is named Table1, has a header row, and you enter a numbering formula in one of its data rows. Use =ROW()-ROW(Table1[#Headers]) to subtract the header’s worksheet row from the current row. The first data row returns 1, and the next returns 2. Excel tables usually propagate a formula entered in a calculated column to the other rows in that column.

To check or change a table’s name, select a cell in the table and use the Table Design tab’s Table Name box in Excel for Microsoft 365 on Windows. The Mac ribbon also has a Table Design tab when a table is selected, though the ribbon layout can vary. If your table has another name, replace Table1 in the formula with that name. A table-relative sequence can be useful in reports, but consider whether the numbers should identify records permanently: if a table is sorted, a position-based number describes the new position, not an unchanging record ID.

Generate a sequence with SEQUENCE

When you want Excel to create a whole set of numbers at once, rather than copy a formula down, SEQUENCE can return a spilling array. It is available in Microsoft 365 and Excel 2021 or later. The cells where results will spill must be empty, or Excel will show a spill error.

With records occupying A2:A10 and an empty output area, enter =SEQUENCE(ROWS(A2:A10)). The formula spills the numbers 1 through 9 into nine cells. The count comes from the nine rows in the stated range. If the range’s size changes, adjust the reference so the sequence matches the rows you want to number.

SEQUENCE is useful for a generated index or a temporary list of positions, but it is not always a replacement for a formula in every record. The spilled results occupy one continuous output range, and they cannot spill into occupied cells. If you need each record to carry a number inside a table, a calculated-column formula is usually more suitable.

Number visible rows after filtering

A formula based on ROW reports the physical worksheet position. Filtering a list hides records but does not make the remaining records move into new worksheet rows, so a ROW-based sequence will usually keep gaps in its visible numbers. To number only visible records consecutively, use SUBTOTAL with a column that is filled for every record.

Assume each record has a nonblank ID in column A, the first record is in row 2, and the numbering formula is in B2. Enter =SUBTOTAL(3,$A$2:A2) and fill it down. The result counts nonblank IDs in the range so far while excluding rows removed by a filter. The first visible record returns 1; later visible records count upward. If some ID cells are blank, the numbers can repeat or fail to represent every record, so use a reliably populated column.

Function number 3 counts nonblank cells and ignores rows excluded by filtering, but it includes rows hidden manually. If you also want manually hidden rows ignored, use function number 103 instead. With the same ID and numbering layout, use =SUBTOTAL(103,$A$2:A2). Be aware that the formula’s behavior is tied to visibility and the referenced values, so this is best for a displayed sequence rather than a permanent record identifier.

Common problems and how to fix them

The answer is the formula cell’s row, not the data row. If you entered ROW() beside a record, the result is the location of the formula. To get a different row’s position, provide a reference such as A5.
The sequence starts at 2 instead of 1. A direct ROW() result is a worksheet coordinate, not a list index. Subtract the row number of the heading or use a ROWS formula anchored at the first data row.
Numbers have gaps after filtering. That is expected for physical row numbers. Use SUBTOTAL with a populated key column if visible records should be renumbered consecutively.
Copying a formula changes the wrong reference. In a ROWS formula, keep the starting point fixed with dollar signs, as in $A$2, while leaving the ending reference relative so it advances as you fill down.
A generated sequence shows a spill error. Clear any occupied cells in the output range, including cells that look blank but contain formulas or spaces, or place the formula somewhere with room to spill.

Sorting and inserting rows can also change what a number means. A worksheet row number identifies a current position, not the identity of a person, product, or transaction. If a number must remain attached to a record when users sort or rearrange data, store a fixed ID as data rather than relying on ROW to create a permanent identifier.

Which row-number formula should you choose?

Pick a formula based on what the number is supposed to represent. The right choice keeps the result meaningful when you copy formulas, add records, or filter the list.

Current worksheet position: use ROW() in the cell whose location you want.
Position of a particular reference: use ROW(reference).
Count of rows in a range: use ROWS(reference).
Running count from a chosen starting row: use ROWS with a fixed start and a changing end.
A consecutive count of filtered, visible records: use SUBTOTAL with a reliably populated column.
A spilled list of numbers in a supported Excel version: use SEQUENCE.

These functions are available in Excel for Microsoft 365 on Windows and Mac, and their basic behavior is the same in Excel for the web. Some interface details differ by platform, but no special ribbon command is required to enter a formula: select the cell, type it in the formula bar or cell, and press Enter.

Frequently Asked Questions

What formula gives the current row number in Excel?
With the formula entered in any worksheet cell, use =ROW(). It returns the row number of that cell, such as 7 when entered in row 7.

How do I make row numbers start at 1 below a header?
With a header in row 1 and the first data row and formula in row 2, use =ROW()-ROW($A$1). Fill it down to create 1, 2, 3, and so on.

What is the difference between ROW and ROWS in Excel?
With a reference such as A2:A6, ROW reports row positions, while ROWS counts the rows in the reference. The range has five rows, even though its first worksheet row is 2.

Can I number only the rows that remain visible after filtering?
With nonblank IDs in A2 downward and the numbering formula in B2, use =SUBTOTAL(3,$A$2:A2) and fill down. It counts visible nonblank IDs as the list is filtered; use function number 103 if manually hidden rows should also be excluded.

Does ROW give a permanent ID to each record?
No. ROW returns a worksheet position, which can change when rows are inserted, deleted, or rearranged. Use a stored, unique value if records need identifiers that remain stable.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.