Microsoft Excel is a great spreadsheet software from Microsoft. It has a lot of useful features that help you manage your data. While editing a spreadsheet in Excel, some users encountered the Microsoft Excel can’t insert new cells because it would push non-empty cells off the end of the worksheet error. This error occurs when a user tries to insert a row or column in Excel. If you see the same error in Excel, use the solutions provided in this article.
The complete error message that Excel displays is:
Microsoft Excel can’t insert new cells because it would push non-empty cells off the end of the worksheet. These cells might appear empty but have blank values, some formatting, or a formula. Delete enough rows or columns to make room for what you want to insert and then try again.
Microsoft Excel can’t insert new cells because it would push non-empty cells off the end of the worksheet
Before we jump to the solutions, let’s understand the cause of this error. You should know why this Excel displays this error message sometimes.
The error message explains the cause of this problem. If you read it carefully, you will know that some data may or may not be hidden. This data prevents Excel from inserting rows and columns.
Excel has a maximum of 1048576 rows and 16384 columns. If you scroll down to the bottom, you will see that the last row has the number 1048576. Similarly, the last column is XFD. Inserting the rows and the columns does not change these values. You can try this. Move to the last row and then insert a new row. You will see that the row is inserted but the number 1048576 remains constant. The same thing applies to columns.
Now, try this. Enter a value to the last row and the last column. You can reach the last row and column in Excel by pressing the Ctrl + down arrow and the Ctrl + right arrow keys respectively. After entering a value to the last row ad column, when you insert a row or a column, Excel will show you the same error message. The same thing happens if you apply formatting to the last row or column. This is how you can reproduce this error in a new Excel file.
If Excel inserts row(s) or column(s), the data on the last row or column will disappear because of being shifted off the end of the worksheet. That’s why Excel is not inserting the row(s) or the column(s).
To fix the Microsoft Excel can’t insert new cells because it would push non-empty cells off the end of the worksheet error in Excel,you have three ways:
- Delete the entries from the last row and column
- Clear formatting from the last row and column
- Create a new sheet in Excel or create a new spreadsheet
Let’s see all these fixes in detail. Before proceeding, we recommend you create a copy of your Excel spreadsheet and save it to another location for backup.
1] Delete the entries from the last row and column
Go to the last row and column and delete the data they contain. If you see the last row or column empty, the data may be somewhere else on the last row or column, i.e., in any cell on the last row or the last column.
Select the last row and press the Delete button. Do the same thing with the last column. This should fix the issue. If this does not help, save your Excel file and restart Excel.
2] Clear formatting from the last row and column
If the above step did not help, some type of formatting might have been applied to the last row or column. Select the last row and column and clear formatting. To clear formatting, select the Home tab and go to “Clear > Clear All.” Save the Excel file and restart Excel (if this does not help).
You can also try one more thing. Select the first empty row. Now press the Ctrl + Shift + Down arrow. This will select all the empty rows. Now, hit the Delete button and clear the formatting of the selected rows. Repeat the same procedure for all empty columns. To select all the empty columns, select the first empty column and then press the Ctrl + Shift + Right arrow.
3] Create a new sheet in Excel or create a new spreadsheet
If the problem still persists, create a new sheet in Excel and copy all your data from the previous sheet to the new sheet. Now, check if you can insert rows or columns in the new sheet. If yes, delete the previous sheet. Alternatively, you can also create a new blank spreadsheet and copy your data there. Use your mouse cursor to select the data to copy. Do not use the Ctrl + A shortcut because it will select all the cells due to which the problem will occur in the new sheet or spreadsheet.
I hope this helps.
Read: The Clipboard cannot be emptied, Another program might be using the Clipboard Excel error.
How do I push everything down in Excel?
To push everything down in Excel, you have to push all the rows down that contain data. To do so, select the first row containing data and insert new rows above it. The easiest way to do this is to use the keyboard shortcut. After selecting the first row containing data, press and hold the Ctrl key. Now, hit the + key. Every time you hit the + key, a new row will be inserted above the selected row.
How do I push all cells up in Excel?
You can push all cells up in Excel by dragging them up. To do so, select the required range of cells. Place your mouse cursor at the border of the selection. The white Plus icon in Excel will be changed to an arrow. Hold the left click and move the selected cells to drag them to the required place.
Read next: Excel keeps blocking files from being inserted.
