Excel ROW Function Examples for Numbering Rows and More

Excel’s ROW function returns the worksheet row number for a reference, or the row containing the formula if you leave the reference out. For example, =ROW(A5) returns 5; a common practical use is =ROW()-ROW($A$2)+1 to create a running number starting at 1 in row 2.

What the ROW function does

ROW is a small function with several useful applications in worksheets. It reports a position on the worksheet, not a value stored in a cell and not the number of records in a list. That distinction matters: if a heading is in row 1 and the first record is in row 2, the first record’s worksheet row is 2, even if you want to label that record as item 1.

The function has two forms. With no argument, it returns the row number of the cell containing the formula. With a reference, it returns the row number of that reference. If the reference covers several rows, Excel can return an array of row numbers in current versions that support dynamic arrays.

Worksheet position: Identify where a cell or formula sits on the sheet.
Relative numbering: Convert worksheet positions into a sequence that starts at 1, or another chosen number.
Formula logic: Supply a changing row position to functions such as INDEX or create row-based formatting rules.

Basic ROW syntax and quick examples

The function name is followed by parentheses. The reference is optional, and the reference can be a cell or a range. A reference such as A5 is interpreted as a worksheet address, so the function returns the row position represented by that address.

With a formula in cell D7 and no reference, =ROW() returns 7.
With any formula location, =ROW(A5) returns 5 because A5 is on worksheet row 5.
With a formula in row 7, =ROW(A5) still returns 5. The explicit reference controls the result.

In Microsoft 365, a range reference spanning multiple rows can return a spilled array. With the formula entered in a clear area, =ROW(A2:A5) returns 2, 3, 4, and 5 in successive cells. Leave enough empty space below the formula for the results; otherwise Excel displays a spill error. In older Excel versions without dynamic arrays, this behavior may require entering the formula as an array formula, so use a single-cell reference when you only need one row number.

Example: create a running number for a list

A running index is one of the most common uses of ROW. Suppose a worksheet has column headings in row 1, item names in A2:A6, and you want sequence numbers in B2:B6. If you use the worksheet row directly, the first item would be numbered 2. Subtract the row containing the first item, then add 1 to make the first result equal 1.

With the first item on row 2 and the numbering formula entered in B2, use =ROW()-ROW($A$2)+1. The formula returns 1 in B2; fill it down and the results are 2, 3, 4, and 5 in B3:B6. The dollar signs keep A2 fixed as the starting point while the formula is copied.

This anchor-based pattern is more adaptable than simply subtracting 1 from ROW. If you insert a title row above the list, the formula still calculates relative to the first data row as long as the reference to that starting cell adjusts with the worksheet. Check the first result after reorganizing a sheet, especially if formulas or ranges have been moved in unusual ways.

If the sequence should begin at a different number, change the final addition. For example, starting at 100 means the first row returns 100, followed by 101 and 102 as the formula is filled down. The row difference supplies the increment; the final constant sets the starting value.

Example: generate row numbers with ROW references

You can also make a changing counter by using a relative reference that shifts when copied. With the formula in B2 and copied downward, =ROW(A1) returns 1 in B2, 2 in B3, and 3 in B4. The reference starts at A1, and when the formula is copied down Excel adjusts it to A2, A3, and so on.

This is concise, but it depends on the copy pattern. If you copy the formula sideways, or place it somewhere other than the expected starting point, it may not produce the intended sequence. The anchor-based formula from the previous section makes the connection to the first data row more explicit. Choose the version that will be easiest for someone maintaining the worksheet to understand.

Example: return the nth item with INDEX

ROW can supply a changing position to INDEX, which is useful for building a formula-driven list from a source range. Suppose product names are in A2:A6 and you want to reproduce them in D2:D6 in the same order. Enter the formula in D2 and fill it down.

With product names in A2:A6 and the first output formula in D2, use =INDEX($A$2:$A$6,ROW(A1)). In D2, ROW(A1) returns 1, so INDEX returns the first source name, from A2. When copied to D3, the relative reference becomes A2, ROW(A2) returns 2, and INDEX returns the second name. The formula therefore returns the five source names in order as it is filled through D6.

The dollar signs hold the source range steady while copying, but the ROW reference remains relative so its position advances. This example is useful when you need a simple sequential extraction. It does not filter out blank cells or reorder results; use an appropriate filtering or sorting approach when the task requires those behaviors.

Example: number only visible records

A normal ROW-based sequence reflects physical worksheet positions, so it may show gaps or nonconsecutive values when rows are filtered. For a visible-row count, SUBTOTAL can count nonblank identifiers while ignoring filtered-out rows. This works best when every real record has a value in the chosen identifier column.

