To list missing integers in an Excel sequence, use a dynamic-array formula that creates every number between the smallest and largest values, then filters out numbers already present. With integer values in A2:A100, enter this formula in an empty cell; it spills the missing numbers below it:
=LET(x,UNIQUE(FILTER(A2:A100,ISNUMBER(A2:A100))),s,SEQUENCE(MAX(x)-MIN(x)+1,,MIN(x)),FILTER(s,ISNA(XMATCH(s,x)),"No gaps"))
This works in Microsoft 365 and Excel 2021 or later. It ignores blank cells and nonnumeric entries, handles duplicates, and returns “No gaps” if every integer in the observed range is present. The examples below explain how to adjust the range, check for gaps outside the observed minimum and maximum, and use a fill-down alternative.
What Counts as a Missing Number?
A missing number is a value expected in a sequence but absent from the data. For example, if a column contains 1, 2, 4, 7, the missing integers between the lowest and highest values are 3, 5, and 6. Excel cannot know the intended range from the list alone: it can identify gaps between 1 and 7, but it cannot infer that the sequence was supposed to start at 0 or end at 10.
That distinction matters for invoice numbers, inventory IDs, attendance dates, and other records. A gap may be an error, but it may also be intentional: a canceled invoice, retired product code, or skipped weekend can create a number or date that should not appear in the final list. Decide what the valid sequence is before treating every gap as a problem.
How the Dynamic Formula Works
The formula uses several functions available in Microsoft 365 and Excel 2021 or later. LET assigns names to intermediate results, so the formula is easier to read than a repeated nest of calculations. UNIQUE removes duplicate entries, and FILTER excludes blanks and text by keeping only numeric cells. SEQUENCE generates consecutive integers from the smallest observed number through the largest.
XMATCH checks each generated number against the cleaned list. Numbers that are not found produce a #N/A result; ISNA marks those results, and the final FILTER returns only the missing values. If the check finds none, FILTER displays the text “No gaps” rather than an error. The formula expects whole numbers. If the source contains decimals, the generated sequence still consists of integers, and a decimal is not treated as a member of that integer sequence.
Enter the formula in a cell with enough clear space beneath it. The result is a spill range: Excel fills the necessary cells automatically. If cells in the output area contain data, Excel may show #SPILL!; clear those cells or move the formula. Do not copy the formula down one row at a time unless you specifically want separate formulas rather than a dynamic list.
Include an Expected Start and End
The first formula checks only from the smallest number in the data to the largest. To include missing numbers before the first observed value or after the last one, set explicit boundaries. Put the expected starting integer in D1 and the expected ending integer in D2. With the source list still in A2:A100, enter this formula in an empty cell:
=LET(x,UNIQUE(FILTER(A2:A100,ISNUMBER(A2:A100))),s,SEQUENCE($D$2-$D$1+1,,$D$1),FILTER(s,ISNA(XMATCH(s,x)),"No gaps"))
For example, if D1 contains 1 and D2 contains 10, the formula checks every integer from 1 through 10 even if the list starts at 2 or stops at 9. The start must be less than or equal to the end, and both boundary cells should contain integers. This is the right choice when a numbering policy defines the valid range independently of the values currently recorded.
Use a Fill-Down Method in Older Excel
If your Excel version does not support dynamic arrays or the functions in the first formula, a helper column provides a broadly compatible approach. It creates the candidate sequence one row at a time and marks which candidates are absent. With the source numbers in A2:A10, enter this formula in D2 to set the first candidate:
=MIN(A2:A10)
With the source numbers still in A2:A10 and the first candidate in D2, enter this formula in D3, then fill it down until the candidate reaches the largest source value:
=D2+1
With the source values in A2:A10 and the candidate number in D2, enter this formula in E2 and fill it down beside the candidates:
=IF(COUNTIF($A$2:$A$10,D2)=0,D2,"")
Column E displays a number when it is missing and a blank when it exists. For example, if the source is 1, 2, 4, 7, the candidate column runs from 1 to 7 and the result column shows 3, 5, and 6, with blanks for 1, 2, 4, and 7. You can filter the result column to show nonblank cells, or copy and paste the results as values into a separate list. This method is less compact, but its steps are visible and easy to audit.
Check Sorting, Duplicates, and Data Quality
The dynamic formula does not require the source list to be sorted. UNIQUE removes duplicate numbers, and XMATCH searches the resulting list regardless of its order. The fill-down gap-check formula also works with unsorted values because COUNTIF checks the entire source range. Sorting can still make the original data easier for a person to inspect, but it is not required by these approaches.
Before investigating a reported gap, check whether the values are really numeric. Numbers imported from a text file may be stored as text, and cells can also contain spaces or other characters. The main formula ignores text rather than counting it as a number. That can be useful for a mixed column, but it can also make a text-formatted value look missing. Use Excel’s error indicators or convert the source to numbers when the list is meant to be numeric.
- Duplicates: Repeated entries do not fill a gap. If 4 appears twice but 3 is absent, 3 is still missing.
- Blanks: Empty cells are excluded from the modern formula’s source list.
- Negative integers: They can be checked too; the generated sequence runs from the lowest value to the highest.
- Decimals: Decide whether the expected sequence should use whole numbers or a fixed increment before applying an integer-based check.
Dates and Other Sequences
Excel stores dates as serial numbers, so a date list can sometimes be checked using the same idea. For a sequence expected to include every calendar day, set the expected first and last dates as the boundaries and generate each serial value between them. Format the output cells as dates to display them as dates rather than serial numbers. This checks calendar days, however, so it will report weekends and holidays if they are absent.
For business-day schedules, a simple every-integer check is usually the wrong test. The expected list might skip weekends, public holidays, or organization-specific closure dates. In that case, create a separate column containing the expected working dates, then compare that list with the actual dates. The same principle applies to product codes that follow a pattern rather than a fully consecutive numbering scheme: define the expected values first, then look for expected values not found in the records.
Choosing the Right Approach
Use the dynamic formula when you want a compact, automatically updating list and have Microsoft 365 or Excel 2021 or later. Add explicit start and end cells when the valid range is defined by a rule or business process, not just by the current data. Choose the helper-column approach when you need compatibility with older Excel, want to see each candidate number, or prefer formulas that are easy to trace one row at a time.
On Excel for the web, dynamic-array formulas are available, though the ribbon and interface may look different from Windows desktop Excel. On Mac, the formulas work in supported Excel versions as well; keyboard shortcuts and some interface details can differ. The formulas use commas as argument separators, as required by the stated Excel setup. Some regional settings use semicolons instead, so Excel may require separators that match the computer’s regional configuration.
Common Errors and Fixes
- #SPILL!: Move the formula or clear cells in its output area so Excel can display the returned list.
- #CALC! or an unexpected result: Check that the source includes at least one numeric value and that the chosen boundaries make sense.
- A number appears to be missing when it is present: Check for text-formatted numbers, extra characters, or a source range that does not include the relevant row.
- Numbers at the beginning or end are not reported: Use the explicit-boundary formula; a minimum-to-maximum check cannot identify values beyond the observed endpoints.
- The output does not update as expected: Confirm that new records are inside the formula’s source range. A fixed range such as A2:A100 does not automatically extend beyond row 100.
For a growing dataset, you can use a larger source range or convert the source into an Excel table and adapt the formula to the table’s column. If you use a fixed range, leave a sensible allowance for future rows and check it when the dataset expands. A formula can only compare the cells it has been told to examine.
Frequently Asked Questions
How do I find missing numbers between two values in Excel?
Use the explicit-boundary formula and enter the lower and upper limits in D1 and D2. It generates every integer in that range and returns those not found in A2:A100.
Can Excel find more than one missing number?
Yes. The dynamic formula returns every missing integer as a spilled list. The fill-down method also identifies multiple gaps, but displays them alongside the full candidate sequence.
Does the formula work if my list is not sorted?
Yes. The modern formula uses UNIQUE and XMATCH, and the helper-column method uses COUNTIF across the whole source range. Sorting is optional.
Why does Excel say there are missing numbers when I can see them?
The values may be stored as text, may contain spaces, or may be outside the formula’s selected range. Confirm that the values are numeric and that the source range includes them.
Can I use this to find missing dates?
Yes, for a sequence expected to contain every calendar day, use date boundaries and format the generated results as dates. For working days or holiday-aware schedules, compare against a list of expected dates instead.
Related
- How to Undo in Excel With a Keyboard Shortcut on Windows
- How to Undo in Excel After Saving Your Workbook Changes
- Undo in Excel: Keyboard Shortcut and How to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Filter Shortcut Keys Not Working? Fixes to TryIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off. If nothing happens,…
- Error Loading Add-Ins in Excel: Causes, Checks, and FixesIf Excel says “Error loading add-ins,” first identify the add-in type, then disable it in Excel and restart:…
- Excel Not Giving Correct Sum? Find and Fix the CauseIf Excel is not giving the correct sum, first check that the formula includes the intended cells, that…
- Excel YEAR Function Not Working? Fix Dates and ResultsIf Excel’s year-today formula is not working, enter =YEAR(TODAY()) in a cell formatted as General or Number; it…
- VLOOKUP Not Working on a PivotTable? Fixes That WorkYes, VLOOKUP can look up values in a PivotTable’s displayed cells, but it often fails when the pivot’s…
- Excel Not Taking Formula? Fix the Cell or CalculationIf Excel is not taking a formula, first check whether the cell is formatted as Text: set it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.