Excel Table Row Number: Get the Position or Sheet Row

In Excel, a table row can mean either its position within the table or its actual worksheet row number. To number table records from 1, enter =ROW()-ROW(Sales[#Headers]) in a calculated column of a table named Sales; to return the worksheet row, use =ROW(). The right choice depends on whether you want a record number that starts at 1 or the row label shown at the left edge of the worksheet.

What “table row number” can mean

Excel displays worksheet row numbers along the left side of the grid: 1, 2, 3, and so on. These numbers identify physical worksheet rows. A table, by contrast, is a named range of related data with its own header row and optional total row. Excel does not show a separate set of built-in row labels inside a table, so “table row number” usually means a number you create to identify each data record.

For example, a table might begin in worksheet row 4. Its header is on row 4, its first record is on row 5, and its second record is on row 6. The first record is therefore table data row 1 but worksheet row 5. Neither number is inherently more correct; they answer different questions.

Worksheet row: where the record currently sits on the sheet.
Table position: how far down the record is among the table’s data rows, starting at 1.
Record ID: a deliberately assigned identifier, which may be intended to stay the same even when records are sorted or moved.

Before adding a number column, decide which of these you need. A worksheet row is useful for locating a cell. A table position is useful for displaying sequence. A durable record ID is usually better for linking the record to other data.

Get the worksheet row number

The ROW function returns the worksheet row number of a cell reference. With a record in cell A7, entering =ROW(A7) in another cell returns 7. If you enter =ROW() in a cell on worksheet row 7, it also returns 7 because the omitted reference means the cell containing the formula.

In a table, a calculated column containing =ROW() reports the physical worksheet row for each record. This can be useful when a formula, report, or troubleshooting note needs to point to the current sheet location. It is not a permanent identifier: sorting the table can move a record to a different worksheet row, and inserting rows above the table can change the returned number.

To check a formula result, compare it with the gray row labels at the left of the worksheet. If a table starts on row 1 and has headers, its first data record is normally on row 2; ROW in that record returns 2, not 1. The header is a worksheet row too, even though it is not a data record.

Number records from 1 inside a table

For a sequence that starts with 1 at the first data record, subtract the header’s worksheet row from each record’s worksheet row. Assume the table is named Sales, its headers are in worksheet row 4, and its first data row is row 5. Add a column such as Table Row, then enter this formula in its first data cell:

With a table named Sales whose header is in row 4 and whose first record is in row 5, the calculated-column formula =ROW()-ROW(Sales[#Headers]) returns 1 for the first record, 2 for the next, and so forth. For the first record, the calculation is 5 minus 4, which equals 1.

Excel tables normally fill a formula down the entire calculated column and apply it to new rows added to the table. The structured reference Sales[#Headers] refers to the table’s header row, so the subtraction adjusts naturally if the table is moved to another worksheet row. Replace Sales with the actual table name. To find or change it in desktop Excel for Windows, select a table cell and look on the Table Design tab for the Table Name box.

Use a short, clear heading such as Position or Row in Table. Avoid calling this value an order number if the sequence is only a position: sorting the table changes which record occupies position 1. If the workbook needs to preserve each record’s identity, use a separate ID column instead.

How table numbering changes when you sort or filter

A formula based on ROW calculates from a record’s current location. When you sort the table, Excel moves records and recalculates their positions. The number 1 remains at the first data row, but it may now belong to a different record. This behavior is helpful when the purpose is to show the current order, but it is unsuitable when the number must follow a record permanently.

Filtering hides records from view; it does not remove them from the table. A basic ROW-based sequence therefore does not become a compact sequence of only the visible records. For instance, if records 2 and 3 are hidden by a filter, the visible records may still display positions 1, 4, and 5. That is expected: the formula reflects table position, not visible-item count.

If you need a permanent identifier, assign unique values that do not depend on position, such as an invoice number or a manually managed ID. If you need visible records numbered consecutively after each filter, treat that as a separate requirement: it needs a formula designed to count visible records, and the result should be checked against the exact filtering and hidden-row behavior required. Do not assume a normal row-number formula will do this.

Use numbering outside the table

You can also calculate table positions in a neighboring worksheet column rather than adding a table column. This may suit a printed layout or a separate report, but it is more fragile: sorting the table alone may not sort the neighboring numbers and records together. If the numbering describes each record, keeping the formula inside the table is generally easier to maintain.

For a simple range with headers in row 1 and data beginning in row 2, a worksheet formula can subtract the header row number. With the formula in B2 and filled down beside the records, =ROW()-ROW($A$1) returns 1 in row 2, 2 in row 3, and so on. Here, A1 is assumed to be the header cell; the dollar signs keep that reference fixed as the formula is copied. This example is for an ordinary range, not a structured table reference.

For Microsoft 365 or Excel 2021 and later, a dynamic-array formula can produce a sequence in a separate area. Assume Sales is a table with an Order ID column containing one nonblank ID per data record; enter =SEQUENCE(ROWS(Sales[Order ID])) in a blank cell outside the table. If Sales has four data records, the formula spills the values 1 through 4 vertically. The spill area must be empty, and dynamic-array formulas cannot spill inside an Excel table, so this is better for a report than for a table’s own calculated column.

Choosing between a formula and a fixed ID

A row-position formula is appropriate when users should see the current order of records. It updates with the table’s location and row arrangement, making it useful for numbered lists, review sheets, and printouts. Its main limitation is that it describes where a record is now rather than identifying the record itself.

A fixed identifier is the better choice when records are referenced elsewhere. For example, a customer’s position in a sorted customer list can change, but a customer ID should remain the same. If identifiers are assigned manually, make sure they are unique and do not reuse a value after deleting a record. If IDs are generated by formulas based on row position, they may change after sorting or structural edits, defeating the purpose of a stable ID.

Use ROW() when you need the current worksheet location.
Use a table-position formula when you need numbering from 1 that follows the current table order.
Use a stable ID when other sheets, systems, or people must refer to the same record over time.
Use a separate dynamic-array sequence for a compact report or listing outside a table.

Common mistakes and fixes

Getting 2 instead of 1: ROW returns a worksheet row, so a first record on row 2 returns 2. To start table numbering at 1, subtract the header row as in the Sales formula above.

Seeing the same result in every table row: Check that the formula is in a calculated column and that the table name in the structured reference matches the actual name. If Excel does not fill the formula down, enter it in the table’s data area and check whether calculated-column formulas are enabled in Excel’s AutoCorrect options.

Numbers change after sorting: That is normal for a position formula. It recalculates based on the record’s new location. Use a fixed ID instead if the number must stay with the record.

Numbers skip after filtering: A filter hides records but does not delete them, so a basic row position can include hidden records. This is not evidence that the table has lost data. Clear the filter to see the complete sequence.

Formula errors after renaming a table: Excel usually updates structured references when a table is renamed through the Table Name box. If a formula was entered as plain text, contains a misspelled name, or refers to a deleted table, edit the reference and verify the table name and column headers.

Frequently Asked Questions

How do I show row numbers in an Excel table?
To display positions starting at 1, add a table column and use =ROW()-ROW(Sales[#Headers]), replacing Sales with your table name. To show actual worksheet row labels in the column, use =ROW() instead.

Does Excel automatically number table rows?
No. Excel labels worksheet rows, but it does not add a separate table-position number to each record automatically. Add a formula column if you want a visible sequence.

Will table row numbers change when I sort?
A formula that calculates table position changes to reflect the new order. A formula returning the worksheet row also changes when the record moves. Neither is a stable record ID.

Can I start table row numbering at zero?
Yes. With a table named Sales and the formula entered in its data column, use =ROW()-ROW(Sales[#Headers])-1. The first data record returns 0 because its worksheet row is one greater than the header row.

Why does ROW return a number larger than the table row position?
ROW reports the worksheet row, which counts the table header and any rows above the table. A table’s first data record might be position 1 but appear on worksheet row 5. Subtract the header-row number to get the position within the table.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.