Quick, clean, and to the point

Get relative row numbers in range

Excel formula: Get relative row numbers in range
Generic formula 

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:


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 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. The formula options explained here are will work better.


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.


With the SEQUENCE function the formula to return relative row numbers for a range is simple:


The ROWS function provides the count of rows, which is returned to the SEQUENCE function. SEQUENCE then builds an array of numbers, starting with the number 1.  So, following the original example above, the formula below returns the same result:

=SEQUENCE(ROWS(B5:B11)) // returns {1;2;3;4;5;6;7}

Note: the SEQUENCE formula is a new dynamic array function available only in Excel 365.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas