4 Easy Ways to Add Columns in Excel (Step by Step)
Adding a column or multiple columns in Excel is a common task when you are working with Excel data. This tutorial provides 4 easy ways, including shortcuts, to quickly insert one or multiple columns (to the right or left of the selected one).
Insert one column or multiple columns with built-in features
Step 1: Select the position you want to insert at and the number of columns you want to add
For example, to insert one column in the left of column B, please click the column B header to select the whole column B; To insert two columns in the left of column B, click the column B header, then hold to drag the mouse to the right to select two columns (column B and column C). By that analogy, you can add any number of columns as you need.
Step 2: Right click to insert columns
After selecting columns, right click to select Insertfrom the context menu.
Tip: Hold Shift and Ctrlkey, then press +on the main keyboard will do the same operation as the Insert command.
After selecting columns, you also can go to ribbon and click Home> Insertto quickly insert columns.
If you only select a range but not the whole column, whatever you use shortcuts or the right-click Insert command, a dialog will pop out, choose Entire column option and click OK.
By default, the adding column will be formatted as the same formatting as the left column of the selected one. If you do not want the formatting to be applied, or want to use the formatting of the selected one, you can select the Insert Options (a brush appears at the top right corner of the adding column) after adding, and choose from one of the options as follows:
If you want to add multiple nonadjacent columns, select one column then hold Ctrlkey to select others, and then use Insert command to add columns.
Quickly add a specific number of columns to right or left
To add columns in Excel using the built-in features, you need to select the related number of columns by dragging your mouse. However, by using the Add Columns feature of Kutools for Excel, you can quickly add columns to the right or left by entering the number of columns you want to add.
Step 1: Select the same number of cells with the number of columns that you want to insert
Suppose you wants to insert two columns in the left of "Sex" column, select two cells across "Sex" and "Age" columns (B2:C2).
Tip: If you want to insert a column, just select a cell in the table column next to where you want to add the column.
Step 2: Right click to insert new column
Right click at the cells you have selected, in the context menu, click Insert, then in the sub menu, choose Table Columns to the Leftoption as you want.
Tables support to insert one column to the right of the last column. Select a cell of last column in the table, right click to select Insert, you can see Table Column to the rightoption appears.
By default, the added column will be formatted as the same formatting with the left column of the selected one.
If you do not want the formatting to be applied, or want to use the formatting of the selected one, you can select the Insert Options (a brush appears at the top right corner of the adding column) after adding, and choose from one of the options as follows:
Using Table Column to the right or Table Column to the leftcommand only add columns within the table layout, which will not affect the data outside the table.
Table does not support to add nonadjacent columns.
Insert a column to the right of the selected one with VBA
Excel's built-in features only allow to add columns to the left of the selected column, if you want to add a column to the right of the selected one, a VBA code is provided to handle this job.
Step 1: Select a cell to the right of which you want to insert a column
Step 2: Press the Alt and F11 keys to enable Microsoft Visual Basic for Applications window, then click Insert > Module to create a new blank module
Step 3: Copy below VBA code and paste it to the new module, then click the Run button or press F5 key to run the code.
Dim xRg, xRg2 As Range
Dim xC As Long
Set xRg = Application.Selection
xC = xRg.Columns.Count
Set xRg2 = xRg.Columns.Item(xC)
Set xRg2 = xRg2.Offset(0, 1).EntireColumn
Note: The added column will be formatted as the same formatting as the selected one column.