How to Add a Column in Excel

Usually, when working with a dataset in Excel, especially a large one, it’s quite normal to miss or require column (s) that need to be added later.

In the following example, the marks of the English subject are missing so we need to add a new column for it.

Inserting a new column at the end (after the Total Marks column) is relatively easy. But, what if you need to add it somewhere in the middle to accurately calculate the Total Marks?

On top of it, what if you need to add multiple columns at once?

How to Add Column on a Regular Cell Block (Non-Table Data)?

If you are looking to insert a column between regular columns, you can use the methods below. However, for a table, you can directly jump to the next section.

Using the Shortcut Key 

1. Click the main column header (A, B, C…) to select an entire column. Here, we are trying to add an “Address” column between the Name and Email columns.
   
2. Then, press the shortcut key Ctrl + Shift + + or Alt + I + C. For Mac users, use the shortcut key Cmd + Shift + + instead.

Using the Home Ribbon 

1. Select a column next to which you want to add the new column.
2. Click the Home tab.
3. Then, select Insert > Insert Sheet Columns. 
   

Using the Right-Click Method

1. Select a column or any cell under it and right-click on it.
2. Then, select Insert. Alternatively, use Insert > Entire column if you have selected certain cells only.
   
   

How to Add a Column in an Excel Table?

Inserting a column in an Excel table is pretty much the same, with some additional options. 

However, unlike in the regular cell block, you cannot add multiple new columns between non-adjacent columns at once. But, you can still re-arrange each column order after opening the table in the Power Query editor.

Using the Resize Handle

1. Hover the bottom-left corner of the last cell inside the table. You can use the keyboard shortcuts; Ctrl + Right arrow and Ctrl + Down arrow, to move between the last column and row of the table.
2. Hover over the cell border. Once the cursor turns into a small double-arrow icon, click and drag it on the right side to add one or more columns. 
   
   

Enter New Input

1. Just select any adjacent cell right next to the last column of the table.
2. Start typing the column header name or a cell input and press Enter.
   
3. Additionally, you can even paste cell values directly next to the table.

Using Resize Table Option

1. Select any cell inside the table where you want to add one or more columns.
2. Under the Table Design tab, click Resize Table.
   
3. On the next prompt, replace the old cell reference and drag across the table to add as many columns as you like.
   
4. Click OK when done.

Using the Home Ribbon

1. Select the table column header next to which you want to insert the new column.
2. Under the Home tab, select Insert > Insert Table columns to the Left.
   
3. Alternatively, choose the Insert Table columns to the right option if you are adding the new column next to the last column.
4. To add multiple columns, select two or more adjacent cells and insert columns using Step 2.

Using the Right-Click Method 

1. Select the column header next to which you want to insert the new column.
   
2. Right-click and select Insert > Table columns to the Left. Alternatively, you can just press the shortcut key, Alt + I + C.
3. To insert multiple columns, select the respective adjacent column header or any cells of such columns. Then, use the shortcut or right-click method.
   

Using Power Query

1. Select any cell on the table where you want to add new columns.
2. Then, under the Data tab, click From Table/Range. You can find it in the Get & Transform Data section.
   
3. On the Power Query editor, select the Home tab and click Enter Data.
   
4. On the Create Table window, select and replace the new default Column 1 column with your preferred column header name. Here, we are adding the Address column.
   
5. To add more tables, click the plus icon next to the new column.
   
6. Enter the new table name and click OK.
7. Now, click the Append Queries under the Home tab.
   
8. Next, select an appropriate option (Two tables, Three or more tables) depending on how many tables you want to combine.
9. Under the Table to append section, click the dropdown menu and select other tables to get a single table with the newly added columns.
   
10. Additionally, right-click on the new column header and select the Move option to rearrange its position. Or, just drag it across columns to the preferred position.
11. Also, select the appropriate type for the new field values next Data Type field. By default, every new column will have the TEXT value type.
12. Click Close and Load > Close & Load To.
    
13. On the next prompt, choose where you want the whole table with the new appended column (s) and click OK.

How to Add Multiple Columns in Excel?

Similar to adding a single column, you can even add multiple columns at once. 

As we have already covered steps for adding multiple columns in tables, you can follow the steps below for regular cell blocks.

1. Select the same number of columns you want to add. For instance, select two columns to insert two columns to the left, and likewise.
   
2. Press the shortcut key Alt + I + C. Or, right-click and select Insert > Entire Column.

How to Add a New Column without Affecting Other Columns/Fields?

The above methods will insert a column for the entire worksheet. Meaning, you get an entire column such as A, B, C, or other depending on where you have inserted the column.

By default, an Excel table prevents you from inserting a new field when you have other tables below.

However, this can become a major issue in cases where you have multiple cell blocks in the same worksheet. 

For instance, 

Here, we have two cell blocks with separate fields in the same worksheet. Now, if we try to add a column using the above methods, Excel could insert it for both of them.

As we can notice that we wanted the Age column only on cell block 1. But Excel inserted it for Cell Block 2 as well. 

To avoid this issue,

1. Select only the cells of the desired column/field. To select an entire field of a cell block, you can use the shortcut key Ctrl + Shift + Down arrow while selecting the first cell of the field (Email in this case.)
   
2. Now, right-click and select Insert. On the next window promo, choose Shift cells right. 
   
3. Alternatively, you can click the Insert button under the Home tab.
   

Related Questions

Why Am I Receiving an Error Message while Adding a New Column?

Although it’s rare, you may sometimes experience an error message such as “Microsoft Excel can’t insert new cells because it would push non-empty cells off the end of the worksheet”. 

This happens when there’s a non-blank cell under the last column of the worksheet. Also, even if the cells appear empty you could still get the error when they have some type of formatting. 

To resolve it, you have to clear the formatting or clear any values on such cells.

1. Press Ctrl + End to reach the last column on the worksheet.
2. Then, press Ctrl + Shift + End to select all cells of the column.
   
3. Now, under the Home tab, select Clear > Clear All.
   

How to Add the Number Values of a Column in Excel?

If you are looking for a shortcut to quickly calculate the total sum of the number values in a column, you can do so as follows.

1. Select the cells of that particular column.
2. Press Alt + =.