Excel 2016 – Fill in missing numbers in a range

The following process shows how to fill missing numbers in a range.

For example, if we had an excel table where the numbers 3, 4, and 6 were missing.

Instead of manually going through to find the missing numbers and filling them in (which would be horrible with a larger data set). 

We’re going to do this by adding the full range of numbers again, sorting and removing duplicates – end result = a complete range of numbers.

Step 1: add full range of numbers

1. Directly below the list, add the full range again – for example 1-10.
2. 

Step 2: Sort the list

1. Select the full data set
2. 
3. Open the ‘Data’ tab
4. In the ‘Sort & Filter‘ group click on the ‘Sort’ button
5. 
6. The sort window will load.
7. Select the ID column and click ‘OK’
8. 
9. The list will now be sorted – but will have duplicates
10. 

Step 3: Remove duplicates

1. Open the ‘Data’ tab
2. In the ‘Data Tools’ group click on ‘Remove Duplicates’
3. 
4. Leave only the  ID column checked and click ‘OK’
5. 
6. You’ll now have a complete list with the missing numbers
7.