Why does your sequence reset to 1 every time you insert a row? Why does =ROW()-1 break when you sort? Why does dragging the fill handle skip numbers after row 1048576? Because you’re using methods that look right—but aren’t built for how Excel actually calculates.
The Myth
Most people believe adding a sequence in Excel means either dragging the fill handle down or typing 1, 2, 3 and double-clicking the corner. They think it’s just about copying numbers—and that if it looks sequential on screen, it’s working.
It’s not. Dragging creates static values—not formulas. Sorting breaks them. Inserting rows shatters them. And yes, even =ROW() fails if your data starts in row 27 and you later paste over row 1.
The Reality
The only reliable way to add sequence in Excel is with a dynamic, position-agnostic formula that recalculates *every time*, regardless of sorting, filtering, or insertion. Not ROW(), not SEQUENCE() alone—and definitely not drag-and-drop.
| Symptom | Cause | Fix |
|---|---|---|
| Sequence restarts at 1 after inserting a row | Static numbers (no formula) | Replace with =SEQUENCE(ROWS(A2:A11)) in A2, then spill |
| Numbers shift incorrectly when sorting | Using =ROW()-1 without anchoring start | Use =SEQUENCE(ROWS(A2:A11),,1,1) anchored to data range |
| #SPILL! error appears mid-column | Adjacent cells contain data blocking dynamic array | Clear cells B2:B11 or use =INDEX(SEQUENCE(100),ROW()-1) for legacy compatibility |
| Sequence skips after filtering | Formula references full column (e.g., A:A) instead of filtered range | Use =SUBTOTAL(103,A$2:A2) inside a helper column |
Why the Myth Persists
YouTube tutorials from 2012 still rank high. Microsoft’s own Help article says “drag the fill handle” as step one—without warning that it’s a dead end for anything beyond a throwaway list. And Excel’s UI rewards speed over sustainability: double-clicking feels instant, so we assume it’s correct.
Even seasoned analysts fall for it. I once rebuilt a dashboard because someone had dragged sequences across 12 worksheets—and then inserted 3 rows in the middle of Q3. All downstream reports broke. Trust me, I learned this the hard way.
The Right Way
Here’s what works—tested across Excel 365, Excel 2019, and Excel Online:
- Type
=SEQUENCE(10)in cell A2. That gives you 1–10, spilling down automatically. - But better: anchor it to your actual data. If names are in B2:B11 (10 rows), type
=SEQUENCE(ROWS(B2:B11))in A2. - Press Ctrl+Enter—not Enter alone—to keep focus in A2 and avoid accidental navigation.
- To restart numbering per group (e.g., per department), use
=IF(B2<>B1,A1+1,1)in A2 and drag—but only if you can’t use dynamic arrays.
Wait—here’s the counterintuitive part: Don’t use =ROW()-1 unless your list literally starts in row 2 and will never move. Even then, if you copy-paste that formula into another sheet, it breaks. SEQUENCE() doesn’t care where it lives. ROW() does.
Sample dataset (A1:C11):
| Seq | Name | Amount |
|---|---|---|
| 1 | Sarah Chen | $45,200 |
| 2 | Diego Mendoza | $38,950 |
| 3 | Priya Patel | $52,100 |
| 4 | Marcus Lee | $41,320 |
| 5 | Anya Dubois | $49,780 |
| 6 | Rajiv Singh | $36,440 |
| 7 | Lena Kim | $55,600 |
| 8 | Tariq Hassan | $44,890 |
| 9 | Nina Torres | $47,210 |
| 10 | Eduardo Vega | $39,550 |
In A2, we used =SEQUENCE(ROWS(B2:B11)). Try inserting a row between Priya and Marcus—watch A5:A11 auto-update. No drag. No panic.
Proof It Works
Before (manual drag): 10 static numbers. After inserting row 5 → sequence breaks. After sorting by Amount → numbers stay fixed while names move.
| Action | Manual Drag Result | SEQUENCE() Result |
|---|---|---|
| Insert row at position 4 | A4 stays '4'; A5–A11 unchanged → gap at A5 | A4 becomes '4', A5 becomes '5', all auto-adjust |
| Sort descending by Amount | '1' stays with Sarah, even though she’s now row 10 | '1' moves with highest amount ($55,600 → Lena Kim) |
| Filter for Amount > $45,000 | All numbers remain visible, including '3' next to Priya (now hidden) | Only visible rows show 1–5; no gaps, no ghosts |
| Copy entire block to new sheet | Numbers copy as values—no formula logic retained | Formula copies intact; recalculates based on new range size |
Exceptions
Yes—there are two cases where dragging *is* acceptable:
- You’re building a one-time printout (e.g., invoice line numbers) and will never sort, filter, or edit again.
- You’re on Excel 2010 or earlier—no SEQUENCE(), no dynamic arrays. Then use
=ROW()-ROW($A$1)anchored to a fixed header row, and accept the fragility.
That’s it. Two narrow windows where the myth holds up. Everything else? Use SEQUENCE(). Or SUBTOTAL() for filtered lists. Or INDEX()+ROW() if you need backward compatibility and can’t upgrade.
Your next step: Open your most fragile spreadsheet right now. Find the first sequence column. Replace its contents with =SEQUENCE(ROWS(B2:B100)) (adjust B2:B100 to match your data). Press Alt+A, S, R to refresh any pivot tables relying on it. Done.
