Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Left Lookup in Excel
  • How to use Excel MMULT Function
  • Extract data with helper column in Excel
  • Lookup entire row in Excel
  • How to use Excel COLUMN Function

Data Analysis Examples

  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • Conditional Formatting Icon Sets Examples in Excel
  • Working With Tables in Excel
  • Example of COUNTIFS with variable table column in Excel
  • How to calculate current stock or inventory in Excel

Data Validation Examples

  • Excel Data validation require unique number
  • Excel Data validation date in next 30 days
  • Excel Data validation allow weekday only
  • Excel Data validation must not contain
  • Excel Data validation date in specific year

How to get relative row numbers in a range in Excel

by

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;

Formula

{=ROW(range)-ROW(range.firstcell)+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.

Worked Example:   How to return TRUE when all cells in range are blank in Excel

Explanation

In the example shown, the formula in B5:B11 is:

{=ROW(B5:B11)-ROW(B5)+1}

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:

{5;6;7;8;9;10;11}

The second ROW function generates an array with just one item like this:

{5}

which is then subtracted from the first array to yield:

{0;1;2;3;4;5;6}

Finally, 1 is added to get:

{1;2;3;4;5;6;7}

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:

{ROW(list)-MIN(ROW(list))+1}

With INDEX, we fetch the first reference in the named range, and using ROW on that:

{=ROW(list)-ROW(INDEX(list,1,1))+1}

You’ll often see “relative row” formulas like this inside complex array formulas that need row numbers to calculate a result.

Post navigation

Previous Post:

How to get relative column numbers in a range in Excel

Next Post:

How to get last column number in range in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to compare two text strings in Excel
  • RIGHT, RIGHTB functions: Description, Usage, Syntax, Examples and Explanation
  • How to get top level domain (TLD) in Excel
  • How to display conditional message with REPT function in Excel
  • How to Remove Leading Spaces in Excel
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Calculate days remaining in Excel
  • SECOND function: Description, Usage, Syntax, Examples and Explanation
  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • Display Date is workday in Excel
  • RATE function: Description, Usage, Syntax, Examples and Explanation
  • ODDFYIELD function: Description, Usage, Syntax, Examples and Explanation
  • Future value vs. Present value examples in Excel
  • YIELD function: Description, Usage, Syntax, Examples and Explanation
  • PV function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles