How to get relative row numbers in a range in Excel
To get a full set of relative row numbers in a range, you can use an array formula based on the ROW function. See example below;
Note: this is an array formula that must be entered with Control + Shift + Enter. If you’re entering this on the worksheet (and not inside another formula), make a selection that includes more than one row, enter the formula, and confirm with Control + Shift + Enter.
In the example shown, the formula in B5:B11 is:
This is formula will continue to generate relative numbers even when the range is moved. However, it’s not a good choice if rows need to be sorted, deleted, or added, because the array formula will prevent changes.
How this formula works
The first ROW function generates an array of 7 numbers like this:
The second ROW function generates an array with just one item like this:
which is then subtracted from the first array to yield:
Finally, 1 is added to get:
Generic version with named range
With a named range, you can create a more generic version of the formula using the MIN function or the INDEX function. For example, with the named range “list”, you can use MIN like this:
With INDEX, we fetch the first reference in the named range, and using ROW on that:
You’ll often see “relative row” formulas like this inside complex array formulas that need row numbers to calculate a result.