Cannot Insert Row in Excel (Quick 7 Fixes)

The created data sheets are not fixed so you may need to update them by inserting rows, columns, or various formulas, values from time to time, but sometimes you face the situation that you cannot insert row in Excel to update your datasheet.

This article will explain the sources of this problem along with the solutions. So, let’s start our main article.

Download Workbook


7 Fixes of Cannot Insert Row In Excel

To demonstrate the issues and solutions of the problems in inserting new rows in Excel, we are using the following data table containing the prices of different products of a company.

cannot insert row in Excel

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


1. Cannot Insert Row in Excel Fixation by Using the Clear All Option

Context:

Here, we want to insert a new row before the row containing the records of the Watermelon product for giving entry of a new product.

cannot insert row in Excel

To do this, after selecting Row 8 (where we want to insert a row) we have gone through Home Tab >> Cells Group >> Insert Dropdown >> Insert Sheet Rows Option.

clearing last row

Instead of having a new row, we are having an error message here which says

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.

clearing last row

The root cause of this error is that we have some unwanted values, borders, and background color in the cells of the very last row.

clearing last row

Solution:

The solution to this problem lies in clearing out all of the values, formatting styles from the last row.
➤ Select the row following the end of your dataset.

cannot insert row in Excel

➤ Press CTRL + SHIFT + ↓ (Down key) to select all of the rows excluding our data range.

clearing last row

After selecting the unused rows, you have to clear all of the unwanted contents from these rows.
➤ Go to Home Tab >> Editing Group >> Clear Dropdown >> Clear All Option.

clearing last row

Then, we can see that the entries of the last row have been removed.

clearing last row

Now, try to insert a new row finely without any error message.

clearing last row

Finally, you can enter the new record of the product in this row (here, we have entered the record for Apple).

cannot insert row in Excel

Read More: How to Insert a Row within a Cell in Excel (3 Simple Ways)


2. Cannot Insert Row in Excel Fixation by Copying Data Range

In this section, we will try to solve the previous problem with another type of solution to insert rows successfully.

Solution:

➤ Select the data range from the sheet where you are facing the problem and press CTRL+C to copy this range.

cannot insert row in Excel

➤ Then go to a new sheet ( here, it is Copy) and select a cell where you want to paste the range.

copying data range

➤Finally, for pasting the data range press CTRL+V.

copying data range

In the new sheet, we have entered a new row successfully and

cannot insert row in Excel

then we have put down our new product’s record here.

copying data range

Now, it is time to copy the data range of this sheet by pressing CTRL+C again.

copying data range

After that, you have to return to the main sheet and paste the copied data by pressing CTRL+V here in the place of the previous data range.

cannot insert row in Excel

Read More: Excel Formula to Insert Rows between Data (2 Simple Examples)


3. Using a VBA Code for Inserting Row Without Error

Context:

We will try to insert a new row before the row for the records of Watermelon.

cannot insert row in Excel

Unfortunately, we are getting the error message after trying to insert a new row.

VBA Code

The cause of this error is the same as the previous sections as you can see.

VBA Code

Solution:

Here, we will solve this problem with a VBA code.
➤ Go to Developer Tab >> Visual Basic Option.

cannot insert row in Excel

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.

VBA Code

After that, a Module will be created.

VBA Code

➤ Write the following code

Sub Rmvall()

Rows(ActiveCell.Row & ":" & Rows.Count).Delete

End Sub

This code will delete all of the unwanted contents from the rows excluding the used range.

VBA Code

➤ Press F5.

Then you will be able to delete all of the contents from the last row.

VBA Code

Now, try to insert a new row

VBA Code

and put down the record of the product Apple.

cannot insert row in Excel

Read More: VBA to Insert Row in Excel (11 Methods)


4. Cannot Insert Row in Excel Due To Protecting Sheet

Context:

Here, we will insert a new row before the row for the product Watermelon.

cannot insert row in Excel

But after selecting Row 8 (the place of the new row) when we try to select the Insert Sheet Rows option of the Insert dropdown under the Home tab, we cannot select it because it is disabled for this sheet.

protect sheet

Due to turning on the Protect Sheet option, here we could not insert a new row.

Solution:

So, we have to unprotect this sheet prior to the insertion of the new row.
➤ Go to Review Tab >> Protect Group >> Unprotect Sheet Option.

cannot insert row in Excel

Then, the Unprotect Sheet dialog box will open up.
➤ Enter the Password (which you used for protecting your sheet) and press OK.

protect sheet

After that, you can try again to insert a new row.
➤ Select the row where you want to have a new row and go to Home Tab >> Cells Group >> Insert Dropdown >> Insert Sheet Rows Option (it is enabled now).

protect sheet

Finally, we have entered a new row and

protect sheet

entered the record for the new product Apple.

cannot insert row in Excel


Similar Readings


5. Cannot Insert Row in Excel Due To Merged Column

Context:

For trying to insert a new row before the row for the product Watermelon,

cannot insert row in Excel

we are getting the error message again.

merged column

The reason for this problem is that we have a fully merged column besides the data range.

cannot insert row in Excel

Solution:

To insert a new row successfully, we have to unmerge this column first.
➤ Select the merged column (Column E in this case).
➤ Go to Home Tab >> Alignment Group >> Merge & Center Dropdown >> Unmerge Cells Option.

cannot insert row in Excel

After unmerging the column, now try to insert a new row again, and as you can see we have inserted it successfully.

merged column

Finally, write down the record of the new product Apple.

merged column


6. Cannot Insert Row in Excel Due To Freezing Pane

Context:

Freezing Panes becomes helpful for a large set of data which you want to scroll down but want to see a fixed part of your data set all over the time of scrolling. But this feature may cause problems when you try to insert a new row.

Solution:

To insert a row successfully we have to unfreeze the following indicated freeze pane at first.

cannot insert row in Excel

➤ Go to View Tab >> Freeze Panes Dropdown >> Unfreeze Panes Option.

unfreeze panes

In this way, you have removed the Freeze Pane successfully.

unfreeze panes

Then, we have inserted a new row and,

unfreeze panes

filled it up with the record of a new product Apple.

cannot insert row in Excel


7. Converting Table to Range for Solving the New Row Addition Problem

Context:

Converting a data range into a Table can make your calculation faster and easier, but sometimes it may cause problems with inserting a new row.

Solution:

So, we will convert the following table into a range prior to the addition of a new row.

cannot insert row in Excel

➤ Select the Table and go to Table Design Tab >> Tools Group >> Convert to Range Option.

converting table to range

Then, a message box will appear which says
Do you want to convert the table to a normal range?

➤ Select Yes here.

converting table to range

We have converted our table into a data range in this way.

converting table to range

Now, insert a new row and,

converting table to range

enter the records of the new product Apple.

cannot insert row in Excel

Read More: Shortcuts to Insert New Row in Excel (6 Quick Methods)


Conclusion

In this article, we tried to cover some of the solutions to the situation when you cannot insert a row in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo