Yes, you can generate sequential numbers in Excel. But if you’re still dragging the fill handle or typing =ROW()-1 into column A, your numbering will vanish the moment someone inserts a row or filters the list.
The Setup
You’re auditing purchase orders for Alibaba’s logistics vendor partners. Your raw data lives in columns A–D, starting at A1. It’s messy: no ID column, inconsistent spacing, and rows get added weekly. You need a stable, auto-updating sequence—starting at 101—for tracking.
| Vendor Name | Order Date | Amount | Status |
|---|---|---|---|
| BlueSky Logistics | 2024-02-17 | $12,450 | Shipped |
| Nexus Freight Co. | 2024-02-18 | $8,920 | Pending |
| Acme Corp Logistics | 2024-02-19 | $15,600 | Shipped |
| Veridian Supply Chain | 2024-02-20 | $22,100 | In Transit |
| Orion Distribution | 2024-02-21 | $7,340 | Shipped |
| TerraLink Express | 2024-02-22 | $14,800 | Pending |
| Stellar Haul Ltd. | 2024-02-23 | $9,750 | Shipped |
| Zenith Forwarding | 2024-02-24 | $18,200 | In Transit |
This is our source range: A1:D8. We’ll add sequential IDs starting at 101 in column E.
The Challenge
You need numbers that behave like real IDs—not just labels. They must:
- Stay attached to each row when sorted (e.g., sorting by Status shouldn’t scramble the IDs)
- Auto-adjust when new rows are inserted above or between existing ones
- Remain visible and correct even when filtered (no blank or duplicated IDs)
Here’s the catch: Excel doesn’t have a native ‘stable row index’ function. So we build one—using structured references and a tiny helper trick.
Walking Through It
We’ll create a spill-range formula in cell E1. No copy-paste. No dragging. Just one entry that governs the whole column.
Step 1: Click E1. Type this exactly:=SEQUENCE(ROWS(A2:A1000),,101)
Press Enter.
That gives you 101, 102, 103… down column E—but only as far as there’s data in column A. Wait, how does it know? Because ROWS(A2:A1000) counts non-blank cells in that range *as it spills*. But that’s fragile if A1000 is empty. So we improve it.
Step 2 (the counterintuitive part): Replace that with:=SEQUENCE(COUNTA(A2:A1000),,101)
Why COUNTA instead of ROWS? Because ROWS always returns 999 here—fixed length. COUNTA counts actual entries. Much safer.
Step 3 (critical fix): Select E1 again. Press Alt + H + V + U — that’s Paste Special → Values. No, wait—don’t do that yet. (trust me, I learned this the hard way). If you paste values now, you lose dynamism. Instead, convert the range to an Excel Table first.
Select A1:D8 → press Ctrl + T → check “My table has headers” → OK. Now rename the table: click inside it → Table Design tab → rename to tblOrders.
Final step: In E1, type:=SEQUENCE(ROWS(tblOrders),,101)
Press Enter.
Now column E spills automatically—and stays locked to the table. Insert a row anywhere in tblOrders? The sequence updates instantly. Filter on Status = "Pending"? Column E stays intact. Sort by Amount? IDs move with their rows.
Before:
| Vendor Name | Order Date | Amount | Status | ID |
|---|---|---|---|---|
| BlueSky Logistics | 2024-02-17 | $12,450 | Shipped | blank |
| Nexus Freight Co. | 2024-02-18 | $8,920 | Pending | blank |
After formula in E1:
| Vendor Name | Order Date | Amount | Status | ID |
|---|---|---|---|---|
| BlueSky Logistics | 2024-02-17 | $12,450 | Shipped | 101 |
| Nexus Freight Co. | 2024-02-18 | $8,920 | Pending | 102 |
| Acme Corp Logistics | 2024-02-19 | $15,600 | Shipped | 103 |
The Result
Here’s what column E looks like after applying =SEQUENCE(ROWS(tblOrders),,101) — fully dynamic, table-aware, and robust:
| Vendor Name | Order Date | Amount | Status | ID |
|---|---|---|---|---|
| BlueSky Logistics | 2024-02-17 | $12,450 | Shipped | 101 |
| Nexus Freight Co. | 2024-02-18 | $8,920 | Pending | 102 |
| Acme Corp Logistics | 2024-02-19 | $15,600 | Shipped | 103 |
| Veridian Supply Chain | 2024-02-20 | $22,100 | In Transit | 104 |
| Orion Distribution | 2024-02-21 | $7,340 | Shipped | 105 |
| TerraLink Express | 2024-02-22 | $14,800 | Pending | 106 |
| Stellar Haul Ltd. | 2024-02-23 | $9,750 | Shipped | 107 |
| Zenith Forwarding | 2024-02-24 | $18,200 | In Transit | 108 |
What Could Go Wrong
Mistake #1: Using =ROW() without anchoring
You type =ROW() in E2, then drag down. Then someone inserts a row above row 2. Every ID shifts +1 — but the first row becomes 1, breaking your 101-start rule. Worse: filter hides rows, but =ROW() keeps counting them. You get gaps like 101, 102, 104, 105.
Mistake #2: Forgetting to convert to a Table before using SEQUENCE
If you skip Ctrl+T and just use =SEQUENCE(ROWS(A2:A100),,101), inserting a new row *outside* A2:A100 won’t expand the range. Your sequence stops at row 100 — and new rows get no ID. It looks fine until week 3.
Mistake #3: Starting the sequence at 1 inside a table that already has headers
You type =SEQUENCE(ROWS(tblOrders),,1) in E1 — but since tblOrders includes the header row, ROWS() returns 9, not 8. Your IDs become 1–9, with ID=1 stuck in the header row. Visually jarring and breaks VLOOKUPs downstream.
| Method | Time for 10K rows | Accuracy | Difficulty |
|---|---|---|---|
| Drag-fill handle | ~12 sec | ❌ Fragile | Easy |
| =ROW()-1 (unanchored) | ~2 sec | ❌ Breaks on sort/filter | Easy |
| =SUBTOTAL(103,$A$2:A2) | ~5 sec | ⚠️ Only visible rows | Medium |
| =SEQUENCE(ROWS(tblOrders),,101) | ~1 sec (one-time) | ✅ Fully stable | Medium |
