Relative Row Numbers in Excel: How to Get Them

This article explains how to get the relative row number of a cell within a range in Excel or Google Sheets — that is, the position of a cell counting from the top of a specific range, not the worksheet. You enter a range and a reference, and the formula returns a number like 1 for the first row of the range, 2 for the second, and so on.

Formula

The general formula to get the relative row number of a cell ref within a range rng is:

ROW(ref) - ROW(rng) + 1

Where:

• ROW(ref) — the absolute row number of the reference cell (e.g., for A5, it returns 5).
• ROW(rng) — the absolute row number of the first row of the range (e.g., for A10:A20, it returns 10).
• The +1 makes the first row of the range return 1 instead of 0.

If the range is not in column A, the formula still works because it uses row numbers only. For example, if the range is C5:C15 and the reference is C8, the result is 8 - 5 + 1 = 4.

Worked Example

Using the default values from the calculator above: reference = A1, range = A1:A10.

1. Get the absolute row of the reference: ROW(A1) = 1.
2. Get the absolute row of the first cell of the range: ROW(A1:A10) = 1.
3. Subtract: 1 - 1 = 0.
4. Add 1: 0 + 1 = 1.

So the relative row number is 1, which matches the calculator output.

Why Use Relative Row Numbers?

Relative row numbers are useful when you want to reference a cell’s position within a specific block of data, rather than its absolute position on the sheet. Common use cases include:

• Creating a numbered list that starts at 1 even if the data begins on row 5.
• Building formulas that need to know which row of a table a value is in (e.g., INDEX/MATCH with a relative offset).
• Writing conditional formatting rules that depend on the position within a range.
• Generating sequential numbers in a filtered or sorted list (combined with SUBTOTAL).

For example, if you have a table starting at row 10 and you want to label each row 1, 2, 3…, you can put =ROW()-9 in the first data row and copy down. The formula above generalizes that so you don’t have to hard-code the offset.

How to Use the Formula in Excel or Google Sheets

In a cell, type the formula with your actual range and reference. For instance, if your data is in B2:B20 and you want the relative row for B7, enter:

=ROW(B7)-ROW(B2:B20)+1

In Excel, you may need to press Ctrl+Shift+Enter if you are using an array formula, but for this simple case, Enter works in modern versions. In Google Sheets, Enter works fine.

If you want the formula to automatically update when you drag it down, use absolute references for the range (e.g., =ROW(A5)-ROW($A$1:$A$10)+1). The reference part can be relative so it changes as you copy.

Common Mistakes and Edge Cases

• Not adding the +1 — without it, the first row of the range returns 0, which is usually not what you want.
• Using the wrong range — if the range does not include the reference cell, the formula returns a negative number or a number larger than the row count. The calculator above checks this and returns #N/A.
• Forgetting that ROW() with a range returns the first row — in Excel, ROW(A5:A10) returns 5, not an array, unless you use it in an array context. The formula works because it only needs the first row.
• Hidden rows or filters — the formula ignores visibility. If you need relative row numbers that skip hidden rows, use SUBTOTAL(3, ...) or a helper column.
• Merged cells — merged cells can cause ROW() to return unexpected values because the merge may hide rows. Avoid using this formula with merged ranges.

Alternatives to ROW()

If you only need a sequence of numbers in a column, you can use:

• Fill handle — type 1 and 2, select both, and drag down.
• SEQUENCE function (Excel 365 / Google Sheets) — =SEQUENCE(10) gives 1 to 10 in a column.
• ROW() without a range — =ROW()-1 in row 2 gives 1, but this is absolute, not relative to a range.
• MATCH function — if you have a list of unique values, =MATCH(A2, A$2:A$100, 0) gives the position within the list.

Frequently Asked Questions

How do I get the row number relative to a table in Excel?

If you have an Excel Table (created with Ctrl+T), you can use =ROW()-ROW(Table1[[#Headers],[Column1]]) to get the row number relative to the data rows, starting at 1 for the first data row.

What if my range starts at a row other than 1?

The formula works for any starting row. For example, if your range is C10:C20, the relative row number for C15 is ROW(C15)-ROW(C10:C20)+1 = 15-10+1 = 6.

Can I use this in Google Sheets?

Yes, the ROW function works the same way in Google Sheets. The formula is identical.

Why does my formula return a negative number?

That happens when the reference cell is above the range. For example, if your range is A10:A20 and you use A5, the formula gives 5-10+1 = -4. Make sure the reference is inside the range.

How do I get the relative column number too?

Use the same logic with COLUMN() instead of ROW(). For a range C5:F5 and reference E5, the relative column is COLUMN(E5)-COLUMN(C5:F5)+1 = 5-3+1 = 3.

Does this work with non-contiguous ranges?

No, ROW() with a non-contiguous range returns the first row of the first area only. For non-contiguous ranges, you would need a more complex formula or VBA.

Related

• Two Browsers Daily: Better Security?
• iOS 8 Questions Resolved: Part 1
• Quantum Sensors: Sensitivity, Noise & Practical Limits

in ref | Cell reference | - | A1 | 0
in rng | Range | - | A1:A10 | 0
out rel | Relative row number | - | IF(AND(ROW(ref)>=ROW(rng), ROW(ref)=COLUMN(rng), COLUMN(ref)