"Microsoft Excel can't insert new cells"
Inserting a row or a column fails: Excel says it would push non-empty cells off the end of the worksheet. The sheet is almost never full; it only believes it is.
What the message says
Inserting a row shifts every row below it down by one. If the sheet's last row (row 1,048,576) holds anything, even just formatting, there is no room left to shift it.
Why it appears
- Formatting, a border or a colour applied to whole columns, or to whole rows for columns.
- A formula copied down to the bottom of the sheet by a misplaced double-click.
- Conditional formatting or data validation set on whole columns.
- A stray space or apostrophe at the very bottom of the sheet.
Making it go away
- Ctrl+End shows the last cell Excel believes is used: if it is far from the data, that is the cause.
- Select the empty rows below the data down to the bottom (Ctrl+Shift+Down), Delete the rows (not just Clear), then save.
- Do the same for the columns to the right of the data.
The tool that handles it
Reduce file size brings each sheet's used range back to the real end of the data, without touching the values; the health check says, sheet by sheet, where Excel believes the sheet ends.
Frequently asked questions
- Why doesn't the used range shrink after clearing?
- Clearing the contents keeps the formatting; you have to delete the rows, then save the file so Excel recalculates the used range.