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

Excel shows “You can’t change part of an array” (older versions word it “You cannot change part of an array”) when you try to edit, delete, insert or move a single cell that belongs to a multi-cell array formula. The fix is to work on the array as one block: select every cell it covers, then edit or delete the formula for the whole range at once.

That’s the short answer. Below is how to find the array that’s causing the message, the right way to edit, resize or remove it, and how to stop it from coming back. The steps are based on Microsoft’s own documentation as of October 2026. Menu names can shift a little between Excel versions.

Why Excel blocks the edit

Excel has two kinds of array formulas, and they behave differently:

• Legacy (CSE) array formulas. These are entered with Ctrl+Shift+Enter and show curly braces around the formula in the formula bar, like {=A1:A10*B1:B10}. Microsoft’s comparison of dynamic and legacy array formulas explains that a multi-cell CSE formula is locked together. Every cell holds the same formula, the result has a fixed size, and you can’t change just one cell. This is where the “can’t change part of an array” message usually comes from.
• Dynamic (spilled) array formulas. In versions of Excel with dynamic arrays, you type the formula in one cell and press Enter, and the results “spill” into neighboring cells. According to Microsoft’s page on spilled array behavior, only the top-left cell is editable. The other cells show a greyed-out “ghost” of the formula that can’t be changed.

According to Microsoft’s rules for changing array formulas, you can’t change, insert or delete individual cells in a multi-cell legacy array. You can move the whole array as a group, and you can delete the whole thing.

Common triggers

You’ll usually run into the message when you:

• Type over, or press Delete on, one cell inside the array
• Insert or delete a row or column that cuts through the array
• Sort or filter a range that overlaps array cells (two Tech Community threads describe this error appearing on a sort)
• Paste data over part of the array
• Cut and move only some of the array’s cells

Step 1: Find and select the whole array

Before you can fix anything, you need to see exactly which cells the array covers. Either of these methods selects the full block from any single cell inside it.

Using Go To Special:

1. Click any cell that is part of the array.
2. On the Home tab, choose Find & Select > Go To Special (or press F5 and click Special).
3. Select Current array and click OK.

Microsoft documents the Current array option in its guide to finding and selecting cells that meet specific conditions.

Using the keyboard: Click a cell in the array and press Ctrl+/. MyOnlineTrainingHub points out that this shortcut selects every cell belonging to that array formula.

When you can’t tell where the array is

Sometimes the error appears during a sort or a large paste and you have no idea which formula is to blame. In a Microsoft Tech Community thread, a responder suggested the Inquire add-in’s Workbook Analysis, which lists where array formulas are in a workbook. The original poster said it led them straight to the problem formula.

You turn Inquire on under File > Options > Add-ins, by choosing COM Add-ins and clicking Go. It is only included in some Windows editions of Office, so it may not be on your machine. If it isn’t, check the range you were sorting or pasting into one area at a time with Go To Special > Current array.

Step 2: Edit, resize, move or delete the array

With the array selected, choose the task you need. For a legacy array, the steps below follow Microsoft’s rules for changing array formulas.

Edit the formula

1. Select the whole array (Step 1).
2. Press F2 to edit the formula.
3. Make your change.
4. Press Ctrl+Shift+Enter to confirm. If you press plain Enter in a version without dynamic arrays, Excel won’t treat the edit as an array formula.

For a single-cell legacy array formula, you only need to select that one cell, press F2, edit, and press Ctrl+Shift+Enter.

Delete the array

1. Select the whole array (Go To Special > Current array, or Ctrl+/).
2. Press Delete.

This clears the formula from every cell in the block at once, so Excel won’t complain.

Make it bigger

1. Select the current array cells plus the empty cells you want to add.
2. Press F2 and adjust the formula’s references to cover the larger range.
3. Press Ctrl+Shift+Enter.

Make it smaller

You can’t remove cells from a legacy array. To shrink it:

1. Copy the formula text from the formula bar so you don’t lose it.
2. Select the whole array and press Delete.
3. Select the smaller output range, paste or retype the formula, and press Ctrl+Shift+Enter.

Move it

Select the whole array, cut it (Ctrl+X), click the new top-left cell, and paste. Microsoft’s rules allow moving the array as a group, but not moving individual cells out of it.

Dynamic arrays: what’s different

If the formula has no curly braces and its results spill from one cell, you’re dealing with a dynamic array. The rules are simpler:

Microsoft notes that dynamic array formulas return a #SPILL! error when something is in the way of the output range. That error is about blocked cells, not about editing part of an array, and clearing the blocking cells fixes it.

On a spilled range, you can’t sort or change just part of the results either. In the Tech Community sorting thread, the advice was to change the formula itself, not its output. If you need sorted results, wrap the formula in SORT() in the top-left cell, or copy the results and use Paste Special > Values somewhere else before sorting.

Preventing the error

• Use dynamic arrays for new work. Microsoft says Excel keeps CSE formulas for compatibility and recommends dynamic array formulas for new workbooks. They are easier to edit because only one cell holds the formula. The exception is a file that must open in older Excel versions without dynamic arrays. Keep the legacy form for those.
• Convert old arrays when it’s safe. Copy the formula text, delete the whole legacy array, then type the formula into the top-left cell only and press Enter. Check the results before relying on them, especially if the original array was a fixed size on purpose.
• Keep arrays away from data you sort. Leave a blank column or row between array output and any table you plan to sort, filter or paste into.
• Paste values when you just need numbers. If a colleague only needs the results, copy the array and use Paste Special > Values. The pasted cells are ordinary cells you can edit one at a time.

Quick checklist

1. Click a cell near the error and press Ctrl+/ to see the array’s extent.
2. Decide what you need: edit, resize, move or delete.
3. Act on the whole range. For a legacy array, confirm edits with Ctrl+Shift+Enter.
4. If you can’t find the array, try Go To Special > Current array on nearby cells, or Inquire’s Workbook Analysis if your edition includes it.
5. For new formulas, consider dynamic arrays so the problem doesn’t come back.