If you’re working with data in Excel and notice that there are some missing numbers in a sequence, it can be frustrating to try and figure out where they are. Fortunately, there are a few ways to find these missing numbers quickly and easily.

This post will guide you how to find missing numbers in a sequence with a formula in Excel. How do I identify missing numbers in a consecutive series in Excel. How to find missing serial number in Excel 2013/2016.

This post will guide you through two methods to find missing numbers in a sequence in Excel: using a formula and using VBA code.

1. Find Missing Numbers in a Sequence in Excel

Assuming that you have a serial number list in Column B, and you want to find the missing number in this sequence list. How to achieve it.

You can use an excel array formula based on the SMALL function, the IF function, the ISNA function, the MATCH function, and the ROW function. Like this:

=SMALL(IF(ISNA(MATCH(ROW(B$1:B$20),B$1:B$20,0)),ROW(B$1:B$20)),ROW(B1))

Type this array formula into a blank cell, and then press Ctrl + Shift + Enter keys in your keyboard.

The missing numbers are listed in cells.

Note: this formula will check the given sequence from 1 to 20 if there are missing numbers. If so, returns the missing numbers.

Or you can use another array formula based on the Small function, the IF function, the Countif function and the row function to achieve the same result. Like this:

=SMALL(IF(COUNTIF($B$1:$B$10,ROW($1:$20))=0,ROW($1:$20),""),ROW(B1))

2. Find Missing Numbers in a Sequence using VBA Code in Excel

To find missing numbers in a sequence in Excel with VBA code, you can follow these steps:

Step1: Press Alt + F11 to open the Visual Basic Editor (VBE) or click Developer > Visual Basic.

Step2: In the VBE window, right-click your workbook and click Insert > Module.

Step3: In the module window, paste the following code:

Sub FindMissingNumbers_ExcelHow()
    Dim rng As Range
    Dim outRng As Range
    Dim i As Long
    Dim n As Long
    Dim found As Range
    
    ' Prompt the user to select the range of data to export
    Set rng = Application.InputBox(prompt:="Select the range that contain a sequence list", Type:=8)
    Set outRng = Application.InputBox(prompt:="Select one cell as output range", Type:=8)
    
    n = Application.Min(rng) 'get the minimum number in the input range
    
    For i = 1 To Application.Max(rng) - n + 1 '
        Set found = rng.Find(n, LookIn:=xlValues, LookAt:=xlWhole)
        If found Is Nothing Then
            outRng.Value = n
            Set outRng = outRng.Offset(1)
        End If
        n = n + 1
    Next i
    
End Sub

Step4: Press ALT + F8 on your keyboard to open the Macro dialog box. Select the FindMissingNumbers_ExcelHow macro from the list and click the Run button.

Step5: Select one range of cells that contains a sequence to find missing numbers.

Step6: Select one cell as output range to place the missing numbers.

Step7: The missing numbers in the sequence will be listed in the output range.

3. Video: Find Missing Numbers in a Sequence in Excel

This video will demonstrate both a formula and VBA code to help you find missing numbers in a sequence in Excel.

4. Related Functions

• Excel MATCH function
  The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
• Excel SMALL function
  The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
• Excel ROW function
  The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
• Excel IF function
  The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
• Excel COUNTIF function
  The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…