Excel Can’t Insert Row? Fix the Non-Empty Cells Error

Errors & fixes · Updated November 18, 2026

Yes—Excel may refuse to insert a row when shifting cells down would push existing content or formatting past the worksheet’s last row. The usual fix is to find and clear only the unused rows at the bottom of the sheet, then save and reopen the workbook before trying again.

What the non-empty-cells warning means

Excel worksheets have a fixed size: 1,048,576 rows and 16,384 columns. When you insert a row, Excel moves the cells below the insertion point down by one row. If something is already in the bottom row, that shift could push it beyond the worksheet boundary, so Excel blocks the operation rather than discarding it.

The warning can be confusing because the cells at the bottom may look blank. A cell can still be considered used if it contains a formula, a space, formatting, or other remnants from earlier work. For example, someone may have formatted a whole column or pasted a large range, then deleted the visible values. The sheet looks empty, but Excel may still treat distant cells as part of its used area.

This is different from Excel deciding that the new row would overwrite data near the row you selected. Inserting a sheet row normally shifts existing cells down; it does not replace them. The problem is usually at the far edge of the worksheet, not directly beside your current data.

Try the quick fix first

Before clearing anything, save a copy of the workbook. Then check the last row of the sheet and remove only material that you have confirmed is unnecessary. These steps apply to Excel for Microsoft 365 on Windows:

  1. Press Ctrl+End to move to the last cell Excel considers used. Note its row number and column letter. If it is close to your actual data, the warning may have another cause.
  2. Identify the first row below your real data that should be completely unused. Check other columns and sections of the worksheet first; the blank space in one column may contain important information in another.
  3. Select the unused rows from that point to row 1,048,576. You can enter a range such as 501:1048576 in the Name Box, left of the formula bar, if row 501 is confirmed to be the first unused row.
  4. Choose Home > Clear > Clear All. This removes cell contents and formatting from the selection, so do not use it on rows that need to retain formatting or other information.
  5. Save the workbook, close it, and reopen it. Try the row insertion again.

If the unwanted used area extends across extra columns as well, check the right edge of the real data. Clear only columns that are truly unused, and take care not to remove information that belongs elsewhere on the sheet.

Use Ctrl+End to diagnose the used range

Ctrl+End is a diagnostic shortcut, not a guarantee that the selected cell contains data. Excel moves to the bottom-right cell it currently regards as used. If your actual table ends at D200 but Ctrl+End lands at XFD900000, that large gap is a clue that formatting or old content may have extended the used range.

Inspect the destination before deleting anything. Look at the row and column around the cell selected by Ctrl+End, and consider whether there is legitimate content there. A distant cell might hold a note, a formula, or a separate data block that is easy to miss when viewing the top of the sheet. If it is genuinely empty but formatted, clearing its formatting may be enough; if it contains unwanted content too, Clear All removes both.

On a Mac, the ribbon path for clearing cells is also Home > Clear > Clear All, though keyboard behavior can vary with the keyboard and Excel settings. You can use the Name Box to select a row range instead of relying on a Windows shortcut. Excel for the web has a similar Home tab, but some selection and workbook-management details differ; if the used-range cleanup is awkward in the browser, open the workbook in the desktop app.

Clear unused cells without deleting real data

The most important part of the repair is choosing the correct boundary. If your records end at row 500, do not assume that every row after 500 is disposable until you have checked the entire worksheet. A second list, a calculation, or a reference table could be far below the main dataset.

When the extra area is truly unused, select the rows and use Clear All rather than pressing Delete alone. The Delete key generally clears cell contents but can leave formatting behind, so Excel may continue to treat those cells as used. Clear All is more thorough, but also more destructive: it removes formats and other cell details along with values. Saving a separate copy first gives you a way back if you clear too much.

After clearing, save and reopen the workbook so Excel can refresh its view of the used area. Then check Ctrl+End again. It should land near the actual end of your worksheet’s content, though a deliberately formatted area may still affect where it goes. If the warning persists, inspect the full bottom row and the far-right columns for anything that still needs to be shifted.

