Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

How to insert numbers or rows for missing sequential numbers in Excel?

Author Xiaoyang Last modified

Supposing you have a list of sequential numbers in a worksheet, but there are some missing numbers among the sequence, and now you need to insert the missing numbers or blank rows to make sure the sequence be complete (shown as the following screenshots). How could you quickly solve this problem in Excel?

original data arrow right insert rows for missing sequential numbers

Insert missing numbers for sequence with Sort and Remove Duplicates feature

Insert missing numbers or blank rows for sequence with Kutools for Excel

Insert missing numbers for sequence with VBA code

Insert blank rows for missing sequence with VBA code


arrow blue right bubble Insert missing numbers for sequence with Sort and Remove Duplicates feature

May be you can find the missing numbers one by one, and then insert them, but it is difficult for you to identify the location of the missing ones if there are hundreds of sequential numbers. In Excel, I can use the Sort and Remove Duplicates feature to deal with this task.

1. Following the end of the sequence list, fill in another sequence numbers from 2005023001 to 2005023011. See screenshot:

fill in another sequence numbers below the data list

2. Then select the range of the two sequence numbers and click Data > Sort A to Z, see screenshot:

screenshot of clicking Data > Sort A to Z

3. And the selected data has been sorted as following screenshot:

the data has been sorted

4. Then you need to remove the duplicates by click Data > Remove Duplicates, and in the popped out Remove Duplicates dialog box, check the Column name that you want to remove the duplicates, see screenshots:

screenshot of clicking Data > Remove Duplicates arrow right check the Column name that you want to remove the duplicates

5. Then click OK, the duplicates in Column A has been deleted, and the missing numbers in the sequence list have been inserted, see screenshot:

the missing numbers in the sequence list have been inserted


arrow blue right bubble Insert missing numbers or blank rows for sequence with Kutools for Excel

Here, I will introduce an easy and handy tool- Kutools for Excel, with its Find Missing Sequence Number feature, you can quickly insert the missing sequence number or blank rows between the existing data sequence.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

If you have installed Kutools for Excel, please do as follows:

1. Select the data sequence that you want to insert the missing numbers.

2. Click Kutools > Insert > Find Missing Sequence Number, see screenshot:

click Find Missing Sequence Number feature of kutools

3. In the Find Missing Sequence Number dialog box, check Inserting missing sequence number to insert the missing numbers or Inserting blank rows when encountering missing sequence numbers to insert blank rows as you need. See screenshot:

select the options you need in the dialog box

4. And then click OK button, and the missing sequence numbers or blank rows have been inserted into the data , see screenshots:

original data  arrow right insert missing numbers arrow right insert rows for missing numbers

Download and free trial Kutools for Excel Now !


arrow blue right bubble Insert missing numbers for sequence with VBA code

If you feel there are so many steps with the above methods, here also have VBA code can help you finish this problem. Please do as follows:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module window.

VBA: insert missing numbers for the sequence

Sub InsertValueBetween()
'Updateby Extendoffice
Dim WorkRng As Range
Dim Rng As Range
Dim outArr As Variant
Dim dic As Variant
Set dic = CreateObject("Scripting.Dictionary")
'On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
num1 = WorkRng.Range("A1").Value
num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
interval = num2 - num1
ReDim outArr(1 To interval + 1, 1 To 2)
For Each Rng In WorkRng
    dic(Rng.Value) = Rng.Offset(0, 1).Value
Next
For i = 0 To interval
    outArr(i + 1, 1) = i + num1
    If dic.Exists(i + num1) Then
        outArr(i + 1, 2) = dic(i + num1)
    Else
        outArr(i + 1, 2) = ""
    End If
Next
With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
    .Value = outArr
    .Select
End With
End Sub

3. Then press F5 key to run this code, and a prompt box will pop out, please select the data range that you want to insert the missing numbers(don’t select the title range), see screenshot:

vba code to select the data list

4. And then click OK, the missing numbers have been inserted to the sequence list. See screenshots:

original data arrow right Insert missing numbers for sequence

arrow blue right bubble Insert blank rows for missing sequence with VBA code

Sometimes, you just need to locate the place of the missing numbers and insert blank rows between the data, so that you can enter the information as you need. Of course, the following VBA code also can help you solve this problem.

1. Hold down the ALT + F11 keys, and it opens a Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module window.

VBA: insert blank rows for missing sequence

Sub InsertNullBetween()
'Updateby Extendoffice
Dim WorkRng As Range
Dim Rng As Range
Dim outArr As Variant
Dim dic As Variant
Set dic = CreateObject("Scripting.Dictionary")
'On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
num1 = WorkRng.Range("A1").Value
num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
interval = num2 - num1
ReDim outArr(1 To interval + 1, 1 To 2)
For Each Rng In WorkRng
    dic(Rng.Value) = Rng.Offset(0, 1).Value
Next
For i = 0 To interval
    If dic.Exists(i + num1) Then
        outArr(i + 1, 1) = i + num1
        outArr(i + 1, 2) = dic(i + num1)
    Else
        outArr(i + 1, 1) = ""
        outArr(i + 1, 2) = ""
    End If
Next
With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
    .Value = outArr
    .Select
End With
End Sub

3. Then press F5 key to run this code, and a prompt box will display, and then select the data range that you want to insert blank rows for the missing sequence(don’t select the title range), see screenshot:

vba code to select data list

4. And then click OK, the blank rows have been inserted for the missing sequence list. See screenshots:

original data arrow right Insert blank rows for missing sequence

Related article:

How to identify missing numbers sequence in Excel?

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license — set up in minutes (MSI-ready)
  • Works better together — streamlined productivity across Office apps
  • 30-day full-featured trial — no registration, no credit card
  • Best value — save vs buying individual add-in