Fresh Excel Tips

For this example, we have a list of a numbers in Column A and we’d like to check if there are any missing values assuming that they are supposed to be consecutive values, i.e., from 1-10 or from 234 - 456.

For this example, all of our values are listed from cells A1:A10 and we will be checking from 1-10. Here is how to check for missing values from list.

1. Select Cells B1:B10 and insert the following formula: In B1: =IF(ISNUMBER(MATCH(ROWS($1:1),A:A,0)),”",ROWS($1:1)) then press CTRL+Enter. This will tell you which numbers are missing at the positions they are missing from.
2. If working with a long list of data, then use this formula: =IF(ROWS($1:1)>COUNT(B:B),”",INDEX(B:B,SMALL(B:B,ROWS($1:1)))) . This will tell you the missing values and put them at the top making them easier to find.

You should now be set! This should work in Excel 97, 2000, 2003 and 2007.

If work with numbers other than 1-10, for example 234-456, you can use a modified version of the formula below to suit your needs.

In B1:
=IF(ISNUMBER(MATCH(ROWS($1:1)+233,A:A,0)),”",ROWS($1:1)+233)
(Add 233 to ROWS($1:1) so that it begins with 234 - your 1st number)

In C1:
=IF(ROWS($1:1)>COUNT(B:B),”",SMALL(B:B,ROWS($1:1)))
Copy B1:C1 down by 223* rows to C223
*the range: 234 to 456 contains 456-234+1 = 223 numbers

1. How to Check for Valid Numbers Using the T Function in Excel
2. How to Use the SUBTOTAL function to sum a filtered list in Excel
3. Using the OR function to check cells for text or numbers in Excel
4. How to Use the SUBTOTAL function to count rows in filtered lists
5. How to convert invalid numbers to valid numbers in Excel