Excel Formula for Serial Numbers: Methods That Work

The simplest Excel formula for a serial number is =ROW()-1, entered in A2 when row 1 contains a heading. It returns 1 in A2, 2 in A3, and so on; change the subtraction if your list starts on a different row.

What a Serial Number Means in Excel

In a worksheet, a serial number usually means a consecutive number assigned to each row in a list: 1, 2, 3, and so forth. It can make a printed list easier to follow, show the order of records, or provide a simple count beside a set of entries. Excel does not have one special function named SERIALNUMBER; the best formula depends on whether you want numbers tied to row position, only to populated records, or to remain fixed after changes.

There is a related meaning that can cause confusion: Excel also represents dates with serial values internally. A date displayed as a calendar date is stored as a number and formatted as a date. That date serial is not the same thing as a numbered list. If you want row labels such as 1, 2, and 3, use a numbering formula rather than changing a date’s number format.

Use ROW for a Number Based on Position

Suppose row 1 contains the heading “Serial number,” and the first record is in row 2. With the first serial number in A2, enter =ROW()-1. ROW returns the worksheet row number, so row 2 minus 1 is 1. When you fill the formula down, the row number changes automatically: A3 returns 2, A4 returns 3, and A20 returns 19.

If the first record starts lower down, subtract the number of rows above the first record. For example, with headings in rows 1 through 4 and the first record in row 5, enter =ROW()-4 in A5. It returns 1; the formula in A6 returns 2. The general rule is to subtract the worksheet row immediately before the first numbered row.

To fill the formula, select the cell containing it and drag the small square at the lower-right corner of the selection (the fill handle) down the column. You can also select the formula cell and the cells below it, then choose Home > Fill > Down. Excel for the web has the same basic fill options; the precise interface can vary slightly by browser window size.

Use ROWS to Count Entries from a Starting Point

ROW numbers a worksheet position. ROWS instead counts how many rows are in a range, which can make the intended sequence easier to understand when you copy a formula down. Assume the records are in B2:B20 and the serial numbers will go in A2:A20. Enter =ROWS($B$2:B2) in A2 and fill it down. The first range contains one row, so the result is 1; in A3 the range expands to B2:B3 and returns 2.

The dollar signs keep the start of the range fixed while its end expands as the formula is copied. This approach is useful when you want the sequence to begin at 1 regardless of the worksheet row where the list sits. It counts rows, not records, however. If there is a blank record row in the middle, the next row’s serial number still advances, leaving a gap in the visible list.

If you want numbers only beside populated records and want blank rows to stay blank, use the data column to control the result. Assume B2:B20 contains the record names and A2:A20 is for serial numbers. In A2 enter =IF(B2="","",COUNTA($B$2:B2)) and fill down. With a name in B2, A2 displays 1; if B3 is empty, A3 stays blank; a name in B4 gives A4 the value 2. COUNTA counts nonempty cells in the range. If your data cells contain formulas that return empty text, note that COUNTA can treat those formula cells as nonempty.

Generate a Whole Numbered List with SEQUENCE

In Microsoft 365 or Excel 2021 and later, SEQUENCE can create a complete run of numbers with one formula. If you want ten numbers beginning at 1, enter =SEQUENCE(10) in A2. The results spill into the cells below, displaying 1 through 10. Keep those spill cells clear: existing content in the output area prevents Excel from displaying the full result and produces a spill error.

SEQUENCE can also specify the number of rows, columns, starting value, and step. For example, with the formula entered in A2, =SEQUENCE(5,1,10,10) returns 10, 20, 30, 40, and 50 in a vertical list. The arguments mean five rows, one column, start at 10, and increase by 10. Use this when you need a planned series rather than a formula copied separately into every row. The results are dynamic-array output, so you generally edit the single formula at the top rather than individual numbers in the spill range.

Choose a Formula for an Excel Table

For a list that grows over time, format the range as an Excel table by selecting a cell in the data and choosing Insert > Table. Confirm that the table has headers if it does, then add a serial-number column. A table can extend its calculated-column formula when you add new records, which is more convenient than repeatedly copying a formula into a plain range.

