Cannot Insert Column in Excel (All Possible Causes with Solutions)

We’ll use the following dataset to try to insert a new column to add some additional data. We will show you why you can’t insert a column in this dataset and how to solve it.

When Can’t You Insert a Column in Excel?

Reason 1 – Content in the Last Column

If you have any content in the last column of your Excel worksheet, you can’t insert more columns. We have the following text in the last column of the Excel worksheet shown in the image.

If you try to insert a new column, an Error message box will appear.

Reason 2 – Outside or All Borders to the Entire Sheet

If you add outside or all borders by selecting the entire sheet, you won’t be able to insert a new column in this sheet. Let’s verify this.

• Select the Entire sheet by clicking on the top left corner of your worksheet where the row number intersects with the column number.
• Go to Home and then to Borders, then click on Outside Borders to add borders to the entire datasheet.

• If you try to insert a new column, an error message box will appear instead.

Reason 3 – Can’t Insert Column for Full Merged Row

If you merge all the cells of a row in your datasheet, you can’t insert a new column in the datasheet.

• We’ll merge all cells in row 3 by selecting the row and going to Merge & Center.

• If you try to insert a new column, the error box will appear.

Reason 4 – Can’t Insert Column in Excel with Frozen Panes

If you have frozen rows or columns in your worksheet, you won’t be able to insert a new column.

Reason 5 – Conditional Formatting to the Entire Sheet

If you accidentally apply conditional formatting for the entire worksheet instead of the cells of your dataset, you won’t be able to insert a new column in this worksheet.

Reason 6 – Can’t Insert a Column due to Sheet Protection

If you turn on Protection for your worksheet, you won’t be able to insert a column in the protected sheet.

• Right-click on the sheet name and click on Protect Sheet.

• A new window named Protect Sheet will appear.
• If you uncheck the box Insert columns and click on OK, you won’t be able to insert a new column in the sheet.

• You will see the Insert option is greyed out.

What to Do When You Cannot Insert Column in Excel?

Fix 1 – Clear All the Columns Outside of the Dataset

• Select the first cell of the first empty column.
• Press Ctrl + Shift + Right Arrow.
• Press Ctrl + Shift + Down Arrow.
• This will select all the cells of the worksheet outside of your dataset.

• Go to Home, then to Editing.
• Select Clear and choose Clear All.

• It will remove all the content and formatting from the selected cells and will show the beginning of your datasheet.

• Right-click on the column number of a column.
• Click on Insert.

• You will see a new column will be inserted on the left of the selected column.

Read More: How to Insert a Column to the Left in Excel

Fix 2 – Unmerge the Cells of a Fully Merged Row

• Select the merged row by clicking on the row number.
• Go to Home then to Merge and Center.
• Select Unmerge Cells.

• Repeat Fix 1 to remove all content outside of your dataset.

• You can merge the cells back if you want to.

Fix 3 – Remove Frozen Panes to Insert Column in Excel

• Go to View and select Freeze Panes, then choose Unfreeze Panes.

• Clear the unused cells via Fix 1.

• You will be able to insert a new column in your worksheet.

Read More: Shortcuts to Insert Column in Excel

Fix 4 – Remove Conditional Formatting from Entire Datasheet

• Go to Home then select Conditional Formatting.
• Choose Manage Rules.

• This will open the Conditional Formatting Rules Manager window.
• Check the box Applies to for each rule to find the cells where the conditional formatting is applied. If you see a very large number in this box, it means you have applied conditional formatting to all the cells of the datasheet.
• Choose that rule and click on Delete Rule.
• Click on OK.

• You might still get an error message.

• Repeat all the steps of Fix 1 if needed.

• Add a new column and reapply conditional formatting if you want.

Read More: How to Insert Column without Affecting Formulas in Excel

Fix 5 – Turn Off Sheet Protection to Insert a Column

• Right-click on the sheet name from the status bar and click on Unprotect Sheet.

• You may need to enter a password that you set up while turning on Sheet Protection.
• You will be able to insert a new column in your worksheet.

Read More: How to Insert a Column Between Every Other Column in Excel

Fix 6 – Copy the Data to a New Worksheet

• Select all the cells of your dataset and press Ctrl + C.

• Open a new Excel sheet.
• Select any cell and press Ctrl + V.
• Click on the Paste sign at the bottom of your pasted cells.
• Click on the option Paste with Keep Source Column Widths (W).

• You can insert a new column without any disruption.

Fix 7 – Clear the Used Range Using VBA to Insert a Column

• Select the dataset.
• Press Alt + F11 to open the VBA window.
• Press Ctrl + G to open the Immediate window.

• Paste the following code in the Immediate window and press Enter.

ActiveSheet.UsedRange

• The code will ensure that the worksheet’s used range is limited to the area where your data is located.

• Close the VBA window.
• You will be able to insert a new column in your worksheet.

Read More: Excel Fix: Insert Column Option Greyed Out

Download the Practice Workbook

<< Go Back to Insert Columns | Learn Excel