Check other reasons Excel may block insertion

If Ctrl+End does not reveal an unexpectedly distant used cell, check for other conditions. The wording of the error matters: Excel can display different messages for protected sheets, merged cells, tables, and array formulas. A different message calls for a different solution; clearing the bottom of the sheet will not fix every insertion problem.

  • Protected worksheet: If the sheet is protected, Excel may prevent structural changes. Check Review > Unprotect Sheet. You may need the password, and you should not remove protection unless you are authorized to do so.
  • Grouped worksheets: If multiple sheet tabs are selected, a row insertion can affect all grouped sheets. Check whether the tabs are grouped and select a single sheet before editing if that is what you intend.
  • Merged cells: Merged areas near the insertion point can make some row or cell operations unavailable or produce a different error. Review the affected region and, if appropriate, unmerge cells before inserting.
  • Array formulas or spilled results: Excel may prevent changes to only part of an array or spill range. That is not the same as the non-empty-cells-at-the-edge warning; change the full formula range or move the formula as appropriate.

Do not respond to an unfamiliar error by deleting large ranges. Note the exact message, determine whether the problem is sheet protection, a range structure, or the worksheet boundary, and then use the matching remedy.

Insert a worksheet row or a table row

For a normal worksheet row in Microsoft 365 on Windows, click its row number, then choose Home > Insert > Insert Sheet Rows. You can also right-click the row number and choose Insert. Excel inserts a full worksheet row above the selected row and shifts cells below it downward. On Mac, the corresponding Home-tab insertion command is available as well; menu wording or placement can vary slightly by release.

If the data is an Excel table, consider inserting a table row rather than an entire worksheet row. Right-click a cell in the table and choose Insert > Table Rows Above. This adds a row to the table structure, which helps preserve the table’s formatting and calculated-column behavior. Alternatively, move to the last cell in the table’s last row and press Tab to create a new table row.

These operations are not interchangeable. A table row expands the structured table; a sheet row shifts cells across the worksheet. If you need a full blank row through the sheet, use Insert Sheet Rows. If you are simply adding a record to a formatted table, use the table-row command.

Prevent the problem from returning

Unnecessary formatting and pasted content can make a worksheet’s used area much larger than its visible dataset. To reduce the chance of another insertion warning, avoid formatting entire columns or rows when only a small range needs a style. When copying data from other workbooks or web pages, paste only the required range and check that you have not carried along thousands of unwanted formatted cells.

Keep the data area distinct from notes, calculations, and other lists, and make a backup before performing large cleanup operations. If multiple people edit the workbook, explain which rows or columns are intentionally reserved. That context helps prevent a later user from clearing something that only appears unused.

Frequently Asked Questions

Why does Excel say cells are not empty when they look blank?
They may contain formatting, formulas returning an empty string, spaces, or other leftover cell content. A cell can appear blank on screen and still count when Excel tries to shift cells toward the worksheet boundary.

Will pressing Delete fix the insertion error?
Not always. Delete usually removes cell contents but may leave formatting behind. If the problem is leftover formatting, select only the confirmed-unused area and use Home > Clear > Clear All, then save and reopen the workbook.

Does Ctrl+End show the last cell with data?
Not necessarily. It shows the last cell Excel currently considers used, which may be formatted or otherwise affected even if it contains no visible value. Treat it as a clue and inspect the surrounding area before making changes.

Can I insert a row without shifting existing cells?
A full worksheet-row insertion shifts cells below the selected row down. If you need to add a record to an Excel table, insert a table row instead; it expands the table rather than treating the operation as a general worksheet-row insertion.

Will clearing unused rows delete my workbook data?
It will delete anything in the selected rows, including formatting, so verify the range first and save a copy. Clear only rows you have confirmed are unused across the entire worksheet, not just rows that look blank in the main data column.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.