In Excel: find the cause before retrying — if Excel says it "cannot shift nonblank cells off of the worksheet", clear everything below your data (select the first empty row, Ctrl+Shift+Down, Home ▸ Clear ▸ Clear All) and save; if Insert is greyed out, the sheet is protected (Review ▸ Unprotect Sheet).
On this page7
Read the message: "cannot shift nonblank cells off of the worksheet" means something sits at the bottom edge; a greyed-out Insert means protection.
Press Ctrl+End to see where Excel thinks the used range ends.
If it is far below your data, select the first empty row, press Ctrl+Shift+Down, then Home ▸ Clear ▸ Clear All (or delete those rows).
Save the workbook so Excel resets the used range, then try Insert again.
If Insert is greyed out, choose Review ▸ Unprotect Sheet (you may need the password), insert, and protect again with "Insert rows" allowed if others need it.
If a second table sits directly below the one you are editing, move it or insert whole sheet rows instead of table rows.
What this does
Inserting a row pushes every row below it down by one. Excel refuses when that push would lose something: a worksheet has a fixed last row, so if any cell in that last row holds a value or formatting, there is nowhere to move it and Excel stops with the message about shifting nonblank cells off the worksheet. The other common block is protection — on a protected sheet the Insert commands are disabled unless insertion was explicitly allowed when protecting. Inserting inside an Excel table can also be refused when the shift would move cells of another table below it. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “excel wont let me insertrow in my table”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the tool above before you touch the real workbook. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. The point is a layout choice that keeps the sheet readable and sortable, but the practical win is that someone else can open the file and understand what happened without asking you.
A worked example
A sheet has data in A1:F300, but at some point a formula in column G was filled all the way down to row 1,048,576, the last row of the sheet. Right-click ▸ Insert returns the "cannot shift nonblank cells" message. Select row 301, press Ctrl+Shift+Down to extend to the last row, choose Home ▸ Clear ▸ Clear All, save the workbook, and Insert works again. Ctrl+End now lands near F300 instead of the bottom of the sheet. A sheet that silently refuses to grow blocks every routine update. The cause is almost always one of a few structural conditions, and checking them in order is faster than rebuilding the workbook. A practical tip: try it on a copy of the sheet or a handful of sample rows first and check the result before you apply it to the real data. That one habit catches almost every surprise while it is still cheap to reverse.
In Google Sheets
This is a command rather than a formula, so there is no syntax to carry over. Google Sheets arranges its commands in menus across the top instead of a ribbon, names some of them differently and does not have every Excel command — look for the equivalent in the Sheets menus or its help before assuming the steps match. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Here is the takeaway for “excel wont let me insertrow in my table”: follow the steps at the top, check the result on the sheet itself, and keep undo in mind while you experiment. Done once deliberately, it becomes something you do from memory in Excel rather than something you search for again.
Common mistakes
- Deleting only the visible empty rows while a value or formula far below them keeps the last row occupied.
- Retrying Insert before saving — the used range is often only recalculated after a save.
- Assuming the file is corrupt when the sheet is simply protected.
- Clearing contents (Delete key) when the blocker is formatting; Clear All removes both.
Frequently asked questions
Why does Excel say it cannot shift nonblank cells off the worksheet?
Because the last row (or last column, for column inserts) contains a value or formatting, so the rows being pushed down would fall off the sheet. Clear everything below your real data and save.
Why is Insert greyed out?
Most often the worksheet is protected. Unprotect it from the Review tab; when protecting again you can tick "Insert rows" to allow it.
Is this the same problem when I cannot insert cells or columns?
Yes. Columns are blocked by content or formatting in the last column, cells by whichever edge the shift moves toward; the fix is the same clear-and-save.