Share via

Excel can't insert new cells because it would push non-empty cells off the end of the spreadsheet

Anonymous
2019-01-24T12:31:52+00:00

Split from this thread.

Greetings,

Just FYI, I had the same problem occur when trying to insert a column. In my case the problem was that I had merged cells in the location where I was trying to insert (Actually an entire row that was merged for a header). I removed the merge and then was able to insert columns again.

The full error message I was getting was: 

"Microsoft Excel can't insert new cells because it would push non-empty cells off the end of the worksheet". These 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.".

While the error message did hint at formatting or a formula being a possible cause, it then went on to recommend a solution (Delete rows or columns) that would not solve the problem. 

It seems like in my instance, there should be a different message that indicates the problem was with merged cells, as I spent quite a bit of time looking for columns/cells that might have had problematic data. It seems like Excel should be able to differentiate between problems caused by formatting or problems caused by data values. My two cents...

Thanks,

Mark

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2019-01-28T10:03:15+00:00

    Hi MLH57,

    According to your description, the error message only happens when you have an entire row merged.

    We'd like to explain the following:

    From this related article Excel specifications and limits we can see that Excel has its column and row limits.

    We suggest you try steps below to try understanding the cause:

    1.Type something in A1 cell

    2.Select B1 cell, then CTRL + SHIFT + RIGHT

    3.You should find the end of the column named “XFD”. This is the maximum column.

    Then please do test below:

    1.Select XFC1 cell, CTRL + SHIFT + LEFT, then go to Home > Merge & Center to merge the cells except the last cell “XFD1”.

    2.Try inserting new columns, you will find you can do that. But the last column is always the “XFD”.

    3.Then please type something in column “XFD” this time.

    4.Then inserting new column again. You will find you can get the error notification as well in this scenario.

    So when we merge the entire row, all the columns are recognized as being used. No more columns can be created to over the column maximum limit.

    Best Regards,

    Tina

    80+ people found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-01-24T17:01:24+00:00

    Hi Tina,

    Upon further investigation, it appears in my case the issue was caused by having an entire row merged. I tried merging just several row cells, and the issue did not reproduce when I then tried to insert a new column, even when I tried to insert a column that was within the merged cells range. However it will reproduce if you select an entire row and then merge all the cells. 

    It should be easy to reproduce as follows:

    1. Open a New Sheet
    2. Select the first row
    3. Right Click, and select Format Cells
    4. Click on the Alignment tab, and select Merge Cells
    5. Click OK
    6. Select a column (e.g. select column E )
    7. Right click and select Insert. At this point you should get the error.

    Note:

    I am using Office 365 (Version 1812 Build 11126.20266). Let me know if you still need a full screenshot, but presumably you were looking for the Version/Build information.

    I replied to your private message, but thought I should also post back to this thread so that this might help others if they encounter the same issue. 

    Hope this helps,

    Mark

    6 people found this answer helpful.
    0 comments
  2. Anonymous
    2019-01-30T12:14:27+00:00

    Hi Mark,

    Let us know if you need further help after referring to the suggestion.

    Best Regards,

    Tina

    5 people found this answer helpful.
    0 comments
  3. Anonymous
    2019-01-24T15:04:43+00:00

    Hi MLH57,

    Thanks for sharing your experience with us.

    Regarding the error message, you may refer to the suggestions in this thread.

    Moreover, to troubleshoot the issue, could you help to confirm the following information?

    1.To check if we can reproduce the same behavior in the same version of Excel, please share with us a full screenshot of Product Information via File>Account.

    2.Does the issue happen in a specific workbook or all workbooks?

    3.Could you help to record a sample video, it would be helpful for us. Thanks for your time and effort.

    Regards,

    Tina

    4 people found this answer helpful.
    0 comments
  4. Anonymous
    2019-01-25T18:56:01+00:00

    Hi Mark,

    Thanks a lot for sharing the detailed information. We will test and investigate the behavior on our side, when we get any update, we will post back and let you know.

    Best Regards,

    Tina

    1 person found this answer helpful.
    0 comments