Why Microsoft Excel Cannot insert new cells?

This tutorial solves a problem where Excel won’t allow you to insert new rows or columns in a worksheet.  When you try this, Excel displays the message “Microsoft Excel can’t insert new cells because it would push non-empty cells off the end of the worksheet.  Those non-empty 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.”

METHOD 1: Clear Last Row or Column

The most probable reason you are getting this message is that in the last row or column of your worksheet, there is either formatting, data or a formula.  This might not be easy to spot, but by following the instructions below you can easily clear the offending cells.

A quick way to navigate to the last column in your worksheet is to:

1. Click somewhere in the first empty column in your worksheet (to the right of any data)
2. Use the key combination CTRL ⇒ (CTRL and the right-arrow key)

A quick way to navigate to the last row in your worksheet is to:

1. Click somewhere in the first empty row in your worksheet (below any data)
2. Use the key combination CTRL ⇓ (CTRL and the down-arrow key)

If you can see either data, a formula or formatting in the last row or column, this is probably where the problem lies.

Here is a fairly reliable way to clear all cells to the right of or below your data (including the last column and row):

1. Select the whole of the first empty column in your worksheet (to the right of any data).  You can quickly select the whole column by clicking on the column letter that appears at the top of the worksheet
2. Use the key combination CTRL SHIFT ⇒ to select all columns to the right
3. On the ribbon’s Home tab, in the Editing group, click the Clear button and then Clear All
4. Now select the first empty row in your worksheet (below your data).  You can quickly select the whole row by clicking on the row number that appears on the left of your worksheet
5. Use the key combination CTRL SHIFT ⇓ to select all rows below
6. On the ribbon’s Home tab, in the Editing group, click the Clear button and then Clear All

Once you have completed these steps, try inserting a row or column.  You may get a warning message titled ‘Large Operation’.  The warning indicates that inserting new cells may take a significant amount of time to complete.  You can ignore the warning and click on OK.

METHOD 2: Copy to a New Worksheet

If the method above does not work, try this method:

1. Create a new worksheet in your workbook
2. Copy and paste your data into the new worksheet
3. Insert the new column or row in the copy of your data
4. Copy the copy of the data that now includes a new row or column
5. Navigate back to the original worksheet
6. Select the top left cell of the data that you originally copied
7. Paste

You will need to repeat these steps for each column or row you need to insert.

METHOD 3: Clear the ‘Used Range’

If method 1 and 2 fail,  Try method 3.

Method 3 requires you to reset the ‘used range’ in your worksheet. To do this:

1. Use the key combination ALT F11 to open the Visual Basic Editor (VBE)
2. Use the key combination CTRL G to open the Immediate window.  The Immediate window appears near the bottom of the VBE with the title ‘Immediate’
3. In the Immediate window type the following code: activesheet.usedrange.select
4. Press ENTER on your keyboard
5. Use the key combination ALT Q to close the VBE
6. Now try inserting a new column or row in your worksheet