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

Get FREE Advanced Excel Exercises with Solutions!

In Excel, you may need to insert a column for adding new data. But sometimes you cannot insert a column in Excel in spite of your needs. In this article, I’ll show you possible causes and solutions to this problem.

Suppose, you have the following dataset where you want to insert a new column to add some additional data. Now, I will show you when you cannot insert a column in this dataset and how to solve it.

DATASET


When You Cannot Insert Column in Excel?

1. Content in the Last Column

If you have any content in the last column of your Excel worksheet, then you cannot insert a column in this worksheet. Suppose, you have the following text in the last column of your Excel worksheet shown in the image.

last cell

Now, if you try to insert a new column an Error message box will appear saying, “Microsoft Excel can’t insert new cells because it would push non-empty cells off the end of the worksheet. These non-empty cells might appear empty but have blank values, some formatting, or a formula. Delete enough rows or columns to make room for what you want to insert and then try again”.

So, when you have a non-blank last column you cannot insert a new column in your dataset. You cannot also shift non-blank cells in this case, you can know about that from this article.

CANNOT INSERT COLUMN IN EXCEL


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 > Borders and click on Outside Borders to add borders outside of your entire datasheet.

all borders

Now, if you try to insert a new column an error message box will appear and you won’t be able to insert the new column.

CANNOT INSERT COLUMN IN EXCEL


3. Cannot Insert Column for Full Merged Row

If you merge all the cells of a column in your datasheet, you cannot insert a new column in the datasheet. Let’s merge all the cells of the 3rd row of the following datasheet and see what happens.

merge cell

To merge all the cells of row 3,

➤ Select all the cells of row 3 by clicking on the row number.

➤ Go to the Home tab and click on Merge and Center in the Alignment ribbon.

merge cell

It will merge all the cells in row 3. Now, if you try to insert a new column, the error box will appear implying that you cannot insert a new column in this datasheet.

CANNOT INSERT COLUMN IN EXCEL


4. Cannot Insert Column in Excel for Panes

If you have panes in your worksheet, you won’t be able to insert a new column.

CANNOT INSERT COLUMN IN EXCEL


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.

CANNOT INSERT COLUMN IN EXCEL

I’ll show you how to find out if your datasheet has conditional formatting for the entire sheet and how to remove it when I will discuss the solutions. So, hang with the article.


6. Cannot Insert Column for 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.

CANNOT INSERT COLUMN IN EXCEL

A new window named Protect Sheet will appear.

Now, if you uncheck the box Insert columns and click on OK, you won’t be able to insert a new column in the sheet.

CANNOT INSERT COLUMN IN EXCEL

➤ Right-click on the top of a column.

You will see the Insert option is greyed out. That means you cannot insert a column in this protected worksheet.

CANNOT INSERT COLUMN IN EXCEL


What to Do When You Cannot Insert Column in Excel?

Now, I will show what you can do to solve the problem when you cannot Insert a column in Excel.


1. Clear All the Columns Outside of the Dataset

If you clear all the contents and formatting of the columns outside of your dataset, the last column of your dataset will be totally blank and you will be able to insert a new column. To do this, first,

➤ Select the first cell of the first empty column, press CTRL+SHIFT+RIGHT ARROW key, and then CTRL+SHIFT+DOWN ARROW key.

It will select all the cells of the worksheet outside of your dataset.

unused cells

Now,

➤ Go to Home > Editing > Clear and select Clear All.

clear content

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

clear unused cells

After that,

➤ Right-click on the column number of a column.

A drop-down menu will appear.

➤ Click on Insert on this menu.

insert

As a result, you will see a new column will be inserted on the left of the selected column.

new column

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


2. Unmerge Cells of Full Merged Row

When you cannot insert a column due to a fully merged row, you need to unmerge the row first.

➤ Select the merged row by clicking on the row number, then Go to Home > Merge and Center and select Unmerge Cells.

unmerge cells

