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
- Quickly add a specific number of columns to right or left
- Insert one column or multiple columns in a table
- Insert a column to right the of the selected one with VBA
Video: Add columns
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 Insert from the context menu.
Tip: Hold Shift and Ctrl key, then press + on the main keyboard will do the same operation as the Insert command.
Result:
- After selecting columns, you also can go to ribbon and click Home > Insert to 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 Ctrl key 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: Specify the adding position and numbers
-
Right click at a column header, for example, click 'B' to select column B, then choose Add Columns;
-
Specify the position and the number of columns, click Ok.
Result:
Notes
-
The Add to Right feature will help you directly add columns to the right of the selected columns, the adding method is the same as the built-in Insert command.
-
For using Insert Columns, you should install Kutools for Excel firstly, please click to download and have a 30-day free trial now.
Insert a column or multiple columns in a table
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 Left option as you want.
Result:
Notes:
-
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 right option 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 left command 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.
Sub InsertToRight()
'Extendoffice20230303
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
xRg2.Insert Shift:=xlToRight
End Sub
Result:
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!