Analistable

Are any invoice numbers missing from the sequence?

By the Analistable team · Updated · 3 min read

Stack the invoice numbers from every file, generate the full sequence from the lowest to the highest number, and list the numbers that aren't present. In a spreadsheet: =FILTER(SEQUENCE(MAX(n) - MIN(n) + 1, 1, MIN(n)), COUNTIF(n, SEQUENCE(...)) = 0). In the example, 1004, 1007 and 1008 are missing.

Part of our guide: How to answer questions across multiple spreadsheets

The data

Invoice numbers in three monthly files
FileInvoice numbers
January1001, 1002, 1003
February1005, 1006
March1009, 1010

In a spreadsheet

=LET(n, VSTACK(Jan[Invoice], Feb[Invoice], Mar[Invoice]),
     all, SEQUENCE(MAX(n) - MIN(n) + 1, 1, MIN(n)),
     FILTER(all, COUNTIF(n, all) = 0, "No gaps"))

Invoice numbers with prefixes (INV-1004) need the number extracted first, for example with --TEXTAFTER([@Invoice], "-").

In SQL

WITH RECURSIVE seq(n) AS (
  SELECT MIN(invoice_no) FROM inv
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < (SELECT MAX(invoice_no) FROM inv)
)
SELECT n AS missing FROM seq
WHERE n NOT IN (SELECT invoice_no FROM inv);
Result
missing
1004
1007
1008

Explaining the gaps

  • Voided or cancelled invoices are usually kept with a void status; check whether the export excluded them.
  • Invoices raised in another system (a second shop, a manual book) using the same sequence.
  • Drafts that consumed a number without being issued.
  • Tax authorities in many countries expect gaps to be explained, so record the reason next to each.

Ask it in Analistable: “Which invoice numbers between the lowest and highest are missing across these files?”

Duplicates are the other half of the check

=FILTER(UNIQUE(n), COUNTIF(n, UNIQUE(n)) > 1, "No duplicates")

A number used twice is as much of a problem as a missing one — often an invoice raised in two systems or re-issued without voiding the original. Run both checks on the same stacked list.

In Google Sheets

=LET(n, VSTACK(Jan!A2:A, Feb!A2:A, Mar!A2:A), m, FILTER(n, n <> ""),
     all, SEQUENCE(MAX(m) - MIN(m) + 1, 1, MIN(m)),
     FILTER(all, COUNTIF(m, all) = 0))

The extra FILTER removes the blanks that open-ended ranges bring in, which would otherwise make MIN return 0.

Show gaps as ranges

SELECT invoice_no + 1 AS gap_start, next_no - 1 AS gap_end
FROM (SELECT invoice_no,
             LEAD(invoice_no) OVER (ORDER BY invoice_no) AS next_no
      FROM inv)
WHERE next_no > invoice_no + 1;
Result
gap_startgap_end
10041004
10071008

With long sequences, a list of every missing number gets unwieldy; ranges are easier to explain. Three numbers are missing out of the range 1001–1010, and seven are present: 7 + 3 = 10 checks the result.

Excel 2019 and older

Without SEQUENCE, stack the numbers in column A and sort ascending. In B2 enter =IF(A3 - A2 > 1, A2 + 1 & "–" & A3 - 1, "") and fill down: each non-empty cell is a gap range, the same result as the LEAD query. Sort first, or the differences are meaningless.

Several sequences in one file

  • Credit notes, pro formas and invoices often have their own series (CN-, PF-, INV-). Extract the prefix into its own column and check each series separately — in SQL, add PARTITION BY series to the LEAD window.
  • Sequences that restart each year (2026-0001) need the year as part of the series, or the end of one year looks like a huge gap.
  • Check the first and last number against the previous period's file too: a gap between files is still a gap.

Troubleshooting

Common problems
SymptomCauseFix
Huge list of missing numbersOne mistyped number far outside the rangeCheck MIN and MAX before generating the sequence
No gaps found but you know one existsNumbers stored as textConvert with VALUE or --
Gaps at the startPrevious year's numbers includedFilter to one period or series

For explanation and audit, keep a log of each gap and its reason with the monthly file. Stacking files first: combining Excel files into one.

Frequently asked questions

How do I find missing numbers in a sequence in Excel?
Generate the full range with SEQUENCE and FILTER it to the numbers whose COUNTIF in your list is zero.
What if invoice numbers have a prefix?
Extract the numeric part with TEXTAFTER or RIGHT and convert it to a number before checking.
Can this check several files at once?
Yes — stack the numbers from all files with VSTACK (or UNION ALL in SQL) before generating the sequence.
How do I show gaps as ranges?
Sort the numbers and compare each with the next one: LEAD() in SQL, or a helper column in Excel. A difference above 1 is a gap.
What about separate series for credit notes?
Check each series on its own, using the prefix as a partition.

Related guides