Can't insert cells in Excel 2010 - "operation not allowed" error message

I was working on a spreadsheet in Excel 2010, and all of a sudden when I attempted to insert a new row of cells, I saw that the insert and delete options were grayed out.

I attempted to copy a different row and insert it as a new row, but I got the error message:

"This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet."

I have not merged or hidden any cells/rows/columns. There are no formulas. There is no data verification. I tried closing and re-opening the spreadsheet.

Searching for answers brings up nothing useful.

As the message indicates, you're trying to insert rows into a Table in your sheet. Typically, but not necessarily, tables will have banded formatting. When you click in a cell in a table, the Table tab will appear, like so:

The Insert and Delete commands get grayed out when there's more than one table intersecting the row you're trying to delete (and maybe other times):

I haven't figured out how to "Insert Copied Cells" into just a table row in one step. Instead I insert a row, or rows, into the table and then copy the content. To insert a row into the table, right-click a cell in the table and choose Insert:

You can turn the tables back into normal cells. Do it like this:

I am not sure if it was the same problem, but I had a similar issue on a large spreadsheet where I had many rows and columns hidden. I would try to add or delete rows or columns and got a message similar to what you had. In many cells I had comments, I discovered that although the comments were linked to a specific cell, you could move them and they could be anyplace on the spreadsheet. If you tried to hide, delete, or insert rows/columns that had those hidden comments you got an error message that would go off the table. I made all the comments visible, and then moved them to a spot I was not trying to affect and no more problem.

I was getting the same error. "This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet."

Tried the suggestions, but everything looked correct. Ended up just converting all 4 Tables to Range and then back to Table. Don't know which Table was the problem, but it's all good now. ;)

Thanks!

If your table is a linked table (via ODBC connection, linked to Access, etc.) that's causing the error, you can change your connection properties on the linked table to "Insert entire rows for new data, clear unused cells". This solved the issue for me where I had several consecutive linked queries on one worksheet.