"You Can't Change Part of an Array": How to Fix It

Excel says you cannot change part of an array when you try to edit, clear or move one cell of a multi-cell array formula. The fix is to select the whole array first, then edit or delete it in one go. This guide explains why the message appears, how to get out of it, and how to replace old array formulas with modern ones so it doesn't come back. It's for anyone who has opened an older workbook and found a block of cells they can't touch.

Why Excel says "You can't change part of an array"#

The message means the cell belongs to a block of cells that share one formula. Excel treats that block as a single object, so it won't let you change only some of its cells.

These blocks are array formulas: formulas that work on a whole range at once. Before 2020, you created one by selecting several cells, typing a formula and pressing Ctrl+Shift+Enter. Excel then wrapped the formula in curly braces in the formula bar, like this:

{=B2:B6*C2:C6}

That one formula fills D2:D6 with Qty × Price for five rows. Each cell shows a different result, but they are all one formula. You'll mostly meet these in files built in Excel 2019 or earlier. The comparison of Excel 365 vs Excel 2021 vs 2019 explains which versions use the older style.

What triggers the message#

Any action that would change some cells of the array but not all of them triggers it. The most common ones are:

A similar message, "Cannot change part of a data table", appears for What-If Analysis data tables. The cause and the fix are the same: work with the whole table, not one cell.

Step 1: Get out of the message with Esc#

If you're stuck in edit mode and Excel keeps showing the message, press Esc. This cancels the edit and puts the old formula back. Clicking OK on the message and then pressing Esc works too. Nothing is lost.

Step 2: Select the whole array#

Before you can change anything, select every cell of the array:

1. Click any cell in the array.
2. On Windows, press Ctrl+/. Excel selects the whole block.
3. Or, on Windows and Mac, go to Home > Find & Select > Go To Special, choose Current array and click OK. (On a Mac, you can also open Go To with Control+G and click Special.)

You can now see exactly which cells belong to the array. Often it's bigger than you expected.

Step 3: Edit, delete or convert the whole array#

With the whole array selected, you have three choices.

Edit it. Press F2 (Mac: Control+U), change the formula, then press Ctrl+Shift+Enter (Mac: ⌘+Shift+Return). This keeps it as an array formula across the same cells. Pressing only Enter in an older version breaks it.

Delete it. Press Delete. All the cells clear at once, and you can now insert rows, sort or type freely.

Turn it into values. If you only need the results, copy the selection (Ctrl+C; Mac: ⌘C), then use Paste Special > Values in the same place. The formula is gone, the numbers stay, and each cell can be edited on its own. The guide to pasting values in Excel walks through this.

If you were trying to insert a row, delete or convert the array first, insert the row, and then re-enter the formula across the new size.

The modern fix: let the formula spill#

In Microsoft 365 and Excel 2021, you don't need multi-cell array formulas any more. Excel now has dynamic arrays: you type the formula in one cell, press Enter, and the results spill into the cells below.

To upgrade an old array:

1. Select the whole array with Ctrl+/ and press Delete.
2. Click the top cell, here D2.
3. Type =B2:B6*C2:C6 and press Enter.

The five results spill into D2:D6, just like before. Only D2 holds the formula. The other cells show results in grey in the formula bar, and if you type in one of them, D2 shows a #SPILL! error instead of the old message. Clear the cell and the results come back. The lesson on modern Excel functions shows how spilling works with UNIQUE, SORT and FILTER.

Often you can skip the helper column entirely. If D2:D6 only existed to be added up, one cell does the whole job:

=SUM(B2:B6*C2:C6)

With Qty 10, 4, 6, 2 and 5 and Price 1.50, 3.25, 2.00, 7.50 and 1.20, the result is 61. In older versions, this needed Ctrl+Shift+Enter too. In Microsoft 365, plain Enter works. SUMPRODUCT gives the same answer in every version: =SUMPRODUCT(B2:B6,C2:C6).

Old array formulas you might find#

Older workbooks used array formulas for many everyday jobs. When you meet one, the modern version is usually a single spilling formula:

For the ideas behind these, see changing text case in Excel, changing the date format and the percent change formula. If the percent column compares two different things rather than old and new, read percent change vs percent difference first.

One more rule: Excel tables don't allow multi-cell array formulas at all. If you convert a range with one into a table, Excel refuses. Spilled formulas can sit next to a table but not inside it either.

Practise it#

Spotting an old array formula and replacing it with one spilling formula is a quick, practical skill that modern Excel tests reward. Try the exercise on this page, then practise modern Excel functions in a live spreadsheet: you type each formula and it checks it instantly.