Identifying Missing Numbers in a Consecutive Series

by Allen Wyatt
(last updated September 2, 2017)

1

Marcya has a long list of sorted numbers in column A of a worksheet. These numbers are supposedly consecutive, but she doesn't know if that is true. Examining the list manually is both tedious and error-prone, so Marcya wonders if there is a way to somehow highlight any "missed numbers" (those that aren't consecutive with the one before) or to compile a list of numbers that were missed in the list.

There are a number of ways you can go about figuring out where there are missing numbers. The first is one I use quite often: I add a helper column next to column A. Assuming your numbers start in cell A1, I put this into cell B2:

=IF(A2<>A1+1,"Error","")

Copy the formula down as many cells as necessary, and you'll easily see the word "Error" next to any value that isn't consecutive to the value just above it. If you prefer to know a bit more about the error, you could use a more detailed formula:

=IF(A2=A1,"Duplicate",IF(A2<>A1+1,"Gap",""))

Another approach is to use conditional formatting on the cells in column A. Follow these steps, again assuming that your values start in cell A1:

  1. Select the range A2 through the last value in column A.
  2. With the Home tab of the ribbon displayed, click the Conditional Formatting option in the Styles group. Excel displays a palette of options related to conditional formatting.
  3. Choose Highlight Cells Rules and then choose More Rules from the resulting submenu. Excel displays the New Formatting Rule dialog box. (See Figure 1.)
  4. Figure 1. The New Formatting Rule dialog box.

  5. In the Select a Rule Type area at the top of the dialog box, choose Use a Formula to Determine Which Cells to Format.
  6. In the Format Values Where This Formula Is True box, enter this formula: =A2<>A1+1
  7. Click Format to display the Format Cells dialog box.
  8. Using the controls in the dialog box, specify a format that you want used to highlight the cells that are not consecutive.
  9. Click OK to dismiss the Format Cells dialog box. The formatting you specified in step 7 should now appear in the preview area for the rule.
  10. Click OK.

Finally, if you want to compile a list of the missing numbers in a consecutive series, you can use an array formula. Place the following into row 1 of an empty column:

=IFERROR(SMALL(IF(COUNTIF($A$1:$A$135,
MIN($A$1:$A$135)+ROW($1:$135)-1)=0, 
MIN($A$1:$A$135)+ROW($1:$135)-1),ROW(A1)),"")

Remember that this is a single array formula, so you need to enter it as a single line using Ctrl+Shift+Enter. You can then copy the formula down a number of cells, until it doesn't return any more values. Also, the formula assumes that your series in in the range A1:A135; if it is not, you'll need to modify the formula to reflect the actual range.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (4315) applies to Microsoft Excel 2007, 2010, 2013, and 2016.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Checking for Words and Phrases

You may want to determine if a document contains a certain set of words or phrases. There are a couple of ways you can make ...

Discover More

Viewing Document Statistics

As you develop a document, Word keeps track of certain statistics about the document itself. Here is how you can review those ...

Discover More

Incrementing Numeric Portions of Serial Numbers

If you use serial numbers that include both letters and numbers, you might wonder how you can increment the numeric portion ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Counting Precedents and Dependents

Do you need to know how many precedents or dependents there are on a worksheet? You could count them manually, or you could ...

Discover More

Summing Absolute Values

You can easily sum a series of values in Excel, but it is not so easy to sum the absolute values of each value in a range. ...

Discover More

Identifying Digit-Only Part Numbers Excluding Special Characters

When working with data in Excel, you often need to determine if that data meets criteria that you specify. This tip examines ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 + 6?

2017-09-08 10:09:56

Yvan Loranger

1st solution:
Put in B2 =IF($A2-$A1>column()-1,$A1+column()-1,"") and replicate.
Result:
row col.A col.B col.C col.D
1 -5
2 -1 -4 -3 -2
3 0
4 2 1
5 3

2nd solution:
Put in B2 =IF(A2-A1>1,A1+1&"-"&A2-1,"") and replicate.
Result:
row col.A col.B
1 -5
2 -1 -4--2
3 0
4 2 1-1
5 3


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.