Assume the table is named Table1, its header row is the table’s header, and you are entering the serial formula in its first data row. Use =ROW()-ROW(Table1[#Headers]) in the table’s serial-number column. The formula subtracts the header row’s worksheet position from the current row’s position, so the first data row returns 1. Excel typically propagates a formula entered in a table column to the other rows in that column. If your table has another name, replace Table1 with its actual name.

Table formulas based on row position are still position-based: sorting the table changes which record appears first and therefore which row gets each number. That is often correct for a display sequence, but not for a permanent record identifier. Decide whether the number should describe the current order or identify the record before choosing this method.

What Happens When You Sort, Insert, or Delete Rows

A formula such as =ROW()-1 recalculates from the cell’s current location. If you sort the entire table, the formula can show a fresh sequence in the new order, which is useful for numbering a report. If you sort only the serial-number column, however, the numbers can become detached from their records. Sort the whole table or data range so each row’s fields stay together.

Inserting a row can shift the numbering, and deleting a row can cause the remaining formulas to return the row-based sequence for their new positions. This makes formula numbering self-adjusting, but it also means the numbers are not guaranteed to remain permanent. A serial number produced by a formula is not automatically a unique ID. If another system, a customer, or a later process relies on an identifier never changing, create and preserve a fixed ID instead of relying on a row formula.

To keep the current numbers as values, copy the numbered cells and use Home > Paste > Values. That replaces the formulas with their displayed results. Those values will no longer renumber automatically after sorting or inserting rows, so first confirm that fixed numbering is what you want. Keep a backup if the original formulas may be useful later.

Number Only Visible Rows After Filtering

A normal ROW formula reflects worksheet position, including rows hidden by a filter. If you want the displayed records to be numbered consecutively after filtering, use SUBTOTAL with a visible-data column. Assume B2:B20 contains a value for each record and A2:A20 is the serial-number column. In A2 enter =IF(B2="","",SUBTOTAL(103,$B$2:B2)) and fill it down. With all records visible, the first populated row returns 1; after a filter hides earlier records, the first visible populated row can return 1 instead.

In this formula, 103 tells SUBTOTAL to count nonempty visible cells while excluding rows hidden by filters and manually hidden rows. The IF test leaves the serial cell blank when its corresponding B cell is blank. This is a display sequence that can change when the filter changes; it should not be used as a permanent ID. Also make sure the chosen data column is populated for every record you expect to number, because the count depends on that column.

Common Problems and Practical Checks

The first number is 0: Check the subtraction. If the formula is in row 2, =ROW()-2 returns 0; use =ROW()-1 to start at 1.
Every row shows the same number: The formula may contain a fixed row reference, such as =ROW($A$2)-1. Use ROW without locking the row so it changes as the formula moves.
The list has gaps: A position-based formula counts blank rows. Use the IF and COUNTA approach if you want blank records omitted, or remove unintended blank rows.
SEQUENCE shows a spill error: Clear the cells where the results need to appear. A spill formula cannot overwrite existing content or merge into obstructing cells.
The numbers change after sorting: That is expected for formulas tied to current row order. Sort all related columns together, or preserve fixed values if the numbers must stay attached to records.
Excel displays a date instead of a number: The cell may have a date number format. Select the cells and choose Home > Number Format > General or Number, as appropriate.

Frequently Asked Questions

What is the simplest formula to number rows in Excel?
With a heading in row 1 and the first serial number in A2, use =ROW()-1 and fill down. The first result is 1, and each following row increases by 1.

How do I start serial numbers at 100?
Assuming the first serial number goes in A2, enter =ROW()+98. A2 returns 100 and A3 returns 101. Change the row adjustment if your first numbered cell is somewhere else.

Can Excel number only rows that contain data?
Yes. If records are in B2:B20 and serial numbers in A2:A20, use =IF(B2="","",COUNTA($B$2:B2)) in A2 and fill down. It leaves the serial cell blank when the corresponding B cell is blank and counts populated cells above it.

Does a formula-generated serial number stay fixed after sorting?
Not necessarily. ROW-based formulas reflect worksheet position, and table formulas can recalculate in the new order. For a number that must remain attached to a record, use a fixed value or another stable identifier.

Can I create serial numbers without dragging a formula?
Yes. In Microsoft 365 or Excel 2021 and later, enter =SEQUENCE(10) in the first output cell to spill numbers 1 through 10 down the sheet. Make sure the cells below it are empty.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.