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:

- Select the range A2 through the last value in column A.
- 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.
- Choose Highlight Cells Rules and then choose More Rules from the resulting submenu. Excel displays the New Formatting Rule dialog box. (See Figure 1.)
- In the Select a Rule Type area at the top of the dialog box, choose Use a Formula to Determine Which Cells to Format.
- In the Format Values Where This Formula Is True box, enter this formula:
**=A2<>A1+1** - Click Format to display the Format Cells dialog box.
- Using the controls in the dialog box, specify a format that you want used to highlight the cells that are not consecutive.
- 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.
- Click OK.

** Figure 1.** The New Formatting Rule dialog box.

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.

**Program Successfully in Excel!** John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out *Excel 2013 Power Programming with VBA* today!

If you need to generate a random sequence of characters, of a fixed length, then you'll appreciate the discussion in this ...

Discover MoreWhen you are editing a formula, Excel helpfully outlines the cells referenced in the formula. If you want this capability ...

Discover MoreWant to figure out how far it is between two points on the globe? If you know the points by latitude and longitude, you ...

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

2020-11-03 06:21:28

Peter Atherton

You can,, as stated in the last paragraph, copy the formula down to get the next value(s), When this is done, the Cell reference in the last part of the formula changes from Row(A1) to Row(A2) and the small value hence uses the fist then the second value.

{=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)),"")}

{=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(A2)),"")}

2020-11-02 18:29:06

Jair

If i paste this formula

=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)),"")

it will apears only the first missing number how about the next missing gap?

2019-08-05 16:30:52

Gail

This is perfect! You have saved me a ton of time.

2017-09-08 10:09:56

Yvan Loranger

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

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.

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

Copyright © 2021 Sharon Parq Associates, Inc.

## Comments