How to Insert Specific Number of Columns at Fixed Intervals in Excel?

In your daily usage of Excel, you may run into a situation that you need to add more columns between the existing columns. You can, of course, insert the columns one by one using the Insert function. But when you are facing a large data and need to insert three columns after every second or nth column, the Insert function is apparently not effective. So how can you quickly finish the task? In this tutorial, we will talk about two easy methods to solve the problem.

Use VBA code to insert specific number of blank columns into data range at fixed intervals

Use Kutools for Excel to insert specific number of blank columns into data range at fixed intervals

We can use the below "VBA code" to insert a specific number of blank columns after every nth column. Please do as follows.

1. Press the "Alt + F11" keys in Excel, and it opens the "Microsoft Visual Basic for Applications" window.

2. Click "Insert" > "Module" and paste the following macro in the "Module" Window.

Sub InsertColumnsAtIntervals()
'Updateby Extendoffice
Dim Rng As Range
Dim xInterval As Integer
Dim xColumns As Integer
Dim xColumnsCount As Integer
Dim xNum1 As Integer
Dim xNum2 As Integer
Dim WorkRng As Range
Dim xWs As Worksheet
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xColumnsCount = WorkRng.Columns.Count
xInterval = Application.InputBox("Enter column interval. ", xTitleId, 1, Type:=1)
xColumns = Application.InputBox("How many columns to insert at each interval? ", xTitleId, 1, Type:=1)
xNum1 = WorkRng.Column + xInterval
xNum2 = xColumns + xInterval
Set xWs = WorkRng.Parent
For i = 1 To Int(xColumnsCount / xInterval)
    xWs.Range(xWs.Cells(WorkRng.Row, xNum1), xWs.Cells(WorkRng.Row, xNum1 + xColumns - 1)).Select
   Application.Selection.EntireColumn.Insert
    xNum1 = xNum1 + xNum2
Next
End Sub

3. Press the "F5" key to run this code. A prompt box pops out to remind you to select the "data range" in which you want to insert blank columns.

4. Click the "OK" button. Another prompt box pops out, please enter "the number of columns intervals" in the "Enter column interval" box.

5. Continue to click the "OK" button. The third prompt box pops out, please specify "the number of columns you want to insert at each interval" in the box.

6. Click the "OK" button to get the result.

If you don’t want to use the VBA code method, the "Insert Blank Rows & Columns" feature of "Kutools for Excel" is the perfect solution for you. With this feature, you can insert the specific number of columns into data range at fixed intervals in just a few clicks.

1. Select the existing data range first. Then click "Kutools" > "Insert" > "Insert Blank Rows & Columns".

2. The "Insert Blank Rows & Columns" dialog box pops up. You can see the data range you selected in step 1 displayed in the Range box. Choose the "Blank columns" option in the "Insert type" section. And specify "the numbers of columns intervals" and "columns you want to insert at each interval". Here I input 1 and 3 separately.

3. Click the "OK" button to finish the setting and get the result.