Prantick Bala
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.
Download Practice Workbook
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.
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.
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.
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.
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.
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.
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.
Read More: Combine Texts from Two Columns in Excel (6 Easy Tips)
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.
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.
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.
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.
➤ 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.
Read More: Excel Fix: Insert Column Option Greyed out (9 Solutions)
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. For doing 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.
Now,
➤ Go to Home > Editing > Clear and select Clear All.
It will remove all the contents and formatting from the selected cells and will show the beginning of your datasheet.
After that,
➤ Right-click on the column number of a column.
A drop-down menu will appear.
➤ Click on Insert on this menu.
As a result, 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 (6 Methods)
2. Unmerge Cells of Full Merged Row
When you cannot insert a column due to a full 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.
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.
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.
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 for removing panes from this article.
➤ Go to View > Freeze Panes and select Unfreeze 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.
Now, you will be able to insert a new column in your worksheet.
Read More: Shortcuts to Insert Column in Excel (4 Easiest Ways)
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.
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.
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.
You need to repeat all the steps of the 1st method to clear the unused cells.
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.
Read More: How to Insert Column without Affecting Formulas in Excel (2 Ways)
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.
Now, you will be able to insert a new column in your worksheet.
Read More: How to Insert Column with Excel VBA (4 Ways)
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.
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.
After copying to a new sheet, you can insert a new column without any disruption.
7. Clear the Used Range Using VBA to Insert a Column
You can also solve the problem of not inserting new columns by using the Microsoft Visual Basic Applications (VBA). First,
➤ Press ALT+F11 to open the VBA window and after that, press CTRL+G to open the Immediate window.
➤ Paste the following code in the Immediate window and press ENTER.
ActiveSheet.UsedRangeThe code will ensure that the worksheet’s used range is limited to the area where your data is located.
➤ Close the VBA window.
Now, you will be able to insert a new column in your worksheet.
Read More: Insert Column with Name in Excel VBA (5 Examples)
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.
