Return to Excel Formulas List

List Skipped Numbers in Sequence – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to list missing numbers in a range.

list skipped numbers in sequence Main Function

List Skipped Numbers in Sequence

To find missing numbers in a list, we can use the following formula:

list skipped numbers in sequence 01

Notes:

  • This is an array formula! When using Excel 2019 and earlier, you must enter the formula by pressing CTRL + SHIFT + ENTER. You’ll know the formula was entered properly when you see curly brackets around the formula.
  • This formula only works with positive whole numbers (integers).

How does the formula work?

First, the ROW Function is used to generate an array of numbers:

In fact, you could even enter in this array of numbers manually:

Next, The COUNTIF Function will count the values “if” they match a value found in the array.

To visualize this, first we will convert the range $B$3:$B$8 to it’s array of values {3;2;0;6;0;5}:

Next we will perform the COUNTIF operation:

If a value is not found 0 is outputted. The IF Function checks if the value is NOT Found and if so, returns the value from the array, otherwise it outputs blank:

Last, the SMALL Function returns the smallest missing value in the selected cell.

Tip:

To avoid the #NUM! error, wrap your formula with the IFERROR Function:

list skipped numbers in sequence If Error

List Skipped Numbers in Sequence in Google Sheets

All the examples explained above work the same in Google sheets as they do in Excel.

list skipped numbers in sequence Google Function