Can’t Insert a Row in Excel – How to Fix!

by

As we work with Excel, we may need to insert a row in a dataset for several reasons.

For example, we can insert a row to accommodate new data, improve readability, add formatting, or create space for formulas. Therefore it can be frustrating if we are unable to insert a row. 

This tutorial shows nine possible reasons you cannot insert a row in Excel and gives probable solutions.

Reason #1: The Worksheet is Protected, Disallowing the Insertion of Rows

A protected Excel worksheet is a worksheet that is locked to prevent users from making changes to particular aspects of the worksheet’s structure, formatting, or contents.

Therefore if a worksheet is protected, disallowing the insertion of rows, you cannot insert a row in Excel.

Suppose we have the following dataset on a protected worksheet, disallowing the insertion of rows.  

Data set on a protected sheet

Let’s try to insert a row just above row 10 by right-clicking the numeric header of row 10 and choosing Insert on the shortcut menu.

insert option is grayed out

The Insert row option is greyed out, meaning it is disabled, and we cannot insert a row in Excel.

How to Fix

We need to unprotect the worksheet to allow the insertion of a row. We use the following steps:

  1. On the Review tab, click Unprotect Sheet on the Protect group.
click on the unprotect sheet option
  1. If the worksheet you are working on is protected with a password, enter the password on the Unprotect Sheet dialog box and click OK.
enter the password if it has any

The worksheet is unprotected, and we can now insert a row:

you can now insert a new row in the unprotected sheet
Also read: How to Select Every Other Row (Alternate Row) in Excel?

Reason #2: The Workbook is Corrupted

A corrupted workbook can prevent the insertion of rows.

Corruption in a workbook can occur due to various reasons, such as virus infection and unexpected shutdown of the application. 

How to Fix

Repair the workbook using Excel’s built-in Open and Repair feature using the steps below:

  1. Open Excel in safe mode by pressing and holding down the Ctrl key while opening the application.
  2. Click File to open the Backstage window.
click the file tab
  1. Click Open on the left sidebar and Browse on the right.
click on the browse option
  1. Locate the corrupted workbook, select it, click the down arrow on the Open button at the bottom of the Open feature, and choose Open and Repair.
Locate the workbook and then choose the open repair option
  1. Click the Repair button on the message box that appears.
click the repair button
  1. Click Close on the information message box that appears.
click on the close button in the repairs to dialog box
  1. Try inserting a row and see if the problem is solved.
Also read: Insert Row in Excel (Shortcut)

Reason #3: The Worksheet’s Last Row Has Data or Formatting

If the last row of the worksheet contains data or formatting, we cannot insert a row.

Let’s consider the below dataset on a worksheet whose last row contains data.

data set in a sheet that has data in the last row

The last row of the worksheet contains data as depicted below:

last row of the worksheet contains data

Let’s try to insert a row above row 6 by right-clicking the numeric header of row 6 and choosing Insert on the shortcut menu that appears:

inserting a new row

Instead of inserting a row above row 6, Excel returns a warning message indicating that it can’t insert new cells because it would push non-empty cells off the bottom of the worksheet. 

message saying that new row cannot be inserted

How to Fix

There can be three things you can do to fix this.

Fix #1: Copy and Paste the Data into Another Worksheet and Paste it Back

In this workaround:

  1. Select the dataset and press Ctrl + C to copy it.
  2. Open a new worksheet and press Ctrl + V to paste the dataset.
  3. Insert a row in the pasted dataset.
  4. Select the dataset with the inserted row and press Ctrl + C to copy it.
  5. Open the worksheet containing the original dataset, select cell A1 and press Ctrl + V to paste it. Excel accepts the dataset with the inserted row without complaining.

Fix #2: Delete a Row 

We can delete one empty row below the dataset to make room for the row we want to insert.

Deleting the row makes Excel push the data in the last row up one row, leaving the last row empty, as shown below:

the last row is now empty

We can now insert a row above row 6 of the dataset, as shown below:

new row can now be inserted

Fix #3: Clear All

If we do not want to keep the data or formatting in the last row, we can clear everything below the dataset using the following steps:

  1. Select the empty row beneath the dataset.
  2. Press Ctrl + Shift + Down Arrow to select all rows below the dataset.
  3. On the Home tab, open the Clear drop-down on the Editing group and choose Clear All.
click on the clear all button

Once everything beneath the dataset is cleared, we can insert a row.

Also read: How to Count Filtered Rows in Excel?

Reason #4: A Merged Column In the Worksheet

A merged column in the worksheet prevents the insertion of a row in a dataset.

For example, the following dataset is on a worksheet with a merged column F.

worksheet with a merged column

Let’s try to insert a row above row 7 of the dataset by right-clicking the numeric header of row 7 and choosing Insert on the shortcut menu:

trying to insert a new row

