Get relative row numbers in range

To get a full set of relative row numbers in a range, you can use an array formula based on the ROW function. In the example shown, the formula in B5:B11 is: {=ROW(B5:B11)-ROW(B5)+1} 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. This 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. The formula options explained here will work better.

April 14, 2025 · 1 min · 84 words · Danielle Stephenson

Table of Contents

SEQUENCE then builds an array of numbers, starting with the number 1.

For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.

When no reference is provided, ROW returns the row number of the cell which contains the formula.

Excel formula: Automatic row numbers

SEQUENCE Function

The Excel SEQUENCE function generates a list of sequential numbers in an array.

The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.

…

ROWS Function

The Excel ROWS function returns the count of rows in a given reference.

Excel formula: Last row number in range

Excel formula: First column number in range

Excel formula: Last column number in range

Excel formula: Get relative column numbers in range

Excel formula: Get nth match with INDEX / MATCH

Excel formula: Create array of numbers

Excel formula: INDEX and MATCH all partial matches

Excel ROW function

Excel SEQUENCE function

Excel ROWS function