Insert blank column every other column in Excel – 4 easy ways
When managing and analyzing data in Excel, you may often come across situations where it is necessary to insert a blank column after every other column or at specific regular intervals. This can be useful for improving the readability of your worksheet, preparing data for imports or exports, or accommodating additional calculations and comments. While inserting blank columns manually is practical for small datasets, it becomes time-consuming and error-prone for larger spreadsheets. In such cases, finding effective and scalable solutions is crucial to maintaining your workflow’s efficiency. This article introduces and explains several practical methods for achieving this task in Excel, catering to different user preferences and data sizes.
Insert blank column every other or nth column in Excel
- With the shortcut key
- By using 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 data tables or quick adjustments, manually inserting blank columns with a keyboard shortcut is straightforward and effective. This method works best when the number of columns is limited and you can directly select the required columns. Follow the procedure below to insert blank columns at regular intervals:
- Select the first column to the right of where you want to insert a blank column by clicking its column letter in the header area.
- Next, press and hold the Ctrl key on your keyboard, and while holding it down, click the headers of every column where you want a blank inserted. See screenshot:
- Once all target columns are highlighted, press Ctrl + Shift + Plus (+) keys together. Excel will instantly insert blank columns to the left of every selected column, allowing you to quickly create spacing between existing data. See screenshot:
This shortcut key approach is highly recommended for quick tasks with moderate data sizes. One limitation, however, is that manually selecting many columns across large datasets can be inefficient or error-prone. In such cases, consider using one of the automated options below.
Insert blank column every other or nth column by a helper column and the Sort feature
For larger or more structured datasets, you can use a logical approach that leverages a helper row in combination with Excel’s Sort feature. By arranging your data based on a custom numbering system, you can automate the process of creating blank columns at your chosen intervals. This method is very flexible and doesn’t require any add-ins or advanced technical skills, making it a good fit for most users working with standard Excel functionality.
- Insert a new blank row immediately above your dataset. This row will temporarily serve as a “helper” row.
- In the first two cells of the helper row (for example, cells A1 and B1), enter the numbers 1 and 2. Highlight both cells, then drag the fill handle (located at the bottom-right corner of your selection) to the right, all the way to the last cell of your data. This will generate a consecutive number sequence, as illustrated below:
- Select and copy the generated sequence numbers, then paste them immediately to the right of the last number in the same row. This pasted section will represent the placeholders for the new blank columns.
- Select the entire range of your data, including the helper row. Then, navigate to the Excel ribbon and click the Data tab, followed by the Sort option. See screenshot:
- In the dialog that appears, click the Options button to further refine your sorting settings.
- Choose the Sort left to right option, which enables sorting by rows rather than columns, and click OK to return to the main sort dialog.
- From the Sort by drop-down menu, pick the helper row you just created (e.g., “Row1”). Leave other sort settings unchanged, then click OK.
- Your dataset will be rearranged according to the values in the helper row, resulting in blank columns being interleaved with your original data as specified.
- Finally, delete the helper row if you no longer need it.
- To insert a blank column after every two columns, enter 2 in F1 and 4 in G1 (for example). Select those two cells and use the fill handle to extend the sequence to the right. Make sure the final number matches or exceeds the number of data columns you have.
- The same principle applies to inserting a blank column after every three columns: in this scenario, enter 3 in F1, 6 in G1, and continue accordingly.
- If you need to insert two or more consecutive blank columns between existing columns, simply copy and paste the helper number list two or more times immediately after the last cell of your existing sequence.
This method allows for great flexibility and is perfect for large datasets without requiring programming knowledge. One consideration is to ensure you carefully build your helper row to match your intended insertion pattern. For extremely large tables, sorting may take a moment to process.
Insert blank column every other or nth column by by only a few clicks
If you want a rapid and user-friendly solution, especially when dealing with large datasets or when frequent insertion of blank columns is part of your workflow, Kutools for Excel provides a streamlined approach. Kutools comes with tools to automate the insertion of blank columns at custom intervals, making your work both simpler and more reliable. With this utility, you can easily define how many blank columns to insert and how often, without risking mistakes or spending extra time on repetitive tasks.
Once you have downloaded and installed 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 blank columns to be placed.
- Select Blank Columns as the insertion type.
- 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.)
- Click OK to apply your settings.
Kutools will instantly insert blank columns according to your specified interval, see screenshot:
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
The Kutools approach is ideal when you frequently need to manipulate structure across large or complex worksheets. It reduces human error risk and vastly improves speed over manual and formula methods. As a reminder, always review your selections before confirming, and be aware of merged cells or hidden columns that may affect placement.
Insert blank column every other or nth column by VBA code
For users comfortable with automation or those managing very large and repetitive tasks, using VBA macros offers a highly efficient solution. With a simple custom macro, you can precisely control where and how many blank columns to insert, without manual intervention. Below is a detailed guide for inserting blank columns at regular intervals with VBA.
- Press ALT + F11 in Excel to open the Microsoft Visual Basic for Applications (VBA) window.
- Go to Insert > Module, and in the newly created module, paste the following code:
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 you paste the code, press F5 to run it. A dialog box will prompt you to select the data range where you want to insert blank columns. Select your target columns and click OK.
- Next, a prompt will ask for the column interval (i.e., after how many columns should blank columns be added). Type the desired interval and click OK.
- Another prompt will ask for the number of blank columns you wish to insert at each interval. Enter your number and click OK.
After completing these steps, the macro automatically adds blank columns to your dataset according to your specifications.
In summary, inserting blank columns at set intervals in Excel can be accomplished using various strategies to suit different needs and data volumes. Manual, Sort-helper, Kutools, and VBA solutions all offer unique advantages—choose the one that best aligns with your workflow requirements and technical preferences. Utilizing these techniques not only streamlines your Excel processes but also helps prevent mistakes and repetitive work. If you wish to explore even more ways to work efficiently with your Excel data, 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