ROW Function

Summary

The ROW function returns the row number of a reference. If the reference is in row 10, the result is 10. If you leave the reference out, ROW returns the row number of the cell that contains the formula.

That simple behavior makes ROW useful in many worksheet patterns. It can generate running numbers, build relative offsets, create moving lookup indexes, and help formulas react to their position in the sheet.

ROW is useful when formulas need awareness of their vertical position. It often appears in numbering patterns, dynamic offsets, and logic that should behave differently depending on which row a value or formula occupies.

Syntax

=ROW([reference])

The reference is optional. If you write =ROW(B500), the result is 500. If you write =ROW() in cell D7, the result is 7 because the formula is sitting in row 7.

In current Excel versions, a range such as A1:A3 can return multiple row numbers as an array. In many everyday worksheets, though, ROW is used with a single cell or with no argument at all.

Arguments

• [reference] - Optional. The cell or range whose row number you want.

If you omit the reference, ROW uses the location of the formula itself. If you give it a reference, the function uses that reference instead. This is why ROW()-1 is a common numbering pattern: the formula takes its own row number and then shifts it by a fixed amount.

The reference does not have to point to a cell with data in it. ROW only cares about position, not cell contents. That means ROW(B500) still returns 500 even if B500 is empty.

ROW vs Other Functions

ROW is about position. Related functions answer slightly different questions.

Using ROW

One of the most common uses of ROW is automatic numbering. Instead of typing 1, 2, 3 by hand, a sheet can use ROW() or an adjusted version like ROW()-1. Because the number comes from the row position, it updates automatically when the sheet structure changes.

ROW is also useful inside other formulas. For example, it can provide a moving index to a lookup formula as the formula is copied downward. This is an older but still practical technique for building multi-row retrieval patterns without rewriting the index by hand.

Another useful pattern is row distance. When you subtract one ROW result from another, you get the gap between two positions. That can help with section-based logic, offset calculations, or checks that depend on how far a record is from a starting point.

• Use ROW for running numbers and row-based IDs.
• Use it inside other formulas when the row position should change automatically as the formula moves.
• Subtract two ROW results when you need a row offset.

Example 1 - Return the Current Row Number

This is the simplest version of the function. If the formula is in row 5, ROW returns 5. The result depends on where the formula is placed, which is why this pattern is useful for automatic numbering.

This is a helpful starting example because it shows the “current position” behavior clearly. The formula reads its own row and turns that location into a usable number.

=ROW() // Returns 5 if entered in row 5

Example 2 - Return the Row of a Specific Reference

This version ignores the formula's own location and returns the row number of the reference you give it. ROW(B500) always returns 500, so it is useful when a formula needs the position of another cell rather than its own row.

That makes the function useful for pointing at known anchors in the sheet. Even if the target cell is empty, ROW can still return its position because it only cares about location.

=ROW(B500) // Returns 500

Example 3 - Create an Incrementing ID

If your data starts in row 2, =ROW()-1 returns 1 in the first data row, 2 in the next one, and so on. This is a common way to build simple IDs that follow the sheet structure automatically.

This makes the example practical for lists and small tables. The numbering updates itself as the sheet changes, so you do not have to type or fix the sequence manually.

=ROW()-1 // Returns 1 in row 2, 2 in row 3, and so on

Example 4 - Measure the Distance Between Two Rows

Subtracting ROW(A1) from ROW(A150) returns 149. This is useful when the sheet needs a relative offset instead of the original row numbers themselves.

That is useful when the real question is not “what row is this?” but “how far apart are these two rows?” ROW makes that kind of spacing logic straightforward.

=ROW(A150)-ROW(A1) // Returns 149

Conclusion Recap

ROW is useful when a formula needs to know where it is on the sheet. In this lesson, that showed up in auto numbering, moving indexes, row-distance checks, and formulas that change based on position.

The easiest way to remember it is position versus size. ROW() gives one row number, while ROWS() tells you how many rows are in a range.

• Main job: ROW returns the row number of a reference or of the current formula cell.
• Flexible input: Leave the argument blank for the current row, or provide a reference for a specific row.
• Common use: It is often used for numbering, offsets, and moving indexes inside other formulas.
• Important detail: ROW looks at position only, not whether the referenced cell contains data.