Fix "Microsoft Excel can't insert new cells"
Causes and fixes
- Something sits in the last column or row
- Formatting was applied to whole rows or columns
- A formula or value was filled all the way down
1Something sits in the last column or row
A sheet has 16,384 columns and 1,048,576 rows. To insert a column, Excel has to push everything one column right; if anything sits in the last column (XFD), there's nowhere for it to go. The same happens with rows.
How to recognise it
Ctrl+End jumps far beyond your data, or Ctrl+Right Arrow from an empty column lands on something in column XFD.
Fix
- Click the column letter just after your data.
- Press Ctrl+Shift+Right Arrow to select every column to the end.
- Right-click a selected column letter and choose Delete.
- Do the same for rows with Ctrl+Shift+Down Arrow if inserting rows fails.
- Save the workbook, so Excel resets where the sheet ends.
2Formatting was applied to whole rows or columns
Borders, fill colors or a format applied to an entire row or column count as content, even if the cells look empty.
Fix
Delete the unused columns or rows as above. Delete works where Clear Contents doesn't, because it removes the formatting too.
3A formula or value was filled all the way down
Double-clicking the fill handle or selecting a whole column before pasting can put a formula in every row to the bottom of the sheet.
Fix
Select the first unused row, press Ctrl+Shift+Down Arrow and delete the rows. For a running list, an Excel table (Insert > Table) grows by itself without filling the whole column.
Still stuck?
- If the sheet is protected, you'll see a different message; use Review > Unprotect Sheet.
- Notes and comments far below the data also count as content.
- If you can't insert in a table, check whether another table or a merged cell is directly in the way.