Find Missing Number in Sequence in Excel

Sometimes you may want to identify missing numbers in a sequence in Excel such as product ID numbers. One way would be to manually scroll through the dataset and identify the missing numbers. However, this would be quite a tedious, time-consuming, and error-prone task especially if you are working with large datasets.

In this tutorial we will explain two methods that can be used to find missing numbers in sequence in Excel more easily and efficiently:

Method 1 – Use an Excel formula

We can use an Excel formula that combines the SMALL, IF, COUNTIF, and ROW functions.

We will use the following dataset to demonstrate how this method can be used:

Step 1 – Enter the formula:

=SMALL(IF(COUNTIF($A$1:$A$6,ROW($21:$32))=0,ROW($21:$32),""),ROW(A1))

in cell C1:

Step 2 – The formula is an array formula so enter it by pressing CTRL + SHIFT + ENTER:

Step 3 – Using the Fill Handle, drag the formula down to cell C6:

All the missing numbers in the sequence will be generated.

The logic of the Formula

The formula counts the numbers in the range A1:A6 that match the numbers generated by the ROW($21:$32) which are {21;22;23;24;25;26;27;28;29;30;31;32}. If they are missing, that is, =0, it gives a list of them. If they are not missing, that is, not =0, it returns an empty string (“”), and then returns the first smallest missing value in the current cell.

The ROW(A1) part of the formula is relative meaning as it is copied down the column C, it becomes ROW(A2) which =2, and returns the second smallest value, ROW(A3) which =3 and returns the third smallest value, and so on.

The functions used in the formula

SMALL function

It returns the smallest nth value in a range, in the form of the first smallest value, the second smallest value, the third smallest value, and so on.

IF function

It tests for counts of 0. If it is TRUE, that is 0, it means the number is missing and so it returns the corresponding value from the array generated by ROW(21:32).

COUNTIF function

It counts the numbers in the range A1:A6 that match the range of values returned by the ROW function.

ROW function

It returns the row number of a cell reference.

Stepping through the formula

We will step through the formula:

=SMALL(IF(COUNTIF($A$1:$A$6,ROW($21:$32))=0,ROW($21:$32),""),ROW(A1)) 

to see how it works.

Step 1 – The values in range A1:A6 and ROW(21:32) are returned:

=SMALL(IF(COUNTIF({21;27;25;28;30;32},ROW({21;22;23;24;25;26;27;28;29;30;31;32}))=0,ROW($21:$32),""),ROW(A1))

Step 2 – The COUNTIF function returns the counts of values in the array returned by ROW(21:32), that are found in the range A1:A6:

 =SMALL(IF({1;0;0;0;1;0;1;1;0;1;0;1})=0,ROW($21:$32),""),ROW(A1))

This means that there is 1 count of 21, 0 count of 22, and so on in the range A1:A6.

Step 3 – The IF function returns TRUE if the number in the resultant array =0 and FALSE if it doesn’t:

=SMALL(IF({FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE},{21;22;23;24;25;26;27;28;29;30;31;32),""),ROW(A1))

Step 4 – The IF function evaluates the  value_if_true and value_if_false values:

=SMALL({"";22;23;24;"";26;"";"";29;"";31;""},ROW(A1))

We now have the list of missing numbers.

Step 5 – The ROW(A1) function evaluates to 1:

=SMALL({"";22;23;24;"";26;"";"";29;"";31;""},1)

Step 6 – The SMALL function returns the first smallest value in the array which is =22.

The next smallest value is =23 and so on.

The Formula method generally works well but it has the limitation of working only with whole numbers that are greater than 0.

To overcome this limitation we have to turn to the next method of using Excel VBA code.

Method 2 – Use Excel VBA Code

We will use the following dataset to demonstrate how VBA code can be used to find missing numbers in sequence in Excel:

Step 1 – Press Alt + F11 to open the Visual Basic Editor (VBE). Alternatively, you can click Developer >> Visual Basic:

Step 2 – In the VBAProject window right click your workbook and click Insert >> Module:

Step 3 – Enter the following code in the new module. Note that comments that are not executable are preceded by an apostrophe (‘). The comments explain what the code is doing:

Sub FindMissingNumbers()
    Dim InputRange  As Range, OutputRange As Range, NumberFound As Range
    Dim LowerNum    As Double, UpperNum As Double, count_i As Double, count_j As Double
    Dim NumRows     As Long, NumColumns As Long
    Dim Horizontal  As Boolean
    'Default is to output the results into a column
    Horizontal = FALSE
    On Error GoTo ErrorHandler
    'Ask for the range to check
    Set InputRange = Application.InputBox(Prompt:="Select a range To check :", _
    Title:="Find missing numbers", _
    Default:=Selection.Address, Type:=8)
    'Find the lowest and highest numbers in the range/sequence
    LowerNum = WorksheetFunction.Min(InputRange)
    UpperNum = WorksheetFunction.Max(InputRange)
    'Ask where the output is to go
    Set OutputRange = Application.InputBox(Prompt:="Select where you want the result To go :", _
    Title:="Select cell For Results", _
    Default:=Selection.Address, Type:=8)
    'Check the number of rows and columns in the output range
    NumRows = OutputRange.Rows.Count
    NumColumns = OutputRange.Columns.Count
    'If there are more columns selected than rows, the output is to go horizontally
    If NumRows < NumColumns Then
        Horizontal = TRUE
        'Reset the number of rows to 1 so that output is into the first row
        NumRows = 1
    Else
        'Reset the number of columns to 1 so that output is into the first column
        NumColumns = 1
    End If
    'Initalise counter and loop through sequence from lowest to highest value
    count_j = 1
    For count_i = LowerNum To UpperNum
        'Search for the current value (count_i)
        Set NumberFound = InputRange.Find(count_i, LookIn:=xlValues, LookAt:=xlWhole)
        'If it's not found, we have a missing number in the sequence
        If NumberFound Is Nothing Then
            'Output the missing number to the sheet
            If Horizontal Then
                OutputRange.Cells(NumRows, count_j).Value = count_i
                count_j = count_j + 1
            Else
                OutputRange.Cells(count_j, NumColumns).Value = count_i
                count_j = count_j + 1
            End If
        End If
    Next count_i
    Exit Sub
    ErrorHandler:
    If InputRange Is Nothing Then
        MsgBox "ERROR : No input range specified."
        Exit Sub
    End If
    If OutputRange Is Nothing Then
        MsgBox "ERROR : No output cell specified."
        Exit Sub
    End If
    MsgBox "An Error has occurred. The macro will end."
End Sub

Step 4 – Click the Save button:

Step 5 – Save the workbook as a .xlsm file:

Code Explanation

The code uses VBA’s FIND method on the input range you specify to look for the missing numbers in sequence.

When the code is executed, Excel prompts asks for the input range in which to search for missing numbers. Use the mouse to select the range.

You will then be prompted for the output range where the missing numbers will be listed.

Code Execution

Step 1 – Switch to the current workbook by pressing Alt + F11. Alternatively, click on the View Microsoft Excel button:

Step 2 – Press Alt + F8 to open the Macro dialog box and select the FindMissingNumbers macro and click on the Run button:

Step 3 – In the Find missing numbers dialog box, enter the input range by selecting using the mouse and then click OK:

Step 4 – Enter the output range and click OK:

The missing numbers will appear in the output range you selected: