Stop Typing Numbers Manually — Try This Instead

The first thing most people do when they need sequential numbers is type 1 in A1, 2 in A2, highlight both, and drag the fill handle down. That works — until you insert a row, delete one, or copy-paste elsewhere. Then your numbers break, jump, or duplicate. And yes, I’ve rebuilt broken sequences for clients three times in one afternoon (trust me, I learned this the hard way).

The Problem

You’re building a purchase order log. Column A should be a clean, unbroken sequence: 1, 2, 3… matching each row of data. But your sheet looks like this — rows inserted mid-list, filters applied, blank lines left behind. Excel doesn’t auto-adjust manual numbers. So you get gaps, repeats, or misaligned IDs that mess up VLOOKUPs and pivot tables.

Row	PO Number	Vendor	Amount
1	1	Acme Corp	$4,250
2	2	Nexus Logistics	$1,890
3	3	Stellar Labs	$7,320
4	5	Brightline Inc	$2,110
5	6	Acme Corp	$5,400
6	6	Veridian Systems	$3,760
7	8	Nexus Logistics	$1,240

Here’s what’s really happening:

Symptom	Cause	Fix
Gap between 3 and 5	Row was deleted but number wasn’t updated	Use ROW() or SEQUENCE(), not manual entry
Duplicate '6'	Copy-paste overwritten original; no auto-increment	Avoid pasting values into numbered columns
Missing row 4, 7	Filter hid rows while dragging fill handle	Always unfilter before filling, or use formula-based numbering

The Solution

We’ll fix this using ROW() — simple, stable, and filter-safe. It returns the actual row number, so inserting or deleting rows keeps everything aligned.

In cell A2 (not A1 — leave A1 for header), type =ROW()-1. That gives you 1 in A2 because ROW() returns 2, minus 1 = 1.
Press Enter, then click A2 again. Hover over the bottom-right corner until the cursor turns to a thin black cross (+), then double-click. Excel auto-fills down to the last adjacent non-blank cell in column B — say, B12. So A2:A12 now shows 1 through 11.
To make it dynamic across filters, replace =ROW()-1 with =SUBTOTAL(103,$B$2:B2)-1. That counts only visible rows above — try filtering for 'Acme Corp' and watch the numbers renumber themselves.

Now your list looks clean and resilient:

Row	PO Number	Vendor	Amount
1	Header	Header	Header
2	1	Acme Corp	$4,250
3	2	Nexus Logistics	$1,890
4	3	Stellar Labs	$7,320
5	4	Brightline Inc	$2,110
6	5	Acme Corp	$5,400
7	6	Veridian Systems	$3,760
8	7	Nexus Logistics	$1,240

Pro tip: If your data starts at row 5, change =ROW()-1 to =ROW()-4. No magic — just subtract the row number *above* your first data row.

Going Further

You don’t always want 1, 2, 3. Sometimes you need PO-001, INV-2024-001, or numbers that restart per category.

Custom prefix + number: In A2, use ="PO-"&TEXT(ROW()-1,"000") → PO-001, PO-002.
Restart per group: If column C contains departments, use =IF(C2=C1,A1+1,1) in A2 — but lock it with $C$2:C2 and use COUNTIFS instead for safety: =COUNTIFS($C$2:C2,C2).
SEQUENCE() for blocks: Need 100 numbers fast? Type =SEQUENCE(100) in A1 — it spills 100 rows automatically. Works in Excel 365/2021 only.
Non-contiguous lists: If your data lives in A2, A5, A8… use =INT((ROW()-2)/3)+1 to number every third row.

Surprising tip: =ROW(A2:A100)-ROW(A2)+1 creates an array of 1–99 — useful inside SUMPRODUCT or FILTER without spilling.

When NOT to Use This

Sequential numbering isn’t always the right tool — especially when:

You’re building an audit trail where numbers must be immutable. ROW() changes if rows move. For true permanence, paste values after generating, or use a database.
Your sheet has merged cells in column A. ROW() still works, but fill handle dragging fails. Use formulas exclusively.
You’re sharing with Excel 2016 or earlier users — SEQUENCE() and dynamic arrays won’t calculate. Stick with ROW() or SUBTOTAL().
You need numbers to reflect business logic (e.g., ‘order # by date’). Then sort first, then number — or use RANK.XYZ with tie-breakers.

If you’re exporting to PDF or printing, check that your formula-based numbers survive — sometimes page breaks cut off spilled SEQUENCE() results. Paste as values before finalizing.

Keyboard Shortcuts

These save real time when setting up or troubleshooting numbering:

Action	Shortcut (Windows)	Notes
Fill down formula from active cell	`Ctrl+D`	Faster than double-clicking fill handle
Select current data region	`Ctrl+A` (twice)	First Ctrl+A selects used range; second extends to full block
Open Go To dialog (to jump to last cell)	`F5` → `Ctrl+End`	Jump to bottom-right used cell fast
Toggle formula view	Ctrl+` (backtick)	See all formulas at once — critical for debugging numbering logic
Insert new row above active cell	`Ctrl+Shift++`	Preserves ROW()-based sequences cleanly