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**.

Table of Contents

## 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)…

## Leave a Reply

You must be logged in to post a comment.