Excel – Can’t insert cells in Excel 2010 – “operation not allowed” error message

microsoft excelmicrosoft-excel-2010spreadsheet

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:

excel formatting

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):

greyed out tables

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:

enter image description here

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

enter image description here

Related Question