Insert Column in Excel (All Possible Ways)

In this article, you will learn everything about how to insert a column in Excel. Throughout this article, we will discuss a total of 11 easy ways to insert columns in Excel. We will demonstrate some shortcuts to insert columns. Then we will use VBA to insert columns. Also, we will show how to insert columns in Excel tables.

While working in Excel, you might need to insert a new column if you miss it earlier. This is a really easy task and you can do it in various ways. In this article, we will show all possible techniques and methods to insert columns in Excel.

I hope you find this article informative and useful. So, let’s get into the main discussion.

Download Practice Workbook

You can download the practice workbook from here:

Inserting Column.xlsm

Insert Column in Excel: 11 Easy Ways

In Excel, adding columns is easy and there are several methods you can use. We will discuss 11 easy ways that you can follow to insert columns in Excel. To show you the ways we have taken a dataset consisting of 8 rows and 3 columns.

1. Insert a Column with a Right Click

To add a new column, select a column to the right.
Now, right-click anywhere in the selected column and choose Insert.

As a result, you can see that a new column has been inserted to the left of the Name column.

2. Using Shortcut Keywords to Insert a Column

In this part, we will use some shortcut keywords to insert columns in Excel.

2.1. Using SHIFT + CTRL + Equal (=) Shortcut

Suppose we want to add a column left to the “Name” header which is actually column C.
Select the entire Column C and press CTRL + SHIFT + Equal (=).

Thus a new column will be added to the left of the header “Name”.

2.2. Using Shortcut ALT + I, C

Select the entire column C.
Press ALT + I (do not hold the keys) and finally, press C.

As a result, a new column will be added to the left of the header “Name”.

3. Insert Multiple Adjacent Columns Using Keyboard Shortcut

We want to add two adjacent columns to the left of column C.
To do that, select column C and column D at once by right-clicking and dragging the mouse.
Then press CTRL + SHIFT + Equal (=).

Thus we have added two adjacent columns next to the “Name” column.

4. Insert Multiple Non-Adjacent Columns

Select column C.
Then hold the CTRL key and select column D.

Finally press CTRL + SHIFT + Equal (=).
This time, two new columns will be added, but they will not be adjacent to each other.
One will be to the left of the header “Name” and another one to the left of the “Sales” header.

5. Insert a Column from Home Tab

Click on the column to the right of your desired location. Here, we have selected column C.
Go to Home tab>> click on Insert under Cells section>> choose Insert Sheet Columns.

As a result, you can see that a new column has been inserted to the left of the Name column.

6. Insert a Column with a Click and Drag

Say we want to insert a column between ID and Name with a click and drag. To that follow the below steps:

Select any empty column. Let’s say column F.
Over the green border, a black cross with arrows appears.
Left click and drag while holding Shift.
Click and drag the green vertical line to the location where you’d like your new column to appear.
To give you a clear understanding we have added a demonstrative video.

Thus a new column has been inserted between the ID and Name columns.

Note: You can’t insert a new column between two columns if your dataset has any merged column header or heading in it. It will place the new column outside of your dataset like this.

7. Insert a Column Using Fill Handle

In this section, we will insert a column using the fill handle option at the top right of the selection. Suppose we have selected column C.

Select the entire column C.
On the top right, you will see the fill handle for column C.
Click on the plus icon over the fill handle.
As soon as you press and hold the Shift key, the icon will change to two bars with arrows.
Click and drag the fill handle left or right while holding the Shift We shifted right.
Finally, when you release, a new column will be inserted between the Name and Sales Columns. You can follow the below demonstrative video to accomplish the task.

Note: You can’t insert a new column between two columns if your dataset has any merged column header or heading in it. It will place the new column outside of your dataset like this.

8. Insert a Column with HSTACK Function

In this part, we will insert a column using the HSTACK function. Suppose we want to insert column F between columns C and D. To do that:

Select cell H4 and insert the following formula.

Thus you can insert the Age column between the Name and Sales columns.

9. Insert a Column with Power Query

In this part, we will discuss how to insert a column with a power query. You can easily add columns to your import query when you use a power query solution. To know more follow the below steps:

First, select your data>> go to Insert tab >> Table tool.
Check your data range. In our case, it is $B$4:$D$11.
Then click OK.

As a result, you will see your dataset converted into a table.
Now, to name this table, click on any value inside the table >> go to the Table Design tab >> Properties >> write Sales in the Table Name text box.

Now, right-click on any cell inside the Sales table and choose the Get Data from Table/Range option from the context menu.

You can see that the Sales table is shown in the Power Query.

Now go to Add Column >> select Columns From Examples >> choose From All Columns from Power Query Editor.

Now give your preferred column name. In our case, it is Region.
Enter sample values into the Region column.

As a result, you will see a new column added in the Power Query Editor window.

You can then close and load the query into Excel to see the new column “Region” added to your data.

Thus you can insert a new column with a power query in Excel.

10. Insert a Column with Power Pivot

If you want to insert a column with power pivot, follow these steps:

Go to Power Pivot tab>> choose Add to Data Model>> select data from your table.
Ensure that the check mark is next to “My table has headers”.
Click OK.

Therefore, the data preview in the editor always shows an empty column labeled Add Column.
By double-clicking on the heading, you can rename a column as you wish like the below image.

11. Insert Column with Excel VBA

In this section, we will show you how you can insert a single column or multiple columns within a data set using VBA in Excel.

11.1. Insert a Single Column

First, I will show you how you can insert a single column using Excel VBA. Let’s insert a new column between columns B and C. To do that:

Press ALT + F11 on your keyboard to open Visual Basic.
Then click Insert and select the Module option.
Insert the following code in the code editor and press F5 to run the entire code.

Sub VBA_Insert_Single_Column()
ActiveSheet.Range("C4").EntireColumn.Insert
End Sub

Here is the final output image.

11.2. Insert Multiple Columns

Now let’s insert multiple columns using VBA. Let’s say we want to insert 2 columns before column C.

Insert a new module and copy and paste the following code.
Press F5 to run the entire code, and your work will be done.

Sub VBA_Insert_Multiple_Columns()
Inserted_Columns = 2
For i = 1 To Inserted_Columns
ActiveSheet.Range("C4").EntireColumn.Insert
Next i
End Sub

Here is the final output image.

11.3. Insert Between Every Other Column

Suppose you want to insert a column between every other column in Excel using VBA macros. To do so follow the below steps:

Insert a new module and copy and paste the following code.
Press F5 to run the entire code, and your work will be done.

Sub Insert_Column_Between_Every_Other()
For Xcol = 3 To 8 Step 2
Columns(Xcol).Insert Shift:=xlToRight
Next
End Sub

Here is the final output image.

How to Insert a Column in an Excel Table

Suppose you have data in an Excel table and you want to insert columns in that table. For more information, please read the following part.

1. Insert a Column in the Middle of an Excel Table

Here, we will discuss how to insert a column in the middle of an Excel table.

Select and right-click any cell in the table.
Choose Insert option>> select Table Columns to the Left option.

As a result, you will see a new column appear to the left of your selected cell.

2. Insert a Column at the End of an Excel Table

It is also possible to add a column to the right end of the table. Suppose you want to add a column named Region to the end of the table. To do that follow the below steps:

Select cell E4 and type the column header name “Region” in the empty column.

Now just press Enter.
As a result, a new column will be created at the end of the table.

Limitations:

In a table, you cannot add columns with non-adjacent columns at the same time as you can in a range dataset.
It is not possible to add a column anywhere in the spreadsheet if you have converted the entire spreadsheet into a table rather than just the filled entries. This is due to the table not having enough space to add a new column, which is not the case with regular datasets.

Read More: Shortcut to Insert Column in Excel

Frequently Asked Questions

1. What happens to the data when I insert a column in Excel?

When you enter a column in Excel, the current data in the worksheet moves to the right to make room for the new column. The data in the shifted columns will be moved as well. Any calculations or references that rely on the shifted data will be immediately updated to reflect the new column.

2. What is COLUMN() in Excel?

In Excel, the COLUMN() function is a built-in function that returns the column number of a specific cell reference. It is often used in combination with other functions, such as INDEX(), MATCH(), or VLOOKUP(), to perform calculations or look up values based on column numbers.

3. Can I insert a column in a protected worksheet?

You might not have the right permissions to add a column to a protected file. To add a column, you need to unprotect the worksheet or ask the worksheet owner or administrator for the rights you need.

Key Takeaways from the Article

In this article, we have discussed a total of 11 easy ways to insert columns in Excel.
We have shown some shortcuts to insert columns in Excel.
We also explained how to insert columns using VBA macro.
Showed a step-by-step procedure of all methods.
Also showed how to insert columns in Excel tables.
Discussed some limitations of inserting columns in an Excel table.
Provide solutions to frequently asked questions by readers.

Insert Column in Excel: Knowledge Hub

Conclusion

This article covers every possible way to insert a column in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, ExcelDemy, a one-stop Excel solution provider, to explore more.

<< Go Back to Columns in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!