Table of Contents
You can use the following formula in Excel to identify missing numbers in a sequence:
=FILTER(NOT(COUNTIF(A2:A13,SEQUENCE(D2+1-D1,,D1)))*SEQUENCE(D2+1-D1,,D1),NOT(COUNTIF(A2:A13,SEQUENCE(D2+1-D1,,D1))))
This particular formula finds all of the missing values in the sequence in the range A2:A13.
This formula assumes that cell D1 contains the minimum value in the sequence and cell D2 contains the maximum value in the sequence.
The following example shows how to use this formula in practice.
Example: Identify Missing Numbers in a Sequence in Excel
Suppose we have the following column of numbers in Excel:
Notice that the values range from 1 to 19.
Suppose we would like to identify all of the values that are missing in this sequence.
First, we’ll type the following formulas into cells D1 and D2 to find the minimum and maximum values in the sequence:
- D1: =MIN(A2:A13)
- D2: =MAX(A2:A13)
The following screenshot shows how to use these formulas in practice:
Next, we can type the following formula into cell C5 to find all missing values in the sequence:
=SMALL(IF(ISNA(MATCH(ROW(A$1:A$13),A$1:A$13,0)),ROW(A$1:A$13)),ROW(A1))
The following screenshot shows how to use this formula in practice:
Column C displays every missing value from the sequence of numbers in column A.
For example, we can see that the values 2, 3, 7, 10, 11, 15 and 18 are all missing from column A.
Note that this formula will work with any sequence of numbers.
For example, we can use the same formula to identify the missing values in the following sequence that ranges from 10015 to 10031:
The formula correctly identifies the missing values in the sequence.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: