Sequence Number Formula in Excel: Methods That Work

Formulas & functions · Updated November 20, 2026

For a numbered list starting in cell A2, enter =ROW()-1 and fill the formula down; the first result is 1, followed by 2, 3, and so on. In Microsoft 365 or Excel 2021, you can also generate a whole series at once with =SEQUENCE(10), which returns the numbers 1 through 10 in a spill range.

Quickest formula for a numbered list

If row 1 contains a heading and your first item is in row 2, the ROW formula is a straightforward choice. With the first sequence number in A2, =ROW()-1 returns 1 because A2 is on row 2. Copy or fill it down to number the rows below it. If your first item is on a different row, adjust the subtraction: for a first item in A5, use =ROW()-4 in A5 so that it returns 1.

This method numbers worksheet positions, not the contents of another column. If you delete a row, insert a row, or sort the list, the result is based on the formula’s current row. That is useful when the numbers should show the list’s current order. If you need permanent identifiers that stay attached to the same record even when it moves, use fixed values or a separate ID system instead.

To fill the formula down, drag the fill handle at the lower-right corner of A2, or select the formula cell and the cells beneath it and use Ctrl+D on Windows. In Excel for Mac, Control+D fills down as well. You can also use Home > Fill > Down. If there are many rows, double-clicking the fill handle can extend the formula alongside a neighboring column of data.

Use ROWS for a sequence that starts at one

A common alternative is the ROWS function. With the first sequence number in A2, enter =ROWS($A$2:A2) and copy it down. The first argument range contains one row, so the answer is 1. As the formula is filled down, its ending reference expands: the next row counts two rows, then three. The dollar signs keep the starting cell fixed while the other end advances.

This formula is handy when you want to make the starting point obvious or avoid calculating the row offset yourself. For example, if your sequence starts in D7, put =ROWS($D$7:D7) in D7. It returns 1, and the next filled row returns 2. The formula does not require the sequence to begin in row 2, and it is easy to inspect later because the range shows exactly where counting begins.

ROW and ROWS are both suitable for ordinary filled-down lists, but they express slightly different logic. ROW uses a worksheet row number and an offset; ROWS counts cells in a growing range. Either can be used for a simple continuous sequence. Choose the one that makes the starting row and intended behavior clearest to the people who will maintain the workbook.

Generate a series with SEQUENCE

SEQUENCE creates an array of numbers in one formula rather than requiring a formula in each row. With no arguments other than the number of rows, =SEQUENCE(10) returns 1 through 10 vertically. Enter it in a clear cell, such as A2, and Excel spills the results into the cells below. The cells in that spill range must be empty.

SEQUENCE can also specify the number of columns, the starting value, and the step between values. With the formula entered in A2, =SEQUENCE(5,1,100,10) returns five values in one column: 100, 110, 120, 130, and 140. The arguments mean five rows, one column, a start of 100, and an increment of 10. To make a horizontal sequence, change the first two arguments; for example, two rows and three columns produce a two-by-three array.

SEQUENCE is available in Microsoft 365 and Excel 2021 or later. In Excel for the web and supported Excel for Mac versions, dynamic arrays also spill into neighboring cells, although menus and interface details may differ from Windows. If a workbook must work in an older Excel version that does not support SEQUENCE, use a filled-down ROW or ROWS formula instead.

Choose a formula for your numbering goal

The right formula depends on what the number is meant to represent. A position number is not the same as a record identifier, and a continuous count is not always the right answer when data has blanks or filters.

  • Number every row by its current position: use ROW or ROWS and fill down. This is suitable for a basic list that should read 1, 2, 3 in worksheet order.
  • Create a fixed-length series quickly: use SEQUENCE when available. It is useful for calendars, sample data, numbered steps, and other generated arrays.
  • Number only rows that contain data: use an IF test around a running count so blank records remain unnumbered.
  • Count only visible rows in a filtered list: use SUBTOTAL with a reference column that is filled for each real record.
  • Keep a durable record ID: use a value that is assigned once and preserved. A row-based formula can change when the order or layout changes.

Before choosing, decide whether gaps are acceptable. If a deleted item should leave a missing number, permanent IDs may be appropriate. If the list should always be renumbered consecutively, a position formula is usually a better fit.

Number rows only when a record exists

Suppose column B contains item names beginning in B2, and column A should show a sequence only beside nonblank names. With the first number in A2, use =IF(B2="","",COUNTA($B$2:B2)) and fill down. The formula returns a blank when B is empty; otherwise, it counts nonempty cells from B2 through the current row.

