Excel Missing Numbers in a Sequence – can I use functions to find out what numbers are missing?
This week’s hint and tip is looking at Excel missing numbers in a sequence in your spreadsheets. We are looking at a way in which you can use functions to overcome this issue. This isn’t covered in any of our Excel training courses so we decided to do a hint and tip on it. We are going to go through it now below.
Excel missing numbers in a spreadsheet
Excel is used by many on a daily basis and often on spreadsheets with vast amounts of numbers. With there being so many numbers in spreadsheets, we can sometimes miss one without realising it. In this post we see how its possible with some Excel functions to identify missing numbers in a range.
** this solution in Excel to ‘fix’ this problem will only work on pure numbers **
The functions we need to use
We will use two functions to help overcome this issue and ‘fix’ it. The two functions are SEQUENCE and UNIQUE.
=SEQUENCE(cell reference). The cell reference part will contain a number and in its simplest form a column of sequential numbers is generated from 1 up to that number in the spreadsheet.
=UNIQUE(array,[by_col],[exactly_once]). This function returns a list of unique numbers in a list or range.
We can use these two functions in a spreadsheet to work out what number is missing.
The ‘fix’ for working out the missing numbers
So in the attached worksheet A1:A29 contains a sequence with a missing number (11) and underneath that range all the numbers 1 – 15 in sequence. Screenshot can also be seen below.
We have used a function in cell B1 to work out what number is missing in the ‘original’ sequence of numbers. This function being =UNIQUE(A1:A29,FALSE,TRUE). The parts are explained below:
- By_col: FALSE means you can compare row by row the cells in 1 column
- Exactly_once: TRUE just picks up the numbers used once in that range in this case 11
The video below shows you how to ‘fix’ the issue of having missing numbers in a sequence in your spreadsheet. You can view the spreadsheet in the video here (clicking here will download a copy to your computer so you can try it out!). If you have a long list of numbers it can sometimes be hard to see if one is missing, so this could help save you a lot of time.
Take a look below at the video to find out more and then try them out on your own computer!
We hope you have enjoyed this hint and tip on Excel missing numbers. Why not take a look at our previous video hint and tip on how to insert a chart into your presentations in PowerPoint?