Excel cannot insert row: how to fix it

1. Why Excel refuses: the sheet has a fixed size

A worksheet always has exactly 1,048,576 rows and 16,384 columns. Inserting a row doesn't add one to the sheet: it pushes every row below it down by one, and the last row falls off the end. If that last row is empty, nobody notices. If it holds anything, Excel stops and shows the message instead of deleting your data. Columns work the same way with column XFD.

Older versions word it as "Microsoft Excel can't insert new cells because it would push non-empty cells off the end of the worksheet". Same cause, same fix.

2. Find what's at the bottom

Press Ctrl+End. Excel jumps to the last cell it thinks is in use. If that's row 1,048,576, or far below your list, you've found the problem. The usual causes: a formula filled all the way down (Ctrl+Shift+Down then Ctrl+D selects to the bottom of the sheet), a whole column formatted or filled by accident, a stray value typed after Ctrl+Down overshot the data, or an old total pasted at the very bottom.

To check one column, press Ctrl+Down from the last row of the data: if it lands on a cell instead of row 1,048,576, that cell has something in it. A formula gives the same answer without moving: it returns the row of the last non-empty cell in column A. In this stock list it shows 12, not 9, because an old total sits below the data.

3. Delete the unused rows, then save

Clearing the contents isn't always enough, because formatting can count too. Delete the rows outright, and Excel replaces them with fresh, empty ones.

To check nothing else is hiding below the list in column A before you insert, count what's there. Zero means clear.

1. Click the row number just below your data (here row 10).
2. Press Ctrl+Shift+Down to select every row to the bottom of the sheet. Press it again if it stops at a cell with content.
3. Right-click a selected row number and choose Delete.
4. Do the same for columns: click the column letter right of your data, press Ctrl+Shift+Right, right-click > Delete.
5. Save the workbook. Excel resets the used range on save, so Ctrl+End now lands at the end of your real data, and Insert works again.

4. "The operation is attempting to shift cells in a table"

The full message is "This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet." It appears when the insert would move only part of an Excel table: for example inserting cells with Shift cells down in a few columns that cut through a table, or inserting next to a second table that sits beside the first.

Insert whole sheet rows instead: select the row with Shift+Space first, then press Ctrl+Shift+Plus. To add a row inside a table, right-click a table row and choose Insert > Table Rows Above. If two tables sit side by side, move one below the other or to its own sheet so they stop blocking each other.

"You can't change part of an array" is the cousin for old Ctrl+Shift+Enter array formulas: you can't insert a row through the middle of one. Select the whole array, edit it, or convert it to a normal formula first.

5. Insert is greyed out

If the Insert commands are grey, check three things. A cell still being edited disables most of the ribbon: press Enter or Esc. A protected sheet blocks inserting unless the owner ticked Insert rows when protecting it: go to Review > Unprotect Sheet. And a workbook opened in Protected View (the yellow bar at the top) is read-only until you click Enable Editing.

If several sheet tabs are selected ([Group] in the title bar), Insert still works but puts the row into every grouped sheet. Right-click a tab and choose Ungroup Sheets unless that's what you want.

6. The row goes in, but totals miss it

Once Insert works, check your totals. A row inserted inside a SUM range is included automatically. A row added below the last row of the range is not, because =SUM(D2:D9) still stops at row 9. Insert new rows above the last data row, or turn the list into a table (Ctrl+T) so new rows and formulas extend by themselves.