For example, if B2 contains Apples, B3 is empty, and B4 contains Pears, the formula shows 1 in A2, leaves A3 blank, and shows 2 in A4. COUNTA counts the populated entries, so the number after a blank continues without a gap. This differs from ROW()-1, which would show 3 in A4 because it numbers the worksheet position.

Pick a column that reliably indicates whether a row is a real record. If column B can be empty for a valid record, test a different field, such as a required ID or date. Also note that a cell containing a formula that returns an empty string is not truly empty to COUNTA. If the field being counted contains formulas, that can affect the running count; use a column with actual entered values or adapt the logic to the structure of the data.

Renumber visible rows after filtering

When a filter hides some records, you may want the visible results to be numbered 1, 2, 3 rather than retaining their original positions. Assume records begin on row 2 and column B contains a value for every record. In A2, enter =IF(B2="","",SUBTOTAL(103,$B$2:B2)) and fill it down. As a filter hides rows, SUBTOTAL with function number 103 counts nonempty visible cells in the expanding range, so visible rows receive a running count.

Use a consistently populated column in the reference. If a visible record has a blank in B, it will not be counted and may produce an unexpected number. Function number 103 excludes filtered-out rows and manually hidden rows. Function number 3 also excludes rows removed by a filter but includes manually hidden rows, so 103 is the better match when both kinds of hidden rows should be omitted.

Filtering does not delete records; it only hides rows from view. If the numbering is meant to be an enduring identifier, do not replace IDs with a visible-row count. This formula is for a display sequence that should change as the visible subset changes.

Common problems and how to fix them

  • The formula displays the wrong starting number: check the row where the first item begins. With =ROW()-1, the first formula must be in row 2 to return 1. For other starting rows, subtract one less than the starting row number.
  • SEQUENCE returns a spill error: clear cells in the area where the results need to appear. A value, another formula, or a merged cell can block the spill range. The formula needs enough empty cells for its requested rows and columns.
  • The numbering skips or repeats: check whether the formula was filled into every intended row and whether the anchor in a ROWS formula is fixed with dollar signs. In the example =ROWS($A$2:A2), the first reference stays fixed and the ending reference changes as the formula is copied.
  • Numbers appear beside blank records: a plain ROW or ROWS formula numbers every row, whether or not it contains data. Use an IF condition that checks a reliable record field if blanks should stay blank.
  • The formula is shown as text: confirm the cell is not formatted as Text, then set it to General and re-enter the formula. Also check that it begins with an equals sign and that calculation is enabled.
  • Excel rejects commas in a formula: some regional settings use semicolons as argument separators. If Excel expects semicolons, replace the commas with the separator used by that installation; the function and argument order are unchanged.

Formula numbers versus fixed numbers

A formula-generated sequence is convenient because it updates when rows are added, removed, or reordered, depending on the formula. That same flexibility can be a drawback: a value based on row position can change after sorting or restructuring. In particular, do not treat a number produced by ROW or ROWS as a permanent customer, invoice, or product ID unless changing that ID is acceptable.

For a list whose numbers simply show current order, formulas are usually easier to maintain than manually typing 1, 2, 3. For stable IDs, assign values that do not recalculate from position and preserve them when sorting. Excel Tables can make data ranges easier to expand and sort, but a calculated numbering formula still represents a formula result, not an immutable ID. Decide what the number means before selecting a method.

Frequently Asked Questions

What is the simplest sequence number formula in Excel?

For a list starting in row 2, enter =ROW()-1 in the first sequence cell and fill down. If the first list item is in another row, change the offset so that the first result is 1.

How do I automatically number rows in Excel?

Enter a ROW or ROWS formula in the first number cell and copy it down the required range. For a dynamic array in Excel 2021 or Microsoft 365, use SEQUENCE with the number of rows you need, such as =SEQUENCE(10).

Can Excel number only rows that are not blank?

Yes. Test a column that indicates a real record with IF, and count populated cells in that column with COUNTA. This leaves blank rows unnumbered while keeping the next record’s number consecutive.

Why does my sequence start at zero or at the wrong number?

The row offset may not match the formula cell’s location. For a first item in row 2, =ROW()-1 returns 1; in row 3 it returns 2. SEQUENCE can also start elsewhere if you supply a different starting-value argument.

Does SEQUENCE work in every Excel version?

No. SEQUENCE is available in Microsoft 365 and Excel 2021 or later, but not in some older perpetual Excel releases. Use a filled-down ROW or ROWS formula when sharing with users whose Excel version may not support dynamic arrays.

Related

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.