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.

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

Solution 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.

Solution 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!

Solution 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.

Solution 5

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.

View more solutions

Comments

[techturtle]

How did the data get on your sheet? (Typed, Pasted, Linked?) That could play into it. It almost sounds like you hit the row limit. While this was not common in 2003 or earlier (~65,000 rows max) it should be a lot harder now, since it can handle over 1,000,000 rows.

[Force Flow]

typed and pasted from another spreadsheet. There are less than 150 rows.

[datatoo]

you do not have formatting in all rows do you? or something that will not allow it to push the rows down, or columns over. Try deleting everything below your 150 rows, and last right column

[Force Flow]

I'm not seeing the same menu options available. Are you using excel 2010 or 2007?

[user3366103]

Excel 2010. Maybe my whole premise is wrong, but the error message says "table" so I don't think so. Let's try this: right-click the sheet's tab and choose "View Code." Then do Ctrl-G to get the Immediate Window. Then paste this line of code into that window ( it's on the bottom): ?Activesheet.listobjects.count. With the cursor still on that line, hit Enter. What number shows on the next line?

[Force Flow]

It returns a 1.

[user3366103]

Ok, that means there's a Table on the sheet. Do the same steps, except enter ?activesheet.listobjects(1).range.address and that will tell you where it is on the worksheet.

[Force Flow]

It returned $A:$J

[user3366103]

Ok, that means there is a table that takes up all of columns A through J. It seems like that, along with my detailed answer above, should answer your original question. Just put your cursor in A1 and you should have the Table menus I described. The fact that the table occupies all rows would explain the Insert button being grayed out, don't know why Delete is.

[Force Flow]

Ah-ha, I was still trying to right click a row number, rather than on an individual cell. After that, I was able to follow your instructions. However, now there's a new error message.

[Force Flow]

If I right click on a column number and click "insert", I get this error "to prevent possible loss of data, excel cannot shift n onblank cells off of the worksheet. select another location in which to insert new cells, or delete the data from the end of your worksheet. If you do not have data in cells that can abe shifted off the worksheet, you can reset which cells excel considers nonblank. To do this, press ctrl+end to locate the last nonblank cell on the worksheet. Delete this cell and all cells between it and the last row and column of your data then save"

[Force Flow]

I followed the instructions and deleted all the rows from the end of my data to the last row (ten thousand and something). That seems to have done it. I'm still puzzed as to why/how this happened, as I haven't done anything different than I usually do.

[user3366103]

Glad it worked for you.

[Sufian]

This is what exactly happened to me today. I created too many columns while creating a table. :D