Excel does not insert a row but returns a warning message indicating that it cannot insert new cells because it would push non-empty cells off the end of the worksheet.

dialog box mentioning that a new row cannot be inserted

How to Fix

Unmerge column F using the following steps

  1. Click the letter column header of column F to select it.
  2. On the Home tab, click the Merge & Center button on the Alignment group.
click on the merge and center icon

Once the column is unmerged, you can insert a row in the dataset as depicted below:

new row can now be inserted
Also read: How to Unmerge All Cells in Excel?

Reason #5: A Cell Range With an Array Formula

An array formula in a column in a worksheet can stop any insertion of rows in a dataset because when you set up an array formula over a range of cells, they must stay together.

Therefore, Excel will not allow inserting a row within the array.

Consider the following dataset:

data set where we want to insert a new row

We want to show how an array formula in cell range F2:F10 can stop the insertion of a row in the dataset.

We use the steps below:

  1. Select the cell range F2:F10.
  2. Enter the following formula in the active cell F2:
=1
enter the formula in cell F2
  1. Press Ctrl + Shift + Enter (Press and hold down the Ctrl key, press and hold down the Shift key and then press the Enter key) to enter the formula in the selected cell range as an array formula.
curly brackets in the Formula Bar

Notice that the formula in the formula bar is enclosed in curly brackets ({ }), a visual indicator that the formula is an array formula. 

Now, let’s try to insert a row above row 4 by right-clicking the numeric header of row 4 and selecting Insert on the shortcut menu that appears:

trying to insert a new row

Excel does not insert a row but returns a warning message that you can’t change part of an array.

message box saying that you cannot change part of an array

How to Fix

Select the cell range with the array formula and press the Delete key on the keyboard to remove it. 

Note: Sometimes, the cell range with the array formula may be in a hidden column, so you must unhide the column before deleting the array formula.

Also read: SPILL Error in Excel – How to Fix?

Reason #6: Add-ins

Sometimes, add-ins installed in Excel can stop the insertion of rows in Excel. To fix this, you can try disabling add-ins using the following steps:

  1. Click File to open Excel’s Backstage window.
click the file tab
  1. Click Options on the left sidebar of the Backstage window.
click on options
  1. On the Excel Options dialog box, click Add-ins on the left sidebar and click the Go button next to the Manage drop-down to disable Excel Add-ins.
select the add-ins option and then click on the go button

Note: Afterward, you can open the Manage drop-down, select COM Add-ins, and then click the Go button to disable COM Add-ins.

select com add ins
  1. On the Add-ins dialog box, uncheck the add-ins you suspect might prevent the insertion of rows and click OK.
add-ins dialog box that shows all the add ins
Also read: Excel Autofit Row Height Not Working

Reason #7: Excel VBA Macro

If the workbook has a macro that disables the Insert function on the shortcut menu, you cannot insert a row using the shortcut menu.

How to Fix

Adjust the macro settings in the Trust Center using the following steps:

  1. Click File.
click the file tab
  1. Click Options on the left sidebar of the Backstage window.
  1. On the Excel Options dialog box, click Trust Center on the left sidebar and the Trust Center Settings button on the right.
click on the trust center settings button
  1. Click the Macro Settings category on the left sidebar and the Disable VBA macros with notification option on the right and click OK.
Select the disabled VBA macros with the notification option

Note: The Disable VBA macros with notification option allows you to enable macros on a case-by-case basis to bypass the macros and insert rows as needed.

In extreme cases, you can save the Macro-Enabled Workbook as a regular .xlsx file to remove all macros from the file.

Also read: How to Remove Macros from Excel?

Reason #8: Insufficient Memory

One possible reason that may prevent you from inserting a new row in Excel is due to insufficient memory.

When you try to insert a row, Excel may display an error message that reads, “Excel cannot complete this task with available resources. Choose less data or close other applications.”

This error message indicates that Excel does not have enough memory to perform the task.

When Excel runs out of memory, it cannot perform the task, and you may need to close other applications or reduce the amount of data you are working with to free up memory.

How to Fix

Here are some steps you can take to fix the insufficient memory error in Excel:

  • Close other applications that are running to free up memory.
  • Reduce the amount of data you are working with by deleting unnecessary rows or columns.
  • Save your work and close Excel. Reopen Excel and try to insert a row again
  • Upgrade your computer’s memory to increase the amount of available memory for Excel.

If none of these steps work, you may need to contact your IT department or a computer technician for further assistance.

Also read: Not Enough Memory to Complete This Action in Excel – How to Fix?

Reason #9: Insufficient Privileges

If you have insufficient privileges to edit the workbook, you may be unable to insert a new row.

However, you can try opening the workbook with administrator privileges or contacting the owner to permit you.

This tutorial described nine reasons you might not be able to insert rows in Excel and gave possible solutions for each. We hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment