10

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.

3
  • 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.
    – techturtle
    Apr 26, 2012 at 20:00
  • typed and pasted from another spreadsheet. There are less than 150 rows.
    – Force Flow
    Apr 27, 2012 at 12:50
  • 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
    – datatoo
    Apr 28, 2012 at 0:37

10 Answers 10

14

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

10
  • I'm not seeing the same menu options available. Are you using excel 2010 or 2007?
    – Force Flow
    May 4, 2012 at 13:36
  • 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? May 4, 2012 at 13:55
  • It returns a 1.
    – Force Flow
    May 4, 2012 at 17:59
  • 1
    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. May 4, 2012 at 18:20
  • 1
    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. May 4, 2012 at 20:05
3

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.

2

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!

1

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.

1

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.

Only 4 cells selected, works fine

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.

Entire row selected, does not work

1
  • This is what exactly happened to me today. I created too many columns while creating a table. :D
    – Sufian
    Jun 13, 2021 at 9:59
0

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.

0

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!

0

"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

-1

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.

-1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .