Insert multiple columns
How to insert multiple columns in a worksheet using Excel, VBA and Shortcut methods
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. |
2. Right-click anywhere on any of the selected columns and click Insert. |
METHOD 2. Insert multiple columns using the ribbon option
EXCEL
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. |
2. Select the Home tab. |
3. Click Insert in the Cells group. 4. Click Insert Sheet Columns. |
METHOD 3. Insert multiple columns using the cell option
EXCEL
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. |
2. Right-click on any of the selected cells. 3. Click Insert |
4. Select the Entire column option and click OK group. |
Worksheets("Sheet1").Range("B2:D2").EntireColumn.Insert
End Sub
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
Worksheets("Sheet1").Range("B:D").EntireColumn.Insert
End Sub
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.
Method 1
Method 2
NOTES
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.
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 Topic | Description | Related Topic and Description |
---|---|---|
Insert a column | How to insert a single column in a worksheet using Excel, VBA and Shortcut methods | |
Delete a column | How to delete a single column in a worksheet using Excel, VBA and Shortcut methods | |
Delete multiple columns | How to delete multiple columns in a worksheet using Excel, VBA and Shortcut methods | |
Insert a row | How to insert a single row in a worksheet using Excel, VBA and Shortcut methods | |
Insert multiple rows | How to insert multiple rows in a worksheet using Excel, VBA and Shortcut methods |