Excel Error Codes

Fix Excel Cannot Change Part of an Array Error When Inserting Rows

Steve KSteve K Nov 12, 2026 869 views

Question details

The user needs to insert rows in an Excel worksheet but receives a message that Excel cannot change part of an array.

Fix Excel Cannot Change Part of an Array Error When Inserting Rows
Product
Microsoft Excel
Device & OS
not provided
Scenario
Inserting a worksheet row in a workbook that contains protected-sheet settings and array formulas.
Observed behavior
Excel blocks the row insertion and displays a “Cannot change part of an array” error, even when the sheet protection settings appear to allow row insertion.
Before you start

Check whether the rows you want to insert intersect a legacy array formula range. On protected sheets, confirm that the person editing the file has permission to insert rows and knows the sheet password if unprotection is required.

Solution 1Recommended

Unprotect the worksheet before inserting the row

Use this workaround when the sheet is protected and Excel continues to reject row insertion despite the Insert rows permission being enabled.

Protected worksheets can behave differently across Excel versions when a row insertion affects an array formula. Temporarily removing protection lets Excel recalculate and resize the affected ranges without the protection restriction.

1
Open the Review tab

In Microsoft Excel, select the affected worksheet and go to Review > Unprotect Sheet.

2
Enter the sheet password if prompted

Type the worksheet protection password, then select OK to remove protection.

3
Insert the needed row

Right-click the row number where you need space and choose Insert, or go to Home > Insert > Insert Sheet Rows.

4
Apply worksheet protection again

Go to Review > Protect Sheet, select Insert rows if users should be able to add rows later, and then confirm the protection settings.

Unprotect the worksheet before inserting the row
Array formula limitation: If the inserted row falls within a legacy array formula range, Excel may still require you to edit or replace the entire array formula rather than changing only one part of it.
Free Microsoft Office alternative

Work with Excel-Compatible Spreadsheets in WPS Office

If a Microsoft Excel build or protected array formula is preventing row insertion, try WPS Office as a free, lightweight spreadsheet alternative. WPS Spreadsheet provides a familiar worksheet interface, supports common Excel files, and makes it easy to move existing workbooks into a compatible editing environment.

Open and edit Excel .xlsx and .xls workbook formats.Use a familiar spreadsheet layout with formulas, rows, columns, and worksheet tools.Work with array-based calculations and organize formulas with helper columns when needed.Move Excel workbooks to a lightweight, free Office suite with minimal learning time.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel say “Cannot change part of an array” when I insert a row?

The new row would alter only part of a legacy array formula range. Excel requires legacy array formulas to be changed as a complete range, so you must revise, remove, or replace the full array formula before inserting the row.

Does allowing Insert rows in Protect Sheet always fix this error?

No. Go to Review > Protect Sheet and enable Insert rows, but Excel can still block the change when the insertion intersects an array formula or when a version-specific protected-sheet issue occurs.

How can I identify a legacy array formula in Excel?

Select a cell in the affected calculation and inspect the Formula Bar. Legacy array formulas are commonly shown with curly braces around the formula. You can also use Home > Find & Select > Go To Special > Current array to select the full range.

Can I insert rows without removing worksheet protection?

Yes, if Review > Protect Sheet was configured with Insert rows allowed and the new row does not create an unsupported change to an array formula range. If the error persists, temporarily use Review > Unprotect Sheet, insert the row, and protect the sheet again.

What details should I collect if this happens only in one Excel version?

Record the Excel version and build from File > Account > About Excel, the workbook format, whether the sheet is protected, the array formula location, and the exact steps used to reproduce the error.