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.

Cannot Insert Row In Excel: 7 Quick Fixes

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.

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.

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.

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.”

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.

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.

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

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.

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

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

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

Read More: Shortcuts to Insert New Row in Excel

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.

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

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

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

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

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

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.

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.

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

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

Solution:

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

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

After that, a Module will be created.

➤ 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.

➤ Press F5.

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

Now, try to insert a new row

and put down the record of the product Apple.

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.

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.

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.

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

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).

Finally, we have entered a new row and

entered the record for the new product Apple.

Read More: Excel Fix: Insert Row Option Greyed Out in Excel

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,

we are getting the error message again.

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

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.

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

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

Read More: Excel Formula to Insert Rows Between Data

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.

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

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

Then, we have inserted a new row and,

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

Read More: How to Insert Row Below 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.

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

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

➤ Select Yes here.

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

Now, insert a new row and,

enter the records of the new product Apple.

Read More: How to Insert a Total Row in Excel

Download Workbook

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

• Insert Multiple Blank Rows in Excel
• How to Insert Multiple Rows in Excel
• How to Insert Blank Row After Every Nth Row in Excel
• Insert Multiple Rows After Every Other Row in Excel
• How to Insert Rows in Excel Automatically

<< Go Back to Insert Rows | Rows in Excel | Learn Excel