Insert blank column every other column in Excel – 4 easy ways
When working with data in Excel, there are times when you need to insert blank columns between existing columns. Doing this manually for small datasets is simple, but for larger datasets, an efficient approach is essential. This article will introduce some useful methods for solving this task in Excel.
Insert blank column every other or nth column in Excel
- With the shortcut key
- By uisng a helper column and the Sort feature
- By using Kutools for Excel
- With VBA code
Insert blank column every other or nth column by shortcut key
For smaller datasets, manually inserting blank columns with keyboard shortcuts is fast and efficient. Follow these steps:
- Select the column by clicking on its column number where you want to insert a blank column.
- Next, press and hold the Ctrl key on your keyboard, and then click on the column numbers one by one that you want to select. See screenshot:
- Then, press "Ctrl + Shift + Plus" keys, blank columns are inserted between every other column. See screenshot:
Insert blank column every other or nth column by a helper column and the Sort feature
If you want to insert blank columns systematically after every other or nth column in a larger dataset, the helper column method combined with the Sort feature can save time:
- Insert a new blank row just above the dataset.
- Enter the number 1 and 2 in the first two cells in the row. Then, select cells A1 and B1, and then drag the fill handle right to the last data cell, a list of incrementing numbers will be filled as below screenshot shown:
- Copy the sequence numbers and paste them just right after the last number in the same row.
- Select the whole data range, and then, click "Data" > "Sort", see screenshot:
- In the following "Sort" dialog box, click "Options" button.
- Then, select "Sort left to right" and click OK to back to the "Sort" dialog box.
- Select added new row from the "Sort by" drop down list, and leave the other options unchanged. Then, click OK to close the dialog.
- Now, the data will be sorted based on the values in the helper row, and a blank column is inserted after every other column.
- At last, you can delete the helper row if needed.
- If you want to insert a blank column after every two columns, in this example, in cell F1, enter the number 2; in cell G1, enter the number 4. Then, select these two cells and drag the fill handle right to fill a series of numbers. Make sure the last number in the series is equal to or greater than the highest number in your dataset.
- Similarly, to insert a blank column after every three columns, in F1, enter the number 3; in G1, enter the number 6. And so on…
- If you want to insert two or more blank columns between existing columns, you just need to copy and paste the helper row list two or more times right the last cell.
Insert blank column every other or nth column by by only a few clicks
Kutools for Excel provides a simple and efficient solution to automate this task. With just a few clicks, you can quickly insert blank columns at specific intervals, saving time and boosting productivity. In this section, we'll show you how to use Kutools for Excel to insert blank columns effortlessly.
After downloading and installing Kutools for Excel, click "Kutools" > "Insert" > "Insert Blank Rows & Columns" to open the "Insert Blank Rows & Columns" dialog box.
- Select the data range where you want to insert blank columns.
- Choose "Blank Columns" from the "Insert type" section.
- Then, specify the numbers into the "Interval of" and "Columns" textboxes as you need. (Here, I will insert two blank columns after one column, so, I enter 1 and 2 into the Interval of and Columns boxes separately.)
- At last, click OK to close the dialog.
Kutools will instantly insert blank columns according to your specified interval, see screenshot:
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Insert blank column every other or nth column by VBA code
For advanced users or large datasets, VBA can automate the insertion of blank columns. Here’s a VBA macro that inserts blank columns every other or nth column.
- Hold down the "ALT + F11" keys in Excel, and it opens the "Microsoft Visual Basic for Applications" window.
- Click "Insert" > "Module", and paste the following code in the Module Window.
VBA code: Insert specific number of blank columns into data at fixed intervalsSub InsertColumnsAtIntervals() 'Updatedby Extendoffice Dim WorkRng As Range Dim xInterval As Integer Dim xCols As Integer Dim xColsCount As Integer Dim xNum1 As Integer Dim xNum2 As Integer Dim xWs As Worksheet Dim xTitleId As String Dim i As Integer xTitleId = "Kutools for Excel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Select column range:", xTitleId, WorkRng.Address, Type:=8) xColsCount = WorkRng.Columns.Count xInterval = Application.InputBox("Enter column interval:", xTitleId, 1, Type:=1) xCols = Application.InputBox("How many blank columns to insert at each interval?", xTitleId, 1, Type:=1) xNum1 = WorkRng.Column + xInterval xNum2 = xCols + xInterval Set xWs = WorkRng.Parent For i = 1 To Int(xColsCount / xInterval) xWs.Range(xWs.Cells(WorkRng.Row, xNum1), xWs.Cells(WorkRng.Row, xNum1 + xCols - 1)).EntireColumn.Insert xNum1 = xNum1 + xNum2 Next MsgBox "Columns inserted successfully!", vbInformation End Sub - After pasting this code, press F5 key to run this code. In the prompt box, select the data range where you want to insert blank columns. Then, click OK, see screenshot:
- Another prompt box will pop out, type the number of column interval. And then, click OK, see screenshot:
- In the following prompt box, type the number of blank columns that you want to insert. Then, click OK, see screenshot:
Now, the specific number of blank columns are inserted into the existing data at the regular interval you specified, see screenshot:
In summary, inserting blank columns every other column or at specific intervals in Excel can be achieved using various methods depending on your needs and dataset size. By choosing the method that best suits your workflow, you can significantly improve efficiency and streamline your Excel tasks. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.
Best Office Productivity Tools
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.
- 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