How to Identify Missing Numbers Sequence in Excel?

Sometimes Excel contains data with missing numbers which creates difficulty and inconsistency in the data. Two methods are described in this article to obtain the missing series of numbers. After identification of the missing series, users may fill the gap by using the Autofill method or by utilizing the VBA code. Kutools tab is showcased in the first example whereas the specified formula is defined in the second example to find out the missing numbers in the dataset. All the guided examples are easy to learn and understand.

Example 1: To identify the missing sequence number in excel by using the kutools.

Step 1

In this article, the user will learn the process to identify the missing numbers of sequences by using the kutools in Excel. To start the article, consider some sample data for reference. First, select the “Editing” tab, and then click on the “Insert” tile, after that click on the “Insert Sequence Number”. A snapshot for reference is provided below:

Step 2

The above step will open the “Find Missing Sequence Number” dialog box. This dialog box contains a range input label. Here, select the data range from the available sheet. In the next input label “handling missing sequence number”, 4 options are available, will select all the options one by one.

Step 2.1.1

Select the third option "Inserting the new column with the following missing marker". And click on the "OK" button. snapshot for reference is provided below:

Step 2.1.2

The above step will display a “Find Missing Sequence Number” dialog box. This dialog box displays the total number of missing numbers. For the chosen data set the total number of missing values is 3. Sample output is provided below:

Step 2.1.3

After clicking on “Ok”. “missing” word is displayed at the side of the cells, whose next number is missing. Snapshot is provided below:

Step 2.2.1

In this step, choose “Inserting missing sequence number”, and then click on the ‘OK” button. this will display a dialog box for “Find Missing Sequence Number”, and the dialog box will display the count as 4, and after that click on the OK button.

Step 2.2.2

This step will display all the missing values precisely, without any issue. Snapshot for reference is provided below:

Step 2.3.1

In this part click on the last option “Fill background color”. This step will display another dialog box, the “Find Missing Sequence Number” dialog box, and then click on the “OK” button. snapshot for reference is provided below:

Step 2.3.2

The above step will highlight all the cells with the yellow color selected above. Please note that this step will allow users to choose different colors. Selecting a color is based on the individual need of the user.

Example 2: To identify the missing sequence number in Excel by using the user-defined formula.

Step 1

To understand the process of evaluating the missing numbers in the Excel sheet, the user needs to assume the below-depicted spreadsheet.

Step 2

After that go to the C3 cell, and type formula “=IF(B4-B3=1,"","Missing")”. Snapshot for using formula is listed below:

Step 3

Press “Enter” key to display the required result. After that drag the fill handle to copy the same formula, for other rows, as well.

Step 4

Finally generated output snapshot if provided below for reference:

Conclusion

In this article, users will be able to understand the missing numbers sequence in Excel. This article illustrates step-wise examples to perform the required task in more than one simple way. For example, here the first method allows the user to use the kutools, while the second method is concerned with the use of user-defined formula.