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.
Best Answer
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: