# List Skipped Numbers in Sequence – Excel & Google Sheets

This tutorial will demonstrate how to list missing numbers in a range. ## List Skipped Numbers in Sequence

To find missing numbers in a list, we can use the following formula: 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 in Google Sheets

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