Excel Ordered List Formula: Number Rows Automatically

Formulas & functions · Updated November 20, 2026

In Excel for Microsoft 365, enter =ROWS($A$2:A2) in B2 when your list items are in A2:A20, then fill the formula down to number each row in order. The first result is 1, the next is 2, and so on; if you mean an alphabetically or otherwise sorted list, use a sorting formula instead.

What “ordered list” means in Excel

People use “ordered list” to mean two different things: a list with consecutive numbers beside its entries, or entries arranged in a particular order, such as A to Z. Excel handles these jobs differently. A numbering formula displays a position for each row, while a sorting formula changes the order in which the items appear.

For an ordinary numbered list, put the entries in one column and the sequence numbers in the next. For example, if A2:A5 contains apples, pears, plums, and grapes, enter the numbering formula in B2 and fill it through B5. The results are 1, 2, 3, and 4, aligned with the corresponding entries. The formula does not alter the entries themselves.

This is useful when you add records, create a checklist, or want numbers that can update after rows are inserted or removed. It also avoids typing each number manually, which can lead to skipped or repeated values.

Use ROWS to number rows

With list items in A2:A20 and the first number going in B2, enter =ROWS($A$2:A2). In B2 it returns 1 because the range contains one row. When filled down to B3, the formula becomes =ROWS($A$2:A3) and returns 2; in B4 it counts three rows and returns 3.

The dollar signs keep the beginning of the range fixed at A2 as you copy the formula. The final reference, A2, is relative and moves down one row at a time. This combination is what makes the count increase. To start at a different number, add an offset: with the same layout and the first number in B2, use =ROWS($A$2:A2)+99 to start at 100.

To fill the formula down, select B2 and drag the small square at the lower-right corner of the selection (the fill handle) to the last list row. You can also select B2:B20 and use Home > Fill > Down. On Windows, Ctrl+D fills the selected range down; on Mac, Control+D is the usual Excel Fill Down shortcut. Check that the top cell contains the intended formula before filling a large range.

Number only rows that contain an item

A straight ROWS formula counts every row, including blank rows. If your list has gaps and you want the visible numbers to remain consecutive, use a conditional formula. With entries in A2:A20 and numbering in B2:B20, enter =IF(A2<>"","","") only if you want blank rows to stay blank? No—the correct formula for numbering nonblank entries is =IF(A2<>"",COUNTA($A$2:A2),"").

For example, if A2 contains “Apples,” A3 is empty, and A4 contains “Pears,” the formula returns 1 in B2, leaves B3 blank, and returns 2 in B4. It checks whether the current item cell has content, then counts populated cells from the first list row through the current row. This version is intended for a source list whose empty cells are genuinely empty; cells containing formulas that display an empty string can be counted by COUNTA.

If the list is contiguous and has no blank item rows, the simpler ROWS formula is easier to read and maintain. If blanks are meaningful, choose the conditional version and confirm that the source cells behave as expected before relying on the numbering.

Create a numbered list with SEQUENCE

If you want a fixed run of numbers without copying a formula, use SEQUENCE. With an empty area starting at B2 and a list of 10 numbers desired, enter =SEQUENCE(10) in B2. In Microsoft 365, Excel spills the results into B2:B11 as 1 through 10. Keep the cells below the formula clear, because existing content in the spill area produces a #SPILL! error.

SEQUENCE can also specify the starting value and increment. With an empty spill area beginning at B2, =SEQUENCE(5,1,10,10) returns five values in one column: 10, 20, 30, 40, and 50. The arguments represent the number of rows, number of columns, starting value, and step. SEQUENCE is available in Microsoft 365 and Excel 2021 or later; older perpetual versions may not recognize it.

Use this approach for a standalone index, a numbered schedule, or a known-size series. It does not automatically create a number beside each item unless the number of rows matches the list and the output is positioned alongside it. For a list that changes in length, a copied ROWS formula or a dynamic-array formula based on the list is usually more convenient.

Make numbering expand with an Excel table

For a list that grows regularly, format the data as an Excel table. Select a cell in the data and choose Insert > Table, confirm the range and whether it has headers, then add a column for the item number. Tables expand when you type directly below them, and Excel generally copies a calculated-column formula into new table rows.

