How-To

Excel Cannot Insert Column: 6 Fixes

By Adam Davidson

Published August 10, 2023

Struggling to add columns in Excel? If Excel can’t insert columns, follow this guide to fix the problem.

Curiosity is a dangerous thing. You can lose hours of your life scrolling your phone calendar to see just how far into the future it goes. If you’re an Excel user, perhaps you’ve tried to work out just how many Excel columns or rows there actually are.

For the majority of the time, the answer is that there are more than plenty. Sometimes, however, you may bump into issues where Excel seems to think that you’ve used up all the available columns, and it won’t let you add any more in.

If this sounds like you, then here are some steps to try if Excel cannot insert a column where you want one.

1. Delete Data from Excess Columns in Excel

One of the most common causes of not being able to insert a column in Excel is because Excel thinks that by doing so, it will destroy some of your data. This occurs when Excel thinks that you have already entered data in the maximum number of allowable columns.

In order to add another column, Excel would have to delete one of the current columns. It means that you can move columns, but if you try to add a new one, you’ll see a warning message that the action can’t be performed.

Often, however, this is usually just down to rogue data or cell formatting being found in all the columns, rather than actual useful data.

By deleting the content from any unneeded columns, you should be able to fix the issue. However, you’ll need to save and reopen the file before it will work.

To delete content from excess columns in Excel:

1. Find the last column that contains data that you want to keep.
2. Click the label of the next column to the right to select it.
   
3. Hold down Ctrl+Shift and press the Right Arrow key.
4. This will select all the columns to the right of the one you already selected.
5. Right-click on any of the highlighted columns, and select Delete. This will remove any content from these columns.
   
6. Save your file and close it. Until you do so, you’ll keep receiving the same error.
7. Re-open your document, and you should now be able to insert a new column without issue.

2. Unmerge Rows

Another reason why you may see this error is if you have merged two entire rows together. In this case, when you try to insert a column, there is no additional room as your merged cell already takes up the full width of the spreadsheet.

To fix this, unmerge the rows—you should be able to insert columns again.

To unmerge rows in Excel:

1. Highlight the merged rows by clicking on their row labels.
   
2. In the Home ribbon, click the Merge & Center button.
   
3. The cells should now unmerge.
4. You should be able to insert columns once more.

3. Unprotect the Document

If you don’t see the Microsoft Excel cannot insert new cells error, then the problem lies somewhere else. Another common reason that you can’t insert a column is because the spreadsheet has been protected to stop any changes being made to it.

By unprotecting the document again, you may be able to insert columns once more. If you try to insert a column, and you see most of the options are grayed out, this is a clue that your worksheet is protected.

To unprotect an Excel document:

1. Select the Home menu.
   
2. Click the Format button.
   
3. Select Unprotect Sheet.
   
4. Enter the password.
5. Once your sheet is unprotected, try inserting a column again.

Allowing New Columns in a Protected Excel Worksheet

If you want to keep your sheet protected, but still want to be able to insert columns, you can change the sheet protection settings.

To allow new columns in a protected Excel worksheet:

1. In the Home menu, click the Format button.
   
2. Select Protect Sheet.
   
3. Ensure that Insert Columns is checked.
   
4. Enter a password and press OK.
   
5. Confirm your password.
   
6. The worksheet is now protected, but you can still add new columns.

4. Unfreeze Panes

There may be cases where freezing panes can stop you from inserting any more columns into your spreadsheet.

By unfreezing panes in your sheet, you may be able to insert columns once more.

To unfreeze panes in Excel:

1. Click the View menu.
   
2. In the ribbon, click the Freeze Panes button.
   
3. Select Unfreeze Panes.
   
4. Save and close the document. The fix may not work if you don’t complete this step.
5. Open the document again, and you should be able to insert columns once more.

5. Convert Table to Range

If you have tables in your data, this may cause issues when trying to insert columns. If this is the case, you can convert your tables back into ranges again, and you should be able to insert columns as normal.

Although it can feel like Excel is one big table, multiple cells within Excel are considered a range. If you insert a table, it has specific formatting that is different from standard ranges, such as sortable column headers, banded rows, and calculated columns.

Inserting columns in a table adds another table column to the next column of cells, rather than inserting a new column into the worksheet.

To convert a table to a range in Excel:

1. Click any cell of your table.
2. In the menu bar, select the Table Design menu that appears.
   
3. Click the Convert to Range button.
   
4. Confirm your decision, and your table will be converted into a range.
5. You should now be able to insert columns as expected.
6. If you want to insert a new column into a table, right-click on one of the table column headers.
7. Select Insert > Table Columns to the Left.
   
8. A new column will be added to your table, but not to the worksheet.

6. Copy Data to a New Sheet

If all else fails, you can try copying that data you want to use and pasting it into a new worksheet. This may ensure that no erroneous data or formatting gets carried over that could stop Excel from allowing you to insert any more columns.

To copy your Excel data to a new sheet:

1. Select all the data that you want to copy. To quickly select a number of columns, click the header of the first, hold down the Shift key, and click the column header of the last column you want to include.
2. Right-click any of your selected cells, and select Copy.
   
3. Create a new sheet by clicking on the New Sheet icon.
   
4. Right-click in the cell where you want the data first piece of data to appear, and select Paste.
   
5. Once your data is in your new sheet, try inserting a new column.

Editing Your Excel Spreadsheet

If you’re having issues because Excel cannot insert a column, then hopefully one of the fixes above can help. Please let us know in the comments below if you discover any other useful ways to fix the issue.

Sometimes getting Excel to do what you want it to can be a challenge. There are some useful tips that can help, however. There are some steps you can try if you’re having problems with Excel formula references, or if you’ve ended up with some circular references.

And there are some useful fixes to try if you find that Excel keeps crashing.