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:

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:

to see how it works.

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

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:

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:

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

We now have the list of missing numbers.

Step 5 – The ROW(A1) function evaluates to 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:

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:

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.