Assuming the table is named Table1, its header is in row 1, and the numbering formula is entered in the table’s number column, use =ROW()-ROW(Table1[#Headers]). It returns 1 in the first data row, 2 in the second, and continues based on each row’s position below the header. Table names can be checked or changed on the Table Design tab when a table cell is selected.

This method is convenient when the list is a structured dataset rather than a temporary range. Its numbers reflect current row positions, so sorting the table changes which item has each number. That is often right for a ranked display or current sequence, but it is not a permanent identifier. If an item needs an ID that stays attached to it after sorting, store a fixed value rather than relying on a position formula.

Sort the items as well as number them

A numbering formula does not sort a list. To alphabetize entries, Excel for Microsoft 365 can return a sorted copy with SORT. With names in A2:A20 and an empty output area beginning at C2, enter =SORT(A2:A20). Excel spills the names into column C in ascending order. The original list remains unchanged, and the output updates when the source range changes.

For a sorted list that also has consecutive numbers, use a dynamic-array formula. With names in A2:A20, at least one nonblank name, and an empty output area beginning at C2, enter =HSTACK(SEQUENCE(ROWS(FILTER(A2:A20,A2:A20<>""))),SORT(FILTER(A2:A20,A2:A20<>""))). The result spills into two columns: ascending numbers in the first and the nonblank names sorted A to Z in the second. For example, if the source contains Pears, Apples, and Plums, the output is 1 Apples, 2 Pears, and 3 Plums.

This combined formula uses FILTER to exclude blank cells, SORT to arrange the remaining values, SEQUENCE to generate matching numbers, and HSTACK to place the two results side by side. HSTACK is available in Microsoft 365 and Excel 2024; the formula is therefore suited to current Microsoft 365, not every older Excel release. Keep the spill area clear, and remember that sorting may differ from a manually curated order or a custom ranking.

Choose the right method for the job

The best formula depends on whether the numbers should track row positions, whether gaps are possible, and whether the list must sort itself. Use these guidelines:

  • Simple, contiguous list: ROWS is easy to fill down and works in many Excel versions.
  • Gaps in the source: IF with COUNTA can number only populated rows, provided blank cells are truly empty.
  • A fixed sequence of numbers: SEQUENCE spills a series without dragging.
  • A growing dataset: An Excel table can extend its calculated-column formula as new rows are added.
  • Alphabetical output: SORT rearranges a copy of the list; combine it with numbering only if you want both results.

There is an important distinction between a row number and an ID. Row-based formulas recalculate when records move, which is useful when the number means “current position.” They are unsuitable as permanent identifiers for orders, people, or inventory items if those records may be sorted or rearranged. Use a separately assigned value for identifiers that must remain stable.

Common problems and fixes

If every row displays 1, inspect the references. The starting cell should stay absolute, as in $A$2, while the ending reference should move as the formula is filled down. If both references are locked, each copied formula counts the same one-row range. If the first result is 0 or a negative number, check the offset or confirm that the formula starts on the intended row.

A #SPILL! error from SEQUENCE, SORT, FILTER, or HSTACK usually means that a cell in the output area is occupied or merged. Clear the obstructing cells and make sure the intended spill range is not inside an Excel table, where spilled-array behavior is restricted. If Excel shows the formula as text rather than calculating it, change the cell format from Text to General and re-enter the formula; also check that Show Formulas is not enabled.

If you see #NAME?, the Excel version may not support a newer function such as SEQUENCE, SORT, FILTER, or HSTACK, or the function name may be misspelled. The ROWS approach is a useful fallback for basic numbering. If a formula is accepted but the sort order seems wrong, check whether the source mixes numbers stored as text, extra spaces, or inconsistent spelling; those differences can affect sorting.

Frequently Asked Questions

What is the simplest formula to number rows in Excel?
With the first item in A2 and its number in B2, use =ROWS($A$2:A2), then fill down. It returns 1 in the first numbered row and increases by one for each row below.

Can Excel number a list automatically when I add new items?
Yes. Put the list in an Excel table and use a calculated-column formula such as =ROW()-ROW(Table1[#Headers]) in its number column. Excel generally extends the formula when new table rows are added. The exact table name in the formula must match your table.

How do I restart numbering after a blank row?
The ROWS formula does not restart at a blank; it counts row positions. If you want numbering to continue while skipping blank entries, use the IF-and-COUNTA formula described above. A true restart after each blank requires a different rule, because the formula must detect the most recent break in the sequence.

Does sorting change formula-generated numbers?
It can. A formula based on row position calculates from where the record currently sits, so sorting may assign different numbers to the items. Use fixed values if the number is meant to be a permanent ID rather than a current position.

Can I make an alphabetical list and number it at the same time?
Yes, in Microsoft 365 you can combine FILTER, SORT, SEQUENCE, and HSTACK to spill numbered, sorted results into two columns. The source list remains untouched, and the output updates when its contents change.

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.