Excel Row Number Formula Examples and Uses

In Excel, enter =ROW() to return the row number of the cell containing the formula; for example, in D7 it returns 7. To return the row number of a particular cell, use =ROW(A7), which also returns 7. These formulas are useful for identifying rows and building numbered lists, but the best choice depends on whether the numbering should follow worksheet positions, skip blanks, or respond to filters.

How the ROW formula works

ROW reports a worksheet row number. With no reference inside its parentheses, it returns the row where the formula is entered. With a cell or range reference, it returns the row number associated with that reference. The result is a number, so it can be used in calculations, conditions, or labels.

For example, assuming the formula is entered in C12, =ROW() returns 12. If the formula is in C12 but refers to A4, =ROW(A4) returns 4. The result depends on the referenced cell, not on where the formula itself is located.

If you give ROW a multirow range, Microsoft 365 can return an array of row numbers. Assuming the formula is entered in C2 and cells C2:C6 are clear for the results, =ROW(A2:A6) spills the values 2, 3, 4, 5, and 6 down those cells. A blocked spill area causes a #SPILL! error. In older Excel versions, array formulas may behave differently.

Basic row number formula examples

Use a direct row reference when you need to display a specific worksheet position. Assuming the formula is entered in B3, =ROW(A10) returns 10. The referenced column does not affect the result: A10, D10, and X10 are all on row 10.

For a numbered list that starts at 1 in row 2, put this formula in A2 and fill it down. Assuming the first list item is on worksheet row 2, =ROW()-1 returns 1 in A2, 2 in A3, and 3 in A4. This is quick when the first item’s worksheet row is fixed and the formula is always filled down from there.

A version based on a starting cell makes the offset more explicit. Assuming the first formula is in A2 and numbering should begin at 1 there, =ROW()-ROW($A$2)+1 returns 1 in A2 and 2 in A3. The dollar signs keep the starting reference fixed when you copy the formula. If the first record starts on a different row, use that cell as the anchor and adjust the placement accordingly.

These formulas return position-based numbers, not permanent record IDs. If you sort a table, the numbers recalculate according to the new row positions. That is helpful when numbers should reflect the current order, but unsuitable if a number must remain attached to the same record after sorting.

ROW versus ROWS

ROW returns the worksheet row number for a reference. ROWS counts how many rows a reference contains. They sound similar but answer different questions: ROW can tell you that a cell is on row 8, while ROWS can tell you that a selected range spans 9 rows.

Assuming a formula is entered anywhere and the range A2:A10 contains nine worksheet rows, =ROWS(A2:A10) returns 9. It does not return 10 just because the last cell is on row 10, and it does not change if the formula is moved elsewhere. Use ROWS when you need a range’s height, such as checking the size of a list or creating a count relative to a starting point.

A common confusion is using ROWS to get a cell’s row number. For a single-cell reference, ROWS returns 1 because the reference contains one row. If the goal is to identify the cell’s worksheet position, use ROW instead. If the goal is to count records, decide whether to count the range’s rows or only the populated entries; those are not always the same.

Numbering records in a list or table

For a simple list with a heading in row 1 and the first record in row 2, the ROW-based formulas are easy to fill down. Assuming the formula starts in A2, =ROW()-1 produces a sequence that matches the records’ current worksheet positions. If you later insert or delete rows, the displayed sequence follows the new positions rather than preserving a record’s original number.

Excel Tables add another consideration: calculated-column formulas generally fill down as rows are added, but the formula’s numbering logic still determines the result. A row-based formula can be convenient for a table displayed in a fixed location. If users sort or filter the table, however, worksheet row numbers may not make a visible sequence from 1 onward. Choose a method based on what the number is supposed to mean.

Before filling a formula down a long list, check the first few results and the final row. For a list starting in row 2, the first result should be 1, and each next record should increase by one. If the starting row changes, update the offset or anchor. This small check catches a frequent mistake: subtracting the wrong number and getting a sequence that begins at 0 or 2.

Numbering only rows with data

If a list contains blank rows, a simple ROW formula still returns a number on every row. To show a number only when a record cell is populated, add an IF test. Assuming record descriptions are in B2:B20, enter this in A2 and fill it down: =IF(B2<>"",ROW()-ROW($B$2)+1,""). It shows 1 beside a nonblank B2, leaves A blank when B is empty, and calculates later numbers from physical row positions.

That method can leave gaps in the displayed numbers. If B2 contains data, B3 is empty, and B4 contains data, the visible values are 1 and 3. This is appropriate when the number should represent the record’s position in the worksheet, even when an intervening row is blank.

To number populated records consecutively without gaps, use a running count of nonblank cells. Assuming record values are in B2:B20, enter this in A2 and fill down: =IF(B2="","",SUMPRODUCT(--($B$2:B2<>""))). For data in B2 and B4 with B3 empty, the results are 1, blank, and 2. This works for text or numeric entries, provided a populated cell is what defines a record.

