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

Question

Excel incorrectly reaches that conclusion (see title of post) for me semi-regularly on some very large, complicated Excel files.  The standard recovery tips I've seen don't work:

Fix 1 - Go to last intentionally populated column.  Select next column.  Ctrl-shift-arrow-right to select all columns meant to be empty.  Right-click > Clear Contents.  Repeat for rows (Go to last intentionally populated row.  Select next row.  Ctrl-shift-arrow-down to select all columns meant to be empty.  Right-click > Clear Contents.)

... This does not work.  You can confirm this by typing ctrl-end, which goes to last used cell, and this is always last-possible column in the spreadsheet.  Also, trying to insert a column produces the same error message.

Fix 2 - Another fix I've seen posted says that an occasional bug in Microsoft Excel (for years), once active, will always & immediately regenerate a false value for last-used cell, (no many how many times, or in what manner you clear it) if the row heights have been manually changed.  The posted fix is to select the whole sheet (ctrl-a), then auto-set the row heights by double-clicking on the margin line between any two rows, at the left of the spreadsheet.

... Early-on, this worked for me (a few times), but it's been a couple-few years since it worked.  I've tried making sure all rows and columns are unhidden too, just in case, but no luck.

Work-Around - My only remedy is to copy-paste instead of insert column.  This works, but it's slow, awkward, and scary:

A - Select every populated column left of the point you want to insert your new column.  (So do not select any of the columns to the right of your last columns that contains anything.) 

B - Ctrl-X (to cut).

C - Click on second column in the selected group of columns, to show Excel where you want to paste them to.

D - Ctrl-V (to paste).  The columns get hopped rightward, starting at the column clicked on in step C.

But I hate this work-around, because: (1) with large sheets, it can be tricky making sure you select your columns correctly (did you really select last intentionally populated column?  Just scroll down 2500 rows to find out!) ; (2) if something goes wrong, you just messed up giant portions of data ... yes you can undo, but sometimes Excel decides to crash at inopportune times, like this, especially when doing very data-bulky operations; (3) it's slower (the "working" icon spins many seconds), (4) all that execution time means Excel is manipulating all of that data; and since Excel has a track record of crashing at ironic times, I dislike jeopardizing business-critical data in such unnecessary moves.

I would love to see a fix that is not someone reposting either Fix 1 or Fix 2 that I have already commented on above.

Answer 1

Select a few hundred blank rows below your last lone and delete them. This will remove the blank lines dropping off the bottom and remove to error.

Answer 2

Is your worksheet formatted as a table with banded rows? Even though I had converted the worksheet back to a range, the banding format remained and I think that was the problem.

My fix was:

1. highlight the entire worksheet
2. click on Cell Styles ---> Normal

After a few seconds the banding format disappeared and the worksheet began operating as usual although I had to reformat costs and dates. My formulas were not affected

Answer 3

THANK YOU! This solved the problem for me!

Answer 4

I am getting the same error when I try simple to move a column by CUTTING it and INSERT PASTING it. Therefore, no new columns should even be needed, but I still get that error message. 

I even select all my unused columns and DELETE them, but still the same error.

I have no merged cells at all.

This issue is driving me nuts. I can't do my work if I can't move columns and insert new ones.

Answer 5

It's worth noting that sometimes this bug appears in a file, and never goes away.  But sometimes it appears, then resets itself, such as after doing the workaround (above) once.