Fixes / Excel · Checked

Excel can't insert new cells (push non-empty cells off worksheet): fix

Short answer

Excel refuses to insert because something already sits in the last column (XFD) or last row (1,048,576). It is usually formatting, a space or an empty-looking formula applied to whole rows or columns. Press Ctrl+End to see where Excel thinks your data ends. Delete every row and column past your real data, then save, close and reopen the file.

What this message means

An Excel worksheet has a fixed size: 1,048,576 rows and 16,384 columns (A to XFD). Inserting a row pushes everything below it down by one, and inserting a column pushes everything to the right over by one. If any cell in the last row or last column holds anything at all, Excel has nowhere to push it and blocks the insert. That includes fill color, borders, a space character or a formula that returns "". The cells look blank, but Excel counts them as used. The fix is to remove whatever is in the unused area so the sheet's used range ends where your real data ends.

Common causes

CauseHow to tell
Formatting was applied to entire rows or entire columns (fill color, borders, font, number format), for example by clicking a column letter or row number and then formatting it.Press Ctrl+End. The cursor jumps to row 1,048,576 or column XFD, far past your data, even though those cells look empty.
Conditional formatting or data validation covers whole columns or rows.Home > Conditional Formatting > Manage Rules (set Show formatting rules for: This Worksheet) shows an 'Applies to' range such as =$A:$Z or =$1:$500. Data > Data Validation on a far-off cell shows a rule.
Hidden values in far-off cells: a space, an apostrophe, or a formula filled down or across to the end of the sheet that returns an empty string ("").Ctrl+End lands on a cell that looks blank, but the formula bar shows a formula, a space, or an apostrophe when you select it.
A formatted table (Format as Table) or banded-row formatting reaches the bottom of the sheet.Clicking a cell far below your data still shows the Table Design tab, or the row banding keeps going as you scroll down.
Hidden rows or columns at the end of the sheet contain data or formatting.Row numbers or column letters skip (for example 20 jumps to 1048576). A double line appears between the headers where rows or columns are hidden.

How to fix it

1. Find where Excel thinks your sheet ends

  1. Click any cell on the sheet and press Ctrl+End (on a Mac laptop keyboard, Fn+Control+Right Arrow).
  2. Note the cell it jumps to. If it is in column XFD, the column insert is blocked. If it is in row 1048576, the row insert is blocked.
  3. Select that cell and check the formula bar. A formula, space or apostrophe there means the cause is hidden values. An empty formula bar means the cause is formatting.
  4. Use this to decide whether to clean up columns (Fix 2), rows (Fix 3) or both.

2. Delete all unused columns to the right of your data

  1. Find the last column that really holds your data (for example T).
  2. Click the column letter just after it (for example U) to select the whole column.
  3. Press Ctrl+Shift+Right Arrow (Mac: Command+Shift+Right Arrow) to extend the selection to column XFD.
  4. Right-click any selected column letter and choose Delete. Selecting whole columns removes them outright; clearing alone often isn't enough.
  5. Save the file, close it and reopen it. Excel only resets the used range when the file is saved and reopened.
  6. Try inserting your column again.

3. Delete all unused rows below your data

  1. Find the last row that really holds your data (for example 20).
  2. Click the row number just below it (for example 21) to select the whole row.
  3. Press Ctrl+Shift+Down Arrow (Mac: Command+Shift+Down Arrow) to extend the selection to row 1048576.
  4. Right-click any selected row number and choose Delete.
  5. If the error comes back, select the same rows again and choose Home > Clear (eraser icon) > Clear All.
  6. Save, close and reopen the file, then press Ctrl+End again. It should now stop at your last real cell.

4. Unhide rows and columns before cleaning up

  1. Click the Select All triangle at the top-left corner of the sheet, or press Ctrl+A twice.
  2. Go to Home > Format > Hide & Unhide > Unhide Rows, then Home > Format > Hide & Unhide > Unhide Columns.
  3. Scroll to the end of the sheet and look for content that was hidden.
  4. Move any data you need, then repeat Fix 2 and Fix 3.

5. Remove whole-column conditional formatting or table formatting

  1. Go to Home > Conditional Formatting > Manage Rules and set Show formatting rules for to This Worksheet.
  2. Edit any rule whose Applies to range covers whole columns or rows (like =$A:$Z) so it only covers your data, for example =$A$1:$Z$500.
  3. If a formatted table runs to the bottom of the sheet, click inside it and go to Table Design > Resize Table. Enter a range that ends at your last row of data.
  4. If banding still covers the whole sheet, select the unused rows or columns and choose Home > Cell Styles > Normal. This removes all formatting from those cells, so don't include your data.
  5. Save, close and reopen, then try the insert again.

6. Use Clean Excess Cell Formatting (Inquire add-in)

Excel for Windows with Microsoft 365 Apps for enterprise or equivalent editions only. Not available in Excel for Mac or Excel for the web.

  1. Make a backup copy of the file first. Microsoft warns that this change cannot be undone.
  2. Go to File > Options > Add-ins.
  3. In the Manage box choose COM Add-ins and click Go. Tick Inquire and click OK.
  4. On the new Inquire tab, click Clean Excess Cell Formatting.
  5. Choose the current sheet or all worksheets, then click Yes to save the changes.
  6. Reopen the file and try the insert again.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

If Ctrl+End still jumps to the end of the sheet after you delete the unused rows and columns, then save, close and reopen, the sheet may be damaged. Copy only your real data range (not whole rows or columns) and paste it into a new, blank worksheet. Use Paste Special > Formulas and number formats, then reapply the formatting you need. You can also try File > Open, select the workbook, and choose Open and Repair from the Open button's dropdown. For a shared or company file, ask the owner or your IT help desk. Someone may have applied formatting or a formula to the whole sheet on purpose, and a template may keep re-adding it.

Questions people ask

Why does Excel say cells are not empty when they look empty?

Excel counts a cell as used if it has any formatting (fill, borders, number format), a space, or a formula, even one that shows nothing. Selecting a whole column or row and formatting it marks every cell to the end of the sheet as used.

I cleared the extra rows and columns but still get the error. Why?

Excel only resets its record of where the sheet ends when you save, close and reopen the file. If the error remains after that, delete the rows and columns instead of clearing them, and check conditional formatting and tables for whole-column ranges.

What is the maximum number of rows and columns in Excel?

Excel 2007 and later sheets have 1,048,576 rows and 16,384 columns (the last column is XFD). Inserting only fails when something already occupies the last row or column. It does not mean you have run out of space for real data.

Is this the same as the 'Cannot shift objects off sheet' error?

No. 'Cannot shift objects off sheet' is caused by shapes, charts, comments or notes near the edge of the sheet and is usually fixed by changing their properties or deleting them. This error is about cell contents or formatting in the last row or column.

Will deleting the empty rows and columns remove my data?

Not if you start the selection right after your last real row or column. Press Ctrl+End first and check the cells in that area for anything you need. Unhide all rows and columns before you delete, so nothing hidden gets removed.

Sources

Related errors