Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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 '12 at 20:00
    
typed and pasted from another spreadsheet. There are less than 150 rows. –  Force Flow Apr 27 '12 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 '12 at 0:37

8 Answers 8

up vote 6 down vote accepted

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

share|improve this answer
    
I'm not seeing the same menu options available. Are you using excel 2010 or 2007? –  Force Flow May 4 '12 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? –  Doug Glancy May 4 '12 at 13:55
    
It returns a 1. –  Force Flow May 4 '12 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. –  Doug Glancy May 4 '12 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. –  Doug Glancy May 4 '12 at 20:05

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.

share|improve this answer

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!

share|improve this answer

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.

share|improve this answer

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!

share|improve this answer

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.

share|improve this answer

"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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.