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:

Contents

## 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 n^{th }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: