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

Question

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.

Answer 1

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:

Answer 2

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.

Answer 3

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!

Answer 4

This also frequently happens when your table has too many rows. You cannot add more rows when the table is maxxed out.

If you select the entire column(s) before making a table, this error will always occur.

---

Simple Example: make a new spreadsheet, fill 4 lines, select those, and make a table. Rightclick the table: you can easily insert a row above.

Now, another column, fill 4 lines, select the COLUMN (by clicking on the column header) and make a table. Rightclick the table: you cannot insert any more rows.

Answer 5

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

The root cause is the refresh is adding rows or columns in the table and detecting there's no room to do so - could be empty looking rows but have been used before? Not certain.

The fix - edit table properties (click in the table, click design tab, click properties) -- change selection at bottom OFF from "insert cells for new data..." and onto either of the other options, my preference is "Overwrite existing cells..."

Caution - this will overwrite any standard information in the expanded rows or columns. However - if there is an immediate adjacent additional table - it will MOVE the table, not overwrite/remove - thanks be to some reasonably minded engineer at MS!

My result - I've converted my SS to have table on top of table (10 in total) on each worksheet - no spaces in between. The updates now occur without error, and all rows shift around to accomodate what is needed.

Answer 6

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.

Answer 7

My issue was influenced by some defined tables I had in the Formulas > Name Manager section.

I had 2 'named' tables, one below the other. But I was trying to insert an entire column which would have effected my table named Table2. I moved "Table2" to another tab, just for testing, then I was able to add a column.

So, check Formulas > Name Manager and see if there's anything defined there. It might help isolate the issue.

PS - Converting my "Format as Table" table to a Regular Range did not resolve my issue. Perhaps because table2, below the newly converted table1 wasn't included in that event (Convert to Range).

Good Luck!

Answer 8

"Probably your table just has maximum amount of rows, i.e. 1048576. It very easily can be the case when you create a table from selected columns. Though most of the rows contains no actual data, Excel for some reason treats those cells as not empty. To fix this issue, just go to the last non-empty (i.e. containing some data) row of your table and delete all empty rows below it."

Yeah, I'v encountered the same problem. Just Cltr+End and delete the rows that doesn't have any data.And your problem will solve

Answer 9

Probably your table just has maximum amount of rows, i.e. 1048576. It very easily can be the case when you create a table from selected columns. Though most of the rows contains no actual data, Excel for some reason treats those cells as not empty. To fix this issue, just go to the last non-empty (i.e. containing some data) row of your table and delete all empty rows below it.

Answer 10

Just create a copy of the tab. Now you are able to insert a row. Then you can remove the original tab and rename the copy to the original name.