How to number rows in Excel automatically: fill handle, ROW and SEQUENCE

Summary. To number rows in Excel, type 1 in the first cell and 2 in the cell below, select both and drag the fill handle (the small square at the lower-right corner) down; Excel continues 3, 4, 5. Excel has no button that numbers rows for you, and numbers made with the fill handle are typed values that do not update when you add, move or delete rows. For numbers that keep their order, enter =ROW()-1 in A2 under a header in row 1 and fill it down, or put the data in an Excel table so rows added at the end are numbered in sequence. In Excel for Microsoft 365, Excel 2024 and Excel 2021, one formula numbers the whole column: =SEQUENCE(10) spills 1 to 10. To number only the rows a filter leaves visible, use =SUBTOTAL(103,$B$2:B2). SEQUENCE is not in Excel 2019 or 2016, which reached end of support on 14 October 2025; Excel 2021 followed on 13 October 2026. Excel's own row headings print only if you turn them on from the Page Layout tab.

About this article. For anyone who keeps lists or registers in Excel, and for IT teams supporting mixed Excel versions. Every step, label, shortcut and function detail was checked on 15 November 2026 against Microsoft Support's Excel articles and Microsoft Learn's release notes and lifecycle pages. Labels can differ slightly between versions and update channels; no vendor paid for inclusion. Researched with AI assistance and reviewed by our editorial team.

Which numbering method should you use?

The steps apply to Excel for Microsoft 365 on Windows (Current Channel Version 2610, Build 20527.20196, released 10 November 2026) and on Mac (Version 16.115, released the same day), Excel 2024 and, except where noted, Excel for the web. Older versions run them too, apart from SEQUENCE. Version coverage comes from each function's Microsoft page; Microsoft says most functions behave the same in Excel for the web.

Worked example: a five-row task list

Put headers in A1 (No.) and B1 (Task), then five tasks in B2:B6. Each column shows what one method returns in A2:A6; the last assumes the Draft brief row is filtered out.

Now delete row 4. The fill-handle column reads 1, 2, 4, 5, with a gap. The =ROW()-1 column recalculates to 1, 2, 3, 4, because each cell returns its own row number minus one. The SEQUENCE column shrinks to 1 to 4 by itself, because COUNTA now finds four non-empty cells.

Method 1: number rows with the fill handle

This works on Windows, Mac and the web.

1. Type 1 in the first cell, for example A2.

1. Type 2 in the next cell to set the pattern. For 2, 4, 6, type 2 and 4.

1. Select both cells.

1. Drag the fill handle down over the rows you want to number. Drag up or left to fill in decreasing order.

If you type only 1 and drag, Excel repeats 1 in every cell. Select the Auto Fill Options button that appears when you finish dragging and choose Fill Series to switch to 1, 2, 3. On Windows you can also drag with the right mouse button held down and pick a pattern from the menu that appears.

Cannot see the fill handle? On Windows, select File > Options > Advanced and, under Editing options, tick Enable fill handle and cell drag-and-drop. Microsoft also suggests keeping Alert before overwriting cells ticked so a drag does not replace existing data.

Excel for the web: drag the fill handle down or right, as on the desktop. Ctrl+D fills the top cell down the selection and Ctrl+R fills right, on Windows too.

Fill Series for long ranges (Windows and Mac)

For thousands of rows, the Series dialog is quicker than dragging:

1. Type 1 in A2 and select A2.

1. On the Home tab, select Fill > Series. On Windows it sits in the Editing group.

1. Choose Columns to fill down the sheet, and set Type to Linear.

1. Enter 1 in the Step value box and your last number, for example 5000, in the Stop value box, then select OK.

You can also select A2 and the cells below it first; Microsoft's sequential dates guide uses that route and covers the Mac versions.

Method 2: number rows with the ROW function

ROW returns the row number of a reference; with no reference, it returns the row the formula sits in. Microsoft's numbering guide uses =ROW(A1) in the first cell, which returns 1, and fills it down. For a list with a header in row 1 we prefer =ROW()-1: it returns 1 in row 2, 2 in row 3, and so on. If the header is lower, subtract its row number instead, for example =ROW()-4 for a header in row 4.

The difference matters: =ROW(A1) and its copies point at other cells, so deleting a row leaves the formula below it pointing at a deleted cell, showing #REF!. =ROW()-1 points at nothing and simply recalculates.

Microsoft says ROW numbers update when you sort them with your data, but the sequence can still break when you add, move or delete rows. Its fix is an Excel table, where rows added at the end are numbered in sequence. Press Ctrl+T (or Ctrl+L) on Windows, or Cmd+T on a Mac, to create one, then enter =ROW()-1 in the first data cell of the number column. Excel fills the formula into the whole column as a calculated column. Our guide to naming a table in Excel covers the next step.

Formatted IDs. For codes with leading zeros, wrap ROW in TEXT. Microsoft's example on the first line returns 000-001, 000-002 and onward; the second line returns INV-0001 in row 2.

            =TEXT(ROW(A1),"000-000")
="INV-"&TEXT(ROW()-1,"0000")
          

Method 3: number rows with SEQUENCE

SEQUENCE generates a list of sequential numbers from one formula. The syntax is =SEQUENCE(rows,[columns],[start],[step]), and any optional argument you leave out defaults to 1.

• =SEQUENCE(10) spills 1 to 10 down the column.

• =SEQUENCE(COUNTA(B2:B1000)) numbers as many rows as column B has entries, and grows or shrinks as you add or delete tasks. COUNTA counts non-empty cells, so the list must have no gaps.

• =SEQUENCE(5,1,1001,1000) returns 1001, 2001, 3001, 4001 and 5001, Microsoft's own example for general ledger codes.

• =SEQUENCE(1,12) numbers across a row, 1 to 12, for month columns.

• The formula below returns INV-0001 to INV-0005.

            ="INV-"&TEXT(SEQUENCE(5),"0000")
          

Type the formula in the top cell only. Excel sizes the result, draws a border round the spill range when you select a cell in it, and lets you edit only the first cell.

Two points from Microsoft's dynamic array guidance:

• #SPILL! means something is in the way. Clear the cells the result needs and it spills normally.

• Not inside tables. Spilled array formulas are not supported in Excel tables, so put SEQUENCE in a normal range beside the table, or use the ROW method inside it.

Our own tip: leave the SEQUENCE column out when you sort; it keeps showing 1 to n because it numbers positions, not records.

Which versions have SEQUENCE? Microsoft lists Excel for Microsoft 365, Excel 2024 and Excel 2021 on Windows and Mac, plus Excel for iPad, iPhone and Android. It arrived in Excel 2021 for Windows as one of six new dynamic array functions, so Excel 2019 and 2016 do not have it. The SEQUENCE page does not name Excel for the web, although the UNIQUE page lists it as Microsoft365.com and SEQUENCE is not among the functions Microsoft says behave differently in a browser. Test it in the browser before building a shared web workbook around it, and use ROW if in doubt.

Number only visible rows, or skip blanks

After filtering. Before you filter with Data > Filter, fill =SUBTOTAL(103,$B$2:B2) down column A. Function number 103 is COUNTA that ignores hidden rows, and SUBTOTAL ignores rows excluded by a filter whatever function number you use. The visible rows read 1, 2, 3 with no gaps, which helps when you print or share a filtered view.

Skip blank rows. Fill the formula below down column A. Rows with no task stay blank and the count picks up again at the next entry.

            =IF(B2="","",COUNTA($B$2:B2))
          

Print row numbers instead of adding a column

Excel already numbers every row in the headings down the left edge, but by default it does not print them. In Excel for Windows, select the Page Layout tab and, in the Sheet Options group under Headings, tick Print. Press Ctrl+P to open Print and check the result. That page still gives Ctrl+F2 for preview, but Microsoft's current shortcut list assigns Ctrl+F2 on Windows to editing the active cell in the formula bar.

Common problems

• Numbers out of order after a sort or delete: they are typed values from the fill handle. Replace them with =ROW()-1.

• Formulas show old numbers: workbook calculation may be set to manual. Select File > Options > Formulas and, under Calculation options, set Workbook Calculation to Automatic.

• SEQUENCE not available: Excel 2019 and 2016 do not include it. Use ROW instead.

For IT teams and businesses

Row numbers and record IDs are different things. A ROW or SEQUENCE number describes a position and changes when someone sorts or deletes a row; asset registers, ticket logs and invoices need an identifier that never changes. Generate IDs once with any method above, then copy the column and paste it back as values. Where a number must be created for each new record, Microsoft suggests moving the data into an Access database, which can generate a unique number automatically.

In a mixed Excel estate, prefer ROW-based formulas in shared workbooks: they work in every version, while SEQUENCE is missing for anyone still on Excel 2019 or 2016. Pair numbering with input controls: drop-down lists built with Data Validation keep entries consistent, and a duplicate check confirms no ID appears twice before the data moves into another system. On long registers, freeze the header row so column titles stay in view.

How eCorpIT can help

eCorpIT is a Gurugram-based technology organisation, founded in 2021, assessed at CMMI Level 5 and MSME certified, with senior-led engineering teams working across AWS, Microsoft and Google platforms. Our data and analytics services team moves fragile spreadsheet registers into structured data with stable IDs, validation and reporting. Talk to us at /contact-us/.

Last updated: 15 November 2026.

References

1. Automatically number rows in Excel — Microsoft Support

1. Create a list of sequential dates — Microsoft Support

1. ROW function — Microsoft Support

1. SEQUENCE function — Microsoft Support

1. SUBTOTAL function — Microsoft Support

1. Ending support in 2025 — Microsoft Lifecycle, Microsoft Learn

1. Ending support in 2026 — Microsoft Lifecycle, Microsoft Learn

1. Print row and column headings — Microsoft Support

1. Release notes for Current Channel releases — Microsoft Learn

1. Release notes for Office for Mac — Microsoft Learn

1. Differences between using a workbook in the browser and in Excel — Microsoft Support

1. COUNTA function — Microsoft Support

1. Enter a series of numbers, dates, or other items (Excel for Mac) — Microsoft Support

1. Fill a formula down into adjacent cells (Windows and Web tabs) — Microsoft Support

1. Fill data automatically in worksheet cells — Microsoft Support

1. Keyboard shortcuts in Excel (Windows, macOS and Web tabs) — Microsoft Support

1. Project values in a series — Microsoft Support

1. Use calculated columns in an Excel table — Microsoft Support

1. TEXT function — Microsoft Support

1. Dynamic array formulas and spilled array behavior — Microsoft Support

1. What's new in Excel 2021 for Windows — Microsoft Support

1. Excel functions (alphabetical) — Microsoft Support

1. UNIQUE function — Microsoft Support

1. Filter data in a range or table in Excel — Microsoft Support