Saturday, December 7, 2019

"Microsoft Excel can't insert new cells" - Cannot add new column

You are working in an Excel file and you need to a new row and suddenly an error message blocks you from doing it. What could be the issue here?

Especially in a case where the document has been used for a while, you quickly check the end of the row and don't see any issue. The error message looks like this:
"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."
So generally there must be somewhere an issue where the last row is filled with something and therefore cannot be moved. The thing is, it can be even by coloring.

As a first step I would go the last columns, mark the last few and look for the "Clear" tool in the ribbon bar. Use "Clear Formats" first and try to add a column.

In my case, around row 100 cells were colored and blocked the whole sheet from adding another row. So there are also ways to find it manually if you know how to look for.