Fix Excel Cannot Change Part of an Array Error When Inserting Rows
Question details
The user needs to insert rows in an Excel worksheet but receives a message that Excel cannot change part of an array.

- 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.
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.
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.
In Microsoft Excel, select the affected worksheet and go to Review > Unprotect Sheet.
Type the worksheet protection password, then select OK to remove protection.
Right-click the row number where you need space and choose Insert, or go to Home > Insert > Insert Sheet Rows.
Go to Review > Protect Sheet, select Insert rows if users should be able to add rows later, and then confirm the protection settings.

Check the array formula range and replace legacy arrays if needed
Use this method when the inserted row touches cells controlled by an array formula.
Update Excel and verify the workbook file format
Use this option when the same protected workbook works in another Excel installation or update channel.
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.

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.




