Excel Performance Problems

How to Fix Excel Cannot Add Rows After Row 70

Amos Gikunda Oct 9, 2026 870 views

Question details

The user is unable to insert new rows into an Excel worksheet, specifically observing this restriction after a certain row like row 70.

Fixing the Cannot Add Rows Error in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to insert new rows into an existing dataset to expand the worksheet.
Observed behavior: Excel prevents the insertion of new rows because hidden formatting or stray data has accidentally been extended to the very last row (row 1,048,576) of the worksheet, leaving no room to shift cells down.

Before you start

Scroll to the bottom of your dataset to visually verify the exact row number where your actual data ends, ensuring you only select truly empty rows for clearing.

Solution 1Recommended

Clear All Data and Formatting from Unused Rows

Use this method to completely remove any hidden data, spaces, or formatting in the final rows of the worksheet, which frees up space and allows Excel to insert new rows again.

Excel has a hard limit of 1,048,576 rows per worksheet. When you insert a new row, Excel shifts all existing rows down. If the very last row contains any data or formatting (even a cell background color or border), Excel cannot shift it off the sheet, triggering an error.

Select the first unused row

Click directly on the row number on the left side of the screen for the first completely blank row immediately below your actual data.

Select to the bottom of the sheet

Press the keyboard shortcut Ctrl + Shift + Down Arrow. This will highlight all rows from your current selection down to row 1,048,576.

Clear all content and formatting

Navigate to the 'Home' tab on the ribbon. Locate the 'Editing' group on the far right, click on the 'Clear' button (often represented by an eraser icon), and select 'Clear All' from the dropdown menu.

Verify row insertion

Right-click on any row number within your active data range and select 'Insert'. The new row should now appear without any errors.

Clear All Data and Formatting from Unused Rows

Save the file to apply changes: If you still cannot insert a row immediately after clearing the data, press Ctrl + S to save the file, close it, and reopen it. This forces Excel to recalculate the used range.

Manage Spreadsheets Easily with WPS Office

Easily Handle Large Worksheets with WPS Spreadsheet

WPS Spreadsheet offers a highly compatible and smooth experience for managing large datasets. You can seamlessly clear stray formatting, insert unlimited data rows within limits, and handle complex calculations without experiencing performance drops.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing .xlsx file using the Spreadsheet module.
2. Highlight the unused rows: Click the row number of the first empty row below your dataset, then press Ctrl + Shift + Down Arrow to select to the bottom.
3. Clear all formatting: Go to the Home tab, click the Eraser icon (Clear) in the toolbar, and choose 'Clear All'.
4. Insert new rows seamlessly: Right-click any row number in your working area and click 'Insert' to add new rows effortlessly.

Free to use with a familiar, easy-to-navigate spreadsheet interfaceFully compatible with Microsoft Excel (.xlsx, .xls) formatsLightweight architecture prevents freezing during large data operationsBuilt-in robust clear tools to quickly reset unused ranges

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel think the last row is in use when it looks empty?

If you previously applied a background color, added a border, or accidentally typed a space character in one of the cells at the very bottom of the worksheet, Excel considers those rows active. Because Excel cannot push active cells beyond row 1,048,576, it prevents you from adding new rows.

Does using 'Clear All' delete my formulas?

Yes, 'Clear All' removes everything, including content, formulas, and formatting. However, because you are only selecting the completely empty, unused rows below your actual data, your active formulas and data will remain safe.

How do I quickly find where Excel thinks my data ends?

Press Ctrl + End on your keyboard. This shortcut jumps to the last cell Excel thinks is being used. If the cursor jumps far below your actual data, that highlights the excess area containing hidden formatting that you need to clear.

Can I just delete the rows instead of clearing them?

Yes, you can right-click the selected unused rows and choose 'Delete'. However, Excel immediately replaces deleted rows with new, unformatted rows at the bottom. Using 'Clear All' is generally faster and more effective for resetting the used range.

Excel & Data Problems