An Excel row index is a row’s position: on a worksheet, it is the number shown at the left edge, from 1 to 1,048,576. In formulas, “row index” can also mean a row’s position within a selected range, which may not be the same as its worksheet row number.
What an Excel row index means
Every Excel worksheet has numbered rows and lettered columns. The row labels appear along the left side of the grid, and the column labels appear across the top. If a cell is at the intersection of column C and row 7, its address is C7; the 7 is its worksheet row number. Excel starts counting at 1, not 0, and a worksheet can contain up to 1,048,576 rows.
The phrase “row index” is not always used consistently. A person may mean the visible worksheet row number, the position of a record inside a selected range, or the row argument supplied to a function such as INDEX. Those meanings can produce different numbers for the same record. For example, a value in worksheet row 4 is the third item in the range A2:A10. Knowing which kind of index a task needs prevents off-by-one mistakes.
In ordinary use, the row number is already visible; you do not need to calculate it just to identify a cell. Formulas are useful when the row number needs to appear in a report, update as data changes, or serve as an input to another calculation.
Show a worksheet row number with ROW
The ROW function returns the worksheet row number of a reference. With the value you want to label in A2 and a formula entered in B2, =ROW(A2) returns 2. Fill the formula down beside the data, and the result becomes 3 beside row 3, 4 beside row 4, and so on. The returned number describes the sheet location, not the record’s position starting at 1 in your data.
If you omit the reference, ROW returns the row number of the cell containing the formula. For example, a formula entered in F12 using =ROW() returns 12. This can be handy for a quick location check, but it means the result changes if the formula is moved to another row.
To make the first data row display 1 instead of its worksheet row number, subtract the worksheet row above the data and add 1. With the first record in A2 and a formula in B2, =ROW(A2)-ROW($A$2)+1 returns 1. Fill it down and the subsequent records are numbered 2, 3, and 4. The dollar signs keep the starting reference fixed while the formula is copied.
This relative numbering is often more useful for lists because it stays meaningful if the list begins lower down the sheet. If the first record moves from row 2 to row 8 and you update the formula’s starting reference, the first data item can still be numbered 1.
Get a position within a range using MATCH
MATCH answers a different question from ROW: it tells you where a value occurs inside a lookup range. Suppose A2:A6 contains the IDs K-101, K-102, K-103, K-104, and K-105, and E2 contains K-104. With that layout, =MATCH(E2,A2:A6,0) returns 4. The final 0 requests an exact match. The result is the fourth position in A2:A6, even though K-104 is on worksheet row 5.
This distinction matters when the lookup range does not start in row 1. If the range is A2:A6, its first position is 1, not 2. MATCH counts from the start of the range; it does not report the sheet row. If you need the worksheet row instead, add the range’s starting row minus 1 to the position. For the same example, the fourth position corresponds to worksheet row 5.
MATCH is a good choice when the question is “Which item is this within my list?” It can also be combined with other functions to locate a related value. If the lookup value occurs more than once, an exact-match MATCH returns the position of the first matching occurrence. If there is no match, it returns the #N/A error, so check spelling, spaces, and data types when a result is unexpected.
How INDEX uses a row number
INDEX returns a value from a specified position in a range or array. Its row number is relative to that range, not automatically a worksheet row number. Suppose B2:D6 is a five-row by three-column block and the cell in the third row and second column of that block contains “In progress.” With that layout, =INDEX(B2:D6,3,2) returns “In progress.” The row argument 3 points to worksheet row 4 because the range begins on row 2; the column argument 2 points to column C because the range begins in column B.
A common source of confusion is treating INDEX’s row argument as an absolute sheet address. Giving INDEX a row number of 5 means “the fifth row in the supplied array,” not necessarily “worksheet row 5.” If the array starts at row 10, its fifth row is worksheet row 14. Check the first cell of the array before using a row index in INDEX.
INDEX is useful when a position has already been found or calculated and you need the corresponding value. MATCH can supply that position. For example, a lookup can find the position of a product ID in one column and INDEX can return the value from the same position in another column. Keeping the lookup range and return range aligned is essential: if one begins on a different row or contains a different number of records, the returned item may not correspond to the match.
Number records in a list or Excel Table
A worksheet row number is not always a suitable record number. Headers, notes, blank lines, and totals may sit between or around the records. If a list starts at row 5, its first record is on worksheet row 5 but might logically be record 1. Use a relative index when the number should describe the record’s order rather than its sheet location.
For a simple contiguous list, the relative ROW calculation shown earlier creates a sequence that follows the row positions. It is easy to understand, but blank rows can still receive numbers if the formula is filled through them. If the list is sorted, the formula numbers recalculate according to the rows’ new positions. That is usually helpful for an order number, but not if the number is meant to be a permanent identifier for each record.
Excel Tables, created with Insert > Table, make lists easier to expand and filter. A formula entered in a table column is generally copied into the other rows of that calculated column and into new rows. You can use a relative row-number formula in a table, but consider whether you want a position that changes when the table is sorted, or a fixed ID that travels with each record. A sequential position is not a substitute for a unique identifier such as an invoice number or customer key.
Filtering hides rows from view; it does not renumber the underlying worksheet. The row labels continue to show their original worksheet positions, so visible records might appear as rows 2, 5, and 9 rather than 1, 2, and 3. Likewise, sorting rearranges records but does not reset the worksheet’s row labels. Decide whether you need a physical sheet location, a position in the current order, or a permanent record ID before choosing a method.
Choose the right kind of row index
Use the meaning of the question to choose a method:
- “What worksheet row is this cell on?” Use the visible row label or ROW with a cell reference.
- “Is this the first, second, or third item in my range?” Use MATCH to find an item’s position, or a relative row calculation to number a list.
- “Which row of this array should INDEX return?” Supply a position relative to the first row of the array.
- “What permanent number belongs to this record?” Store a fixed ID rather than relying on a row position that may change after sorting or inserting rows.
It also helps to distinguish a row index from a cell address. An address such as D8 identifies both a column and a row; the row index alone is 8. If a task asks for the “index” of a record, check whether it expects a number starting at 0 or 1. Excel worksheet rows and the usual positions returned by functions such as MATCH start at 1. Some programming tools use zero-based positions, where the first item is 0, but that is not Excel’s worksheet numbering convention.
Common row-index problems and fixes
The number is off by one. This commonly happens when a formula mixes a worksheet row with a relative position. A record in row 5 is position 4 in a range starting at row 2. Confirm whether the expected answer is a sheet row or a range position, and check where the range begins.
Copied formulas show the same number. A reference such as $A$2 is fixed when copied. That is useful for the starting point in a relative-number formula, but using only a fixed reference inside ROW will keep returning the same worksheet row. Use a changing reference such as A2, or use ROW() in each row when the formula is placed directly beside the data.
Numbers change after sorting. Formulas based on row positions recalculate with the data’s new locations. This is expected for a position number. If the number must remain attached to the same record, assign a fixed ID and do not use the current row number as the identifier.
A lookup returns #N/A. With MATCH, an exact match can fail because of an extra space, a spelling difference, or a number stored as text in one place and as a number in another. Compare the lookup value and list entries, and verify that the lookup range includes the expected item. A missing match is different from an incorrect row index.
Hidden or filtered rows seem to skip numbers. The row headers reflect worksheet locations, including rows that are hidden by a filter. A normal ROW-based formula also reflects row locations; it does not automatically count only visible records. If a report needs a visible-record sequence, that is a separate requirement from retrieving the worksheet row number.
Frequently Asked Questions
What is the row index in Excel?
It usually means a row’s position. That may be the worksheet number shown at the left of the grid, or the position of an item inside a selected range. Check the context because those values can differ.
How do I find the row number of a cell?
Read the number beside the row, or use ROW with the cell reference. For example, a reference to D8 has worksheet row number 8. ROW returns the sheet location, not the item’s position within a list.
Does Excel start row numbering at 0 or 1?
Excel worksheet rows start at 1. Positions returned for a range by functions such as MATCH also start at 1. Zero-based indexing is used in some programming contexts, but it is not Excel’s worksheet row numbering.
What is the difference between ROW and MATCH?
ROW returns a worksheet row number for a reference. MATCH returns the relative position of a matching value inside a lookup range. For a range beginning in row 2, its first item has MATCH position 1 but worksheet row number 2.
Why does a row number change when I sort?
A worksheet row number identifies a location in the grid, and a formula that calculates position reflects the current arrangement. Sorting moves records to different rows. Use a fixed record ID if a value must remain associated with the same item regardless of its position.
Related
- Power Query Basics: Import, Clean, and Transform Data
- Append Queries in Power Query: How to Combine Tables
- Power Query Editor: What It Is, How It Works, and Why
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Nested IF Statements in Excel: Examples, Limits, and TipsNested IF statements in Excel put one IF function inside another, letting a formula choose among several outcomes.…
- XLOOKUP Spreadsheet Examples for Everyday Excel TasksXLOOKUP finds a value in one range and returns the corresponding value from another range; for example, with…
- Excel Formula Greyed Out? Causes and FixesIf Excel’s formula commands are greyed out, first press Esc to leave cell-edit mode, then select a worksheet…
- Roundup Excel Cell Values: 10 Ways to Round NumbersTo round a number up to the next whole number in Excel, with the value in A2, enter…
- Excel Compare Two Sheets: Methods and FormulasTo compare matching cells on two sheets in the same workbook, enter ='Sheet1'!A1='Sheet2'!A1 in a blank cell; Excel…
- Excel Formula to Split Text in a Cell: A Practical GuideIn Excel for Microsoft 365, split text in a cell with =TEXTSPLIT(A2,","), replacing the comma with the character…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.