With nonblank record IDs in A2:A100 and the formula entered in B2, use =SUBTOTAL(103,$A$2:A2). Fill it down the list. The first visible, nonblank record returns 1; subsequent visible records return 2, 3, and so on. The first range endpoint is fixed at A2, while the second expands as the formula is copied down.

The function number 103 tells SUBTOTAL to count nonblank cells while excluding manually hidden rows as well as rows excluded by a filter. If you want to ignore filtered-out rows but still count manually hidden rows, use function number 3 instead. Rows with blank IDs do not increase the count, which is why a consistently populated identifier column is important. After changing a filter, check the displayed results to confirm they match the list you intend to count.

Example: alternate row formatting

ROW can drive conditional formatting without adding a helper column. A common example is shading every other row to make wide tables easier to read. Select the range to format, such as A2:D100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Choose a fill format and create the rule.

With A2:D100 selected and the first data row on row 2, use =MOD(ROW(),2)=0 as the rule formula. It is TRUE on even worksheet rows, so rows 2, 4, 6, and so on receive the chosen format. To shade odd worksheet rows instead, change the comparison to 1. Because the rule uses the actual worksheet row, moving the table can change which visible row is shaded first; that is expected behavior.

If you need the first data row shaded regardless of whether it is on an odd or even worksheet row, base the test on the offset from that first row instead. With A2:D100 selected and row 2 as the first record, use =MOD(ROW()-ROW($A$2),2)=0. This shades the first data row, then every second row after it. Conditional formatting formulas are evaluated relative to the top-left cell of the selected range, so select the intended range before creating the rule.

ROW versus ROWS: a frequent mix-up

ROW and ROWS sound alike but answer different questions. ROW reports a row position. ROWS counts how many rows are in a reference. If a list occupies rows 2 through 6, the worksheet row of its first cell is 2, while the range contains 5 rows.

With the list in A2:A6, =ROW(A2) returns 2, while =ROWS(A2:A6) returns 5. Use ROW when a formula needs a worksheet position or a row-based counter. Use ROWS when you need the height of a range, such as checking how many rows a source block includes. Neither function counts only nonblank entries; for that, choose a counting function suited to the data and the required treatment of blanks.

Common problems and ways to avoid them

Most ROW errors come from confusing a worksheet address with a list position, or from copying a formula in a different pattern than intended. Before filling a formula down a long range, check its first two or three results.

The first number is 2 instead of 1: ROW() reports the physical worksheet row. Subtract the starting row and add 1, or use a relative reference such as ROW(A1).
Every result is the same: A fixed reference such as $A$1 does not advance when copied. That is useful for a fixed anchor, but not for a changing counter.
The count does not match filtered records: ROW returns physical positions, not a count of visible entries. Use a cumulative SUBTOTAL formula with a reliably populated identifier column.
A dynamic-array formula shows a spill error: Clear cells where the returned array needs to appear. A nonempty cell in the spill area prevents Excel from displaying the complete result.
Numbers change after sorting: A formula based on ROW recalculates from the item’s new position. It is a positional index, not a permanent identifier for a record.

One practical decision is whether the numbers should track positions or identities. If the purpose is to display the current order of a list, a ROW-based formula is appropriate. If each record needs a stable ID that should remain attached to it after sorting, filtering, or moving rows, store a fixed identifier instead of calculating it from its current row.

When to use ROW and when to choose another method

ROW is a good choice when the desired answer depends on where a formula or reference sits on the worksheet. It is particularly useful for relative numbering, row-based conditional formatting, and supplying an ordinal position to another function. Because it is a basic worksheet function, these examples also work in many earlier desktop editions of Excel, not only Microsoft 365.

Choose a different method when the requirement is not about worksheet position. Use ROWS to count the height of a range, a counting function to count populated cells, or a fixed value for a permanent record number. For a simple sequential list in current Excel, SEQUENCE can also create a series directly, but ROW remains useful when the sequence must follow existing worksheet rows or be embedded in another formula.

Frequently Asked Questions

What does the Excel formula =ROW() return?
It returns the worksheet row number of the cell containing the formula. For example, if you enter it in D7, the result is 7.

How do I start row numbering at 1 in Excel?
If the first record is in row 2, enter =ROW()-ROW($A$2)+1 in the numbering column on that row and fill down. The first result is 1, and each subsequent row increases the number by 1.

What is the difference between ROW and ROWS?
ROW returns a row position, such as 2 for A2. ROWS counts the number of rows in a range, such as 5 for A2:A6.

Does ROW numbering change when I sort a list?
Yes. A formula based on ROW reflects the row’s current position, so its result can change when records move. Use a stored ID if a record needs a permanent number.

Can ROW number only visible rows after filtering?
ROW alone cannot; it reports physical positions. A cumulative SUBTOTAL formula such as =SUBTOTAL(103,$A$2:A2) can count visible nonblank IDs as you fill it down, provided the ID column is populated for every record.

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.