How to find missing numbers in a sequence in Excel

List every missing number with FILTER and SEQUENCE

Column A is a cheque register: the cheques that have been entered from book 1041 to 1060. Auditors and bookkeepers want to know which numbers are not there, because a missing cheque is either unrecorded, voided or lost.

The formula works in three steps. SEQUENCE(MAX-MIN+1,,MIN) makes every whole number from the lowest cheque (1041) to the highest (1054). COUNTIF(A2:A10, that list) counts how many times each of those numbers appears in the register. FILTER keeps only the numbers whose count is 0. The result spills down, one missing number per row.

The repeated SEQUENCE is long. In Excel 365 you can name it once with LET: =LET(s,SEQUENCE(MAX(A2:A10)-MIN(A2:A10)+1,,MIN(A2:A10)),FILTER(s,COUNTIF(A2:A10,s)=0)) returns the same list. Add a third argument to FILTER, such as "No gaps", or a complete run shows a #CALC! error instead of a friendly message.

Catch missing numbers at the start or the end

MIN and MAX only see the numbers you have. If the first cheque in the book was never entered, or the last few are missing, the formula above cannot know, because the range starts and stops at what is there. When you know the full range, put it in input cells (F1 and F2 here: the book runs 1041 to 1060) and build the sequence from those instead.

Now the answer has 11 numbers: the five real gaps plus 1055 to 1060 at the end. Those six may simply be unused cheques still in the book, not missing ones. That is why it pays to decide which question you are asking: "gaps between the first and last entry" (use MIN and MAX) or "everything not accounted for in this book" (use the known range).

Count the missing numbers and the gaps

Often you only need a number for a checklist: how many are missing? Take the size of the full run and subtract how many numbers you have. Here that is 14 possible numbers minus 9 entered, so 5.

This shortcut assumes every number appears once. If a number was entered twice, COUNT includes both copies and the result is too low. In the invoice column, INV-0047 appears twice: counting all 9 entries says 3 invoices are missing, but counting unique ones with ROWS(UNIQUE(B2:B10)) gives the right answer, 4. When in doubt, count the FILTER result instead: =ROWS(FILTER(...)) or =COUNT(FILTER(...)).

To count the gaps (runs of missing numbers) rather than the numbers, compare each entry with the one above in a sorted list: =SUMPRODUCT(--(A3:A10-A2:A9>1)) returns 2, one gap after 1043 and one after 1049. This version needs the list sorted from smallest to largest.

Older Excel: flag the gaps in a helper column

Without SEQUENCE and FILTER (Excel 2019 and earlier), sort the numbers and flag each row where the jump from the row above is bigger than 1. The formula below goes in row 5, next to cheque 1046: the previous cheque is 1043, so 1044 and 1045 are missing.

The label reads "1044-1045" for a run, or a single number when only one is missing, and stays blank when there is no gap. Fill it down from row 3 to the last row. Row 9 shows 1050-1052. The & joins text and has lower priority than + and -, so A4+1 and A5-1 are worked out before they are joined.

1. Sort the numbers: select the column and use Data > Sort Smallest to Largest.
2. In the first empty column, row 3, enter =IF(A3-A2>1,A2+1&IF(A3-A2>2,"-"&A3-1,""),"") and fill it down to the last row.
3. Use Data > Filter on the helper column and untick (Blanks) to show only the rows with a gap.

IDs with a prefix such as INV-0042, and numbers stored as text

Invoice and ticket numbers often have a prefix and leading zeros, so they are text, and SEQUENCE cannot count through text. Strip the prefix with TEXTAFTER(B2:B10,"-"), which gives "0038", "0039" and so on, then turn those into numbers with VALUE. At the end, put the prefix back with "INV-"&TEXT(n,"0000") so the answer looks like the IDs you started with. TEXTAFTER is in Excel 365 only; elsewhere use VALUE(MID(B2,5,10)).

Two traps here. First, without VALUE nothing matches: "0038" is text and 38 is a number, and XMATCH (like MATCH and =) treats them as different. Leave VALUE out and every number from 38 to 49 comes back as missing. Second, COUNTIF only accepts a real cell range as its first argument, not a calculated list, so the formula checks with ISNA(XMATCH(s,n)) instead of COUNTIF(n,s)=0. Excel refuses the COUNTIF version with a "There's a problem with this formula" message.

The same text problem hits plain numbers imported from other systems. If cheque 1054 were stored as text, MAX would skip it and return 1053, and COUNT would say 8 while COUNTA says 9. Compare =COUNT(A2:A10) with =COUNTA(A2:A10): if they differ, some numbers are text. Convert them with Data > Text to Columns > Finish before looking for gaps.

Find missing dates in a daily log

Dates are whole numbers in Excel, so the same pattern finds days missing from a sales log, meter readings or a timesheet. Column C should have one row per working day in early October 2026. The plain formula (with C2:C10 in place of A2:A10) returns six dates: Sat 3, Sun 4, Wed 7, Sat 10, Sun 11 and Wed 14 Oct. Most of those are weekends, which nobody expected to log.

To check weekdays only, add a second condition with WEEKDAY(date,2)<6, which keeps Monday to Friday. Only the two real gaps are left. To just count them, =NETWORKDAYS(MIN(C2:C10),MAX(C2:C10))-COUNT(C2:C10) returns 2, and NETWORKDAYS takes a holiday range as a third argument.

If the log holds timestamps (1 Oct 2026 08:12) rather than plain dates, COUNTIF on a whole date finds nothing, because 08:12 is part of the value. Count each day as a window instead: COUNTIFS(C2:C10,">="&s,C2:C10,"<"&s+1)=0, and wrap MIN and MAX in INT so the sequence starts at midnight.