How to Find Missing Numbers in a Sequence in Excel
A run of invoice, cheque, PO, or ID numbers should be unbroken — a gap can mean a lost document, a skipped entry, or worse. Spotting which numbers are missing between the first and last is a classic audit check. Here's how to do it without scrolling.
Last updated: September 2026
| Have | Missing | |
|---|---|---|
| 1001, 1002, 1004, 1005 | → | 1003 |
| INV-001, INV-003, INV-004 | → | INV-002 |
Method 1 — a helper formula
- Down a spare column, list every number in the expected run — e.g.
=MIN($A:$A)+ROW()-1. - Flag the ones not present:
=IF(ISNA(MATCH(C2,$A:$A,0)), C2, ""). - It works for plain integers, but breaks the moment the numbers carry a prefix or leading zeros — those are text, and MATCH won't line them up.
Method 2 — a 365 dynamic formula
On Excel 365 you can do it in one cell: =FILTER(SEQUENCE(MAX(A:A)-MIN(A:A)+1, 1, MIN(A:A)), ISNA(MATCH(SEQUENCE(...), A:A, 0))). Compact, but only on the newest Excel — and still no help with INV-0007-style IDs.
The 1-click way — Tellsheet
Gap Finder audits a column of sequence numbers and lists every missing value on a new sheet. It figures out the run automatically — including a shared text prefix plus a number, so INV-1001 and INV-1004 are the same sequence — and rebuilds the missing entries with the same prefix and zero-padding (INV-1002, INV-1003). Handles several prefixes at once, and tells you plainly when a run is complete. Pairs with Add Serial Numbers and Highlight Duplicates.
Frequently asked questions
How do I find missing numbers in a sequence?
A SEQUENCE + MATCH formula on 365, or Tellsheet's Gap Finder in one click.
How do I find gaps in invoice numbers?
Gap Finder treats prefixed IDs as one run and lists the missing ones rebuilt with the prefix.
Does it handle leading zeros?
Yes — it keeps the observed pad width, so INV-002 comes back correctly.
What if there are no gaps?
It says the sequence is complete rather than leaving you guessing.
Related Excel guides
Catch a missing invoice in one click
Gap Finder lists every number missing from a run — prefixes and leading zeros included.
Get Tellsheet free See pricing