Identify Missing IDs and Sequence Gaps

There are several ways in Excel to find missing IDs (or gaps) in a big list of sequential IDs, such as check numbers or invoice numbers.

In this video, we'll use Power Query so that each time we have a new list, we simply click Refresh. Excel then creates an updated list of the missing IDs. This approach makes recurring tasks fast because there is no need to manually sort, write formulas, filter, or apply conditional formatting. Just click Refresh and Excel provides a list of the missing Ids.

[EricaDyson]

Just what I needed. I knew PQ could do it and you've shown me how - neat! Thanks a lot

[yuk]

this is a god send, thank you so much for this 😭❤️

[MLee]

This is perfect! a quick question though, when I want do this for a new list, how do I connect the new list to the already existing sequence number test that I had performed? Do I work on the same workbook and load new data ( and create connection only query) to the already-existing power query results obtained from previous sequence test?

[Altvel]

How du you use this query next month (when you get a new list of cheks)? Paste it over the old one? Or can you locate the query in another way?

[Macfinity]

I am toiling to find a solution for Sequence Gaps where numbers start with a character!