Numbering visible rows after filtering

A normal ROW formula continues to show each record’s worksheet row number when a filter hides other records. For example, visible rows might be numbered 2, 5, and 8 because those are their original positions. If you want a consecutive count of visible records instead, use SUBTOTAL with a running range.

Assuming every record has a nonblank value in B2:B20, enter the following formula in A2 and fill down: =SUBTOTAL(103,$B$2:B2). With all rows visible, the results begin 1, 2, 3, and so on. When you filter the list, the visible records are counted consecutively because function number 103 counts nonempty visible cells and ignores manually hidden rows too.

The reference column matters: SUBTOTAL counts nonempty cells, so choose a field that is filled in for every record. If some records have a blank in column B, those rows will not contribute to the count and the visible numbering may not match the number of displayed records. Filtering changes which rows are included; it does not change the underlying row numbers reported by ROW.

In Excel for Microsoft 365 on Windows, filters can be applied from Data > Filter. The same general filter command is available on the Mac ribbon, though the layout can vary by window size and Excel release. The formula itself is the same on Windows and Mac.

Creating a sequence with SEQUENCE

If you want a series of numbers in one step rather than formulas copied down, Microsoft 365 includes the dynamic array function SEQUENCE. Assuming A1:A10 is clear and you want ten numbers starting at 1, enter =SEQUENCE(10) in A1. Excel spills 1 through 10 into the cells below. If the spill range contains other values, Excel returns a spill error until the obstructing cells are cleared.

SEQUENCE is useful for generated numbering, but it is not a direct replacement for ROW in every situation. It creates a chosen series; ROW reports a worksheet position. If the list grows or shrinks, a fixed SEQUENCE count may need to be changed. SEQUENCE is available in Microsoft 365 and Excel 2021 or later. In Excel for the web, dynamic arrays are also supported, although editing and display details can differ slightly.

Common mistakes and how to avoid them

Expecting a fixed ID: ROW reflects the current worksheet location. Sorting or inserting rows can change the displayed number. Use stored values or a separate ID-generation process if the identifier must stay attached to a record.
Using the wrong starting offset: A formula such as =ROW()-1 begins at 1 only when entered in row 2. Check the first formula cell and the intended first number before filling down.
Confusing blanks with zero: A formula that returns "" displays an empty string, not the number zero. Other formulas may treat that result differently from a truly empty cell, so test how downstream counts or checks behave.
Forgetting that filters hide rows, not renumber them: ROW continues to report worksheet positions. Use SUBTOTAL when the visible list needs a running count, and make sure its reference column is populated for every record.
Overwriting a dynamic array spill: A formula such as SEQUENCE needs the cells below it to be available. Clear the intended spill area or choose another starting location.

When formulas are copied, Excel normally adjusts relative references, while dollar signs lock the references you want to keep fixed. For example, in the running-count formula, $B$2 stays at the first record and B2 expands as the formula is filled down. Understanding that difference makes it easier to adapt examples safely.

Choosing the right numbering method

Use ROW() when you want the current cell’s worksheet position, and ROW(reference) when you want the position of a particular cell. Use ROWS(range) to count the rows in a range. For a simple numbered list, a ROW formula with an offset is usually enough; for consecutive numbering that ignores blank records, use a populated-cell count; for a filtered list, consider SUBTOTAL; and for a predetermined run of numbers, use SEQUENCE.

The key question is whether the number describes a worksheet location, a record’s place in the current list, or a lasting identifier. A formula can reliably calculate the first two when designed for the layout, but position-based formulas do not create permanent IDs. Decide what the number means before choosing the formula, then test sorting, blank rows, and filtering if those actions are part of the workflow.

Frequently Asked Questions

What is the formula for a row number in Excel?
Use =ROW() to return the row containing the formula. Use =ROW(A5) to return the row number of A5, which is 5.

How do I start row numbering at 1 in Excel?
If the first numbered item is in row 2, enter =ROW()-1 in its numbering cell and fill down. Adjust the offset if your first item is on another row.

How do I number rows without numbering blank cells?
Test the record column with IF. For example, assuming records begin in B2, =IF(B2<>"",ROW()-ROW($B$2)+1,"") leaves the number cell blank when B is blank. The numbering can have gaps if blank rows occur between records.

Can Excel automatically renumber rows after filtering?
ROW does not renumber visible records; it returns their worksheet positions. A running SUBTOTAL(103,...) formula can count visible nonblank records, as long as the chosen reference column contains an entry for every record.

What is the difference between ROW and ROWS?
ROW returns a worksheet row number, such as 7 for a reference in row 7. ROWS counts the number of rows in a reference, such as 9 for A2:A10.

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.