Insert multiple columns

How to insert multiple columns in a worksheet using Excel, VBA and Shortcut methods

METHOD 1. Insert multiple columns by selecting entire columns

EXCEL

Select the number of columns you want insert > Right-click anywhere on the selected columns > Click Insert

1. Select the number of columns you want to insert.
Note: in this example we are inserting three new columns in columns B, C and D. The new columns will be inserted in the same columns that you have selected. To select entire columns, either click on the first column heading and drag to the side until you reach the number of columns you want to insert or select the first cell of the column, press and hold the Ctrl and Shift keys and press the Down key, then release the Ctrl key (still holding the Shift key) and press the Right or Left key to select the number of new columns you want to insert.
Select multiple columns

2. Right-click anywhere on any of the selected columns and click Insert. Right-click and click Insert

METHOD 2. Insert multiple columns using the ribbon option

EXCEL

Select multiple cells > Home tab > Cells group > Insert > Insert Sheet Columns

1. Select the cells where you want to insert new columns.
Note: in this example we are inserting three new columns in columns B, C and D. The new columns will be inserted in the same columns of the selected cells. You can select multiple cells across separate columns and rows (e.g. B4, E7, G9) which will insert new columns in columns B, F and I. The reason why Excel doesn't insert the new columns in the nominated column references is because the selected cells, after the first selected cell, would have shifted rightwards. In this example Excel will inserts the first column in column B, then moves to the next cell's reference which has shifted right by one column and inserts a new column in column F. With the third column, G9, it would have shifted rightwards by two columns and hence the new column will be inserted in column I.
Select multiple cells across multiple columns

2. Select the Home tab. Select Home tab - Excel

3. Click Insert in the Cells group.
4. Click Insert Sheet Columns.
Click Insert and click Insert Sheet Columns

METHOD 3. Insert multiple columns using the cell option

EXCEL

Select multiple cells > Right-click on any of the selected cells > InsertEntire column> OK

1. Select the cells where you want to insert new columns.
Note: in this example we are inserting three new columns in columns B, C and D. The new columns will be inserted in the same columns of the selected cells. You can select multiple cells across separate columns and rows (e.g. B4, E7, G9) which will insert new columns in columns B, F and I. The reason why Excel doesn't insert the new columns in the nominated column references is because the selected cells, after the first selected cell, would have shifted rightwards. In this example Excel will inserts the first column in column B, then moves to the next cell's reference which has shifted right by one column and inserts a new column in column F. With the third column, G9, it would have shifted rightwards by two columns and hence the new column will be inserted in column I.
Select multiple cells across multiple columns

2. Right-click on any of the selected cells.
3. Click Insert
Right-click on any of the selected cells and click Insert

4. Select the Entire column option and click OK group. Select Entire column and click OK

METHOD 1. Insert multiple columns using VBA by selecting a multiple cells

VBA

Sub Insert_Multiple_Columns()
'insert multiple columns as columns B, C and D
Worksheets("Sheet1").Range("B2:D2").EntireColumn.Insert

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.

ADJUSTABLE PARAMETERS
Columns Selection: Select where you want to insert new columns by changing the column references ("B2:D2"). The column selection doesn't need to be in a single range. You can replace the range reference with, for example, ("B4,E7,G9") which will insert new columns in columns B, F and I. The reason why Excel doesn't insert the new columns in the nominated column references is because the selected cells, after the first selected cell, would have shifted rightwards. In this example Excel will inserts the first column in column B, then moves to the next cell's reference which has shifted right by one column and inserts a new column in column F. With the third column, G9, it would have shifted rightwards by two columns and hence the new column will be inserted in column I.
Worksheet Selection: Select the worksheet where you want to insert new columns by changing the Sheet1 worksheet name.

METHOD 2. Insert multiple columns using VBA by selecting an entire columns

VBA

Sub Insert_Multiple_Columns()
'insert multiple columns as columns B, C and D
Worksheets("Sheet1").Range("B:D").EntireColumn.Insert

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.

ADJUSTABLE PARAMETERS
Columns Selection: Select where you want to insert new columns by changing the column references ("B:D"). The column selection doesn't need to be in a single range. You can replace the range reference with, for example, ("B:B,E:E,G:G") which will insert new columns in columns B, F and I. The reason why Excel doesn't insert the new columns in the nominated column references is because the selected cells, after the first selected cell, would have shifted rightwards. In this example Excel will inserts the first column in column B, then moves to the next cell's reference which has shifted right by one column and inserts a new column in column F. With the third column, G, it would have shifted rightwards by two columns and hence the new column will be inserted in column I.
Worksheet Selection: Select the worksheet where you want to insert new columns by changing the Sheet1 worksheet name.

Insert multiple columns using a Shortcut

SHORTCUT

WINDOWS SHORTCUT
Method 1

Ctrl
Shift
+

Method 2

Ctrl
+

NOTES

To insert multiple columns using these shortcut methods you will need to select entire columns. If you select individual cells or a range of cells and action these shortcuts an Insert dialog box will appear and you will need to select Entire column and click OK.

The Plus Sign key in the first method refers to the key on the top of the keyboard. The Plus Sign key in the second method refers to the key to the right of the keyboard, which some devices will not have. The reason why the first method requires the use of the Shift key is because the Plus Sign key is used for both Plus and Equal Signs, therefore, to activate the Plus Sign you are required to use the Shift key.

Explanation about how to insert multiple columns

EXPLANATION

EXPLANATION
This tutorial explains and provides step by step instructions on how to insert multiple columns in a worksheet using Excel, VBA and Shortcut methods.

Excel Methods: Using Excel you can insert multiple columns by selecting entire columns, multiple cells and using a ribbon or cell option.

VBA Methods: Using VBA you can insert multiple columns by referencing to a multiple cells or entire columns.

Shortcut Methods: Using a Shortcut you can instantly insert multiple columns by selecting entire columns where you want to insert new columns and actioning the shortcut.

ADDITIONAL NOTES
Note 1: Inserting new columns will move the existing columns, that are to the right of the new columns, rightwards. In this this tutorial every column to the right of column D will be moved to the right by three columns.
Note 2: To insert multiple columns, for example three new columns, you will need to ensure that the every cell in the last three columns of the worksheet are clear of any content, otherwise Excel will not permit you to insert the columns.

RELATED TOPICS

Related Topic Description Related Topic and Description
How to insert a single column in a worksheet using Excel, VBA and Shortcut methods
How to delete a single column in a worksheet using Excel, VBA and Shortcut methods
How to delete multiple columns in a worksheet using Excel, VBA and Shortcut methods
How to insert a single row in a worksheet using Excel, VBA and Shortcut methods
How to insert multiple rows in a worksheet using Excel, VBA and Shortcut methods