How do I get the row number of the last entry in my sales log?

Wrap ROW with the LOOKUP function that finds the last non-blank cell in a column, for example =LOOKUP(2,1/(A:A<>""),ROW(A:A)). This returns the row index of the deepest filled cell in column A.

Can ROW return multiple row numbers in a single cell?

In modern Excel and Google Sheets, ROW applied to a multi-cell range returns a spill array that occupies a vertical range of cells. It will not concatenate the numbers into one cell; instead, the results flow into adjacent rows unless you explicitly aggregate them with TEXTJOIN or similar.

Why does ROW sometimes give me a #SPILL! error even though I thought the result was a single number?

If your formula references a range (even a single-column range) ROW produces an array. If that array cannot expand because neighboring cells are filled, the engine raises #SPILL!. Use a single-cell reference (e.g., ROW(A5)) or clear the blocking cells.

Is there a way to offset the row number by a dynamic amount?

Yes. Combine ROW with arithmetic or with the OFFSET function. For example, =ROW()+OFFSET(A1,0,0) adds the value found in A1 to the current row number, letting you shift the result up or down based on a cell’s content.

ROW function

ROW returns the row number of a given cell or range, or the row of the formula itself when no reference is supplied.

=ROW([reference])

Generate a ROW formula

Describe what you need. The generator will reach for ROW where ROW is the right tool, and tell you when it is not.

Arguments

How ROW reads its arguments

Argument	Required	Description
reference	Optional	Optional. A cell or range reference; if omitted the function uses the cell containing the formula. Supplying a non-reference (e.g., a text string) triggers a #VALUE! error.

Returns

A single integer or an array of integers representing row numbers.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Identify the physical row of a specific order

Order ID	Region	Rep	Units	Unit Price	Order Date
101	North	Alice	5	12.99	2023-01-10
102	South	Bob	3	9.99	2023-01-12
103	East	Carol	8	15.49	2023-01-15

=ROW(A4)

Result: 4

The formula points at cell A4, which holds the Order ID 103. Because the worksheet’s first row is the header, A4 resides in the fourth physical row. ROW simply returns that numeric index, 4. This is useful when you need to reference the row for a lookup or when building dynamic ranges. The result is a single scalar, not an array.

2. Generate a list of row numbers for all West-region sales

Order ID	Region	Rep	Units	Unit Price	Order Date
101	West	Alice	5	12.99	2023-01-10
102	West	Bob	3	9.99	2023-01-12
103	East	Carol	8	15.49	2023-01-15
104	West	Dave	2	11	2023-01-18
105	South	Eve	7	13.75	2023-01-20

=ROW(B2:B6)

Result: {2;3;4;5;6}

The range B2:B6 covers the Region column for the first five data rows. ROW applied to a multi-cell range returns an array where each element is the row number of the corresponding cell in the range. Because the range starts at row 2 and ends at row 6, the resulting vertical array is {2;3;4;5;6}. This array can be fed directly into functions like FILTER or INDEX to build more complex calculations.

3. Create a zero-based index column next to the sales log

Order ID	Region	Rep	Units	Unit Price	Order Date
101	North	Alice	5	12.99	2023-01-10
102	South	Bob	3	9.99	2023-01-12
103	East	Carol	8	15.49	2023-01-15

=ROW() - 1

Result: 1 (in row 2), 2 (in row 3), 3 (in row 4)

When ROW is called without an argument, it returns the row number of the cell that contains the formula. Placing the formula in column G, starting at G2, ROW() yields 2, 3, 4 for the three data rows. Subtracting 1 converts those to a zero-based index (1, 2, 3) which is handy for programming-style loops or when you need a sequential identifier that starts at zero rather than one.

Common errors

Which ROW error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The reference argument is supplied as a literal text string (e.g., =ROW("A2")) instead of an actual cell reference.	Replace the text with a proper reference: =ROW(A2).
#REF!	The reference points to a deleted or otherwise invalid range, such as =ROW(ZZ1000) on a sheet that only has 500 rows.	Adjust the reference to a valid cell or range that exists on the worksheet.
#SPILL!	ROW is asked to return an array (e.g., =ROW(A2:A10)) but the cells directly below the formula are already occupied, preventing the spill.	Clear the obstructing cells or move the formula to a location with enough empty space for the array to expand.

Tips and when to use something else

Use ROW() without an argument to quickly generate a running count when building helper columns.
When you need the column number instead of the row, switch to the COLUMN function.
If you require the address of a cell rather than its numeric row, combine ROW with ADDRESS, e.g., =ADDRESS(ROW(A4),COLUMN(A4)).
For extracting a specific row from a table based on a condition, consider using FILTER or XLOOKUP instead of manually building row arrays.

Related functions

COLUMNCOLUMN returns the column number of a given reference, or the column of the cell containing the formula when no reference is supplied.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.OFFSETOFFSET returns a reference to a range that is a specified number of rows and columns from a starting cell, optionally sized by height and width.INDIRECTINDIRECT returns the value or reference specified by a text string, letting you build dynamic cell or range references on the fly.ADDRESSADDRESS returns a text string that represents a cell reference based on supplied row, column and optional absolute/relative settings.

Frequently asked questions

How do I get the row number of the last entry in my sales log?: Wrap ROW with the LOOKUP function that finds the last non-blank cell in a column, for example =LOOKUP(2,1/(A:A<>""),ROW(A:A)). This returns the row index of the deepest filled cell in column A.
Can ROW return multiple row numbers in a single cell?: In modern Excel and Google Sheets, ROW applied to a multi-cell range returns a spill array that occupies a vertical range of cells. It will not concatenate the numbers into one cell; instead, the results flow into adjacent rows unless you explicitly aggregate them with TEXTJOIN or similar.
Why does ROW sometimes give me a #SPILL! error even though I thought the result was a single number?: If your formula references a range (even a single-column range) ROW produces an array. If that array cannot expand because neighboring cells are filled, the engine raises #SPILL!. Use a single-cell reference (e.g., ROW(A5)) or clear the blocking cells.
Is there a way to offset the row number by a dynamic amount?: Yes. Combine ROW with arithmetic or with the OFFSET function. For example, =ROW()+OFFSET(A1,0,0) adds the value found in A1 to the current row number, letting you shift the result up or down based on a cell’s content.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17