It will unmerge all the cells of that row. Now, you need to repeat all the steps of the 1st method to clear the contents outside of your dataset.

After clearing the outside contents, your problem will be solved and you will be able to insert a column in your Excel datasheet.

CANNOT INSERT COLUMN IN EXCEL

You may want to merge the relevant cells of the row to make your dataset more presentable. You can find the ways of merging cells in this article.

CANNOT INSERT COLUMN IN EXCEL


3. Remove Panes to Insert Column in Excel

If your worksheet has panes you need to remove the panes to insert a new column. In the current article, I’ll show you one way to remove panes. You can find some other ways to remove panes from this article.

➤ Go to View > Freeze Panes and select Unfreeze Panes.

panes

It will remove the panes from your worksheet. Now, you need to repeat all the steps of the 1st method to clear the unused cells.

clear

Now, you will be able to insert a new column in your worksheet.

new column

Read More: Shortcuts to Insert Column in Excel


4. Remove Conditional Formatting from Entire Datasheet

If you have applied conditional formatting to the entire datasheet you have to remove this conditional formatting to add a new column. First, to check if the conditional formatting is applied to the entire dataset,

➤ Go to Home > Conditional Formatting > Manage Rules.

conditional formattiing

It will open the Conditional Formatting Rules Manager window. Now,

➤ Check the box Applies to for finding out 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. So, you have to remove this incorrect conditional formatting.

➤ Click on Delete Rule and then click on OK.

manage rules

As a result, the conditional formatting from the entire sheet will be removed. But even now you won’t be able to insert a new column. This is happening because you have to clear the unused cells before adding a new column.

CANNOT INSERT COLUMN IN EXCEL

You need to repeat all the steps of the 1st method to clear the unused cells.

CANNOT INSERT COLUMN IN EXCEL

After that, you will be able to add a new column to your datasheet.

If you want to apply conditional formatting to your dataset, you can do that now. You can find the ways to apply conditional formatting correctly from here.

CANNOT INSERT COLUMN IN EXCEL

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


5. Turn Off Sheet Protection to Insert Column

If the reason for not being able to insert a column is sheet protection, you can simply turn off the protection from the sheet.

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

CANNOT INSERT COLUMN IN EXCEL

Now, you will be able to insert a new column in your worksheet.

CANNOT INSERT COLUMN IN EXCEL

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


6. Copy Data to a New Worksheet

Another way of solving the problem is to copy your data and paste it into a new website.

➤ Select all the cells of your dataset and press CTRL+C.

CANNOT INSERT COLUMN IN EXCEL

Now,

➤ Go to a new worksheet, select a cell, and press CTRL+V.

It will paste your dataset into the new sheet.

➤ Click on the Paste sign on the bottom of your pasted cells.

A paste menu will appear.

➤ Click on the option of Paste with Keep Source Column Widths (W).

So, you don’t have to readjust the columns’ widths.

paste

After copying to a new sheet, you can insert a new column without any disruption.

CANNOT INSERT COLUMN IN EXCEL


7. Clear the Used Range Using VBA to Insert a Column

You can also solve the problem of not inserting new columns by using VBA Macro. First,

➤ Press ALT+F11 to open the VBA window and after that, press CTRL+G to open the Immediate window.

CANNOT INSERT COLUMN IN EXCEL

➤ 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.

CANNOT INSERT COLUMN IN EXCEL

➤ Close the VBA window.

Now, you will be able to insert a new column in your worksheet.

CANNOT INSERT COLUMN IN EXCEL

Read More: Excel Fix: Insert Column Option Greyed Out


Download Practice Workbook


Conclusion

I hope now you know when you cannot insert a column in Excel and what you can do to solve the problem. If you have any confusion please feel free to leave a comment.


<< Go Back to Insert Columns | Columns in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

2 Comments
  1. My problem seems to be different.
    I tried all the above methods but still I am not able to insert the column

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo