Excel Cannot Change Part of Array Error: How to Fix

Errors & fixes · Updated November 19, 2026

Excel shows “You can’t change part of an array” when you try to edit or delete only some cells governed by one array formula. Select the entire legacy array range to change or clear it; for a dynamic array, edit or delete the formula in its top-left anchor cell, not the spilled results.

Why Excel shows this message

An array formula can return several results, with one formula controlling a group of cells. Excel protects the relationship between those cells: changing just one result cell could leave the array in an inconsistent state. So if you select one cell—or only part of the formula’s output range—and type, paste, or press Delete, Excel may block the action with this message.

The warning is about how the selected cells relate to a formula, not necessarily a problem with the workbook. It does not mean the worksheet is damaged, and it does not mean that the formula itself is wrong. Most often, the fix is to select the complete legacy array range before making the change.

Legacy array formulas and dynamic arrays

Excel has two array behaviors that are useful to distinguish. A legacy array formula, used in older Excel workflows and still supported in Microsoft 365 for Windows, is entered into a selected output range as one formula. Excel marks it internally as an array formula. In older versions, users typically confirmed one with Ctrl+Shift+Enter on Windows; on Mac, use Control+Shift+Enter. Excel displays braces around a legacy array formula in the formula bar, but those braces are added by Excel and are not typed as part of the formula.

For example, with numbers 2, 3, and 4 in A2:A4 and 10, 20, and 30 in B2:B4, select C2:C4 and enter =A2:A4*B2:B4 as a legacy array formula. The results are 20, 60, and 120. All three output cells belong to the same array formula, so Excel will not let you overwrite only C3.

Dynamic arrays are the newer behavior in Microsoft 365 and Excel 2021 or later. You enter a formula in one anchor cell, and Excel spills its results into adjacent cells automatically. The anchor is the cell containing the formula; the other cells are spill results, not separate formulas. The anchor can be edited, but a spill result generally cannot be edited independently. This may feel like the same restriction, but it is important to select the right cell when fixing it.

Find the complete array range

If you do not know which cells belong to the array, use Excel’s selection tools rather than guessing. Selecting a single output cell and checking the formula bar can help identify the formula, but it may not reveal the full range clearly. To select a legacy array, click any cell within it and use the Go To Special dialog.

  1. Click a cell that displays one of the array results.
  2. On the ribbon, choose Home > Find & Select > Go To Special.
  3. Select Current array, then choose OK.
  4. Check the highlighted range before editing, clearing, or replacing anything.

Current array selects the cells associated with that legacy array formula. If the dialog option is unavailable or the selection is not what you expected, you may be dealing with a dynamic spill range, a table formula, or a different kind of protected cell rather than a legacy array. In Microsoft 365, clicking a spilled result often makes Excel indicate the anchor cell, which is where you should inspect the formula.

Edit a legacy array formula

To change the calculation, select a cell in the array and inspect the formula bar. You can edit the formula for the array as a whole, but do not try to type a different value into one output cell. For a careful edit, select the current array using the steps above, make the formula change, and confirm it as an array formula. When using the legacy entry method, confirm with Ctrl+Shift+Enter on Windows or Control+Shift+Enter on Mac. Excel will apply the updated formula to the selected array range.

Before editing, note the existing range and formula so you can restore them if needed. If Excel rejects the change, check that the full output range is selected and that you are not attempting to change only one cell. Also check whether the formula depends on cells that are locked or on a worksheet that is protected; protection can prevent edits for a different reason.

Remove or replace the array

To remove a legacy array formula, select its complete range and clear its contents. You can press Delete after selecting the whole array, or use Home > Clear > Clear Contents. Clearing contents removes the formula and its results but does not, by itself, remove formatting from the cells. If you need to preserve the displayed results while removing the formula, copy the complete range and use Paste Special > Values. Confirm the selected range first, since pasting values over the array replaces the formulas.

To replace the formula with a different calculation, work on the full array rather than typing into one of its cells. If your goal is to retain only one result, copy that result and paste it as a value into a separate cell outside the array. Then, if appropriate, clear the original array as a whole. This avoids trying to detach one cell while it is still controlled by the shared formula.

Resize an array formula

A legacy array formula’s output range cannot usually be expanded or reduced by inserting or removing just one of its result cells. To resize it, record the formula and its current range, select and clear the entire array, select the intended new output range, then enter the formula again as an array formula. Check that the new range does not overlap data you need to keep.

For instance, if a legacy formula currently fills C2:C4 but needs to produce four results, do not try to type into C5 while treating C2:C4 as unchanged. Clear the complete existing array and re-enter it across the intended four-cell range. This is different from a dynamic array: its output size is usually determined by the formula, and Excel spills the results from its anchor cell as needed, provided the spill area is clear.

What to do with a dynamic array

For a dynamic array in Microsoft 365, select the top-left anchor cell and edit or clear that formula. Do not delete or overwrite just one of the displayed spill results. If you want the results to become independent values, copy the entire spill range and paste values into a separate clear area. Then you can remove the original formula by clearing its anchor cell.

If a dynamic formula cannot spill because another cell occupies the required output area, Excel typically displays a #SPILL! error. That is different from the message about changing part of an array: #SPILL! points to an obstructed or otherwise unavailable spill range, while the array message means you are trying to alter cells that belong to one array operation. Clear or move any obstructing content only after checking that it is safe to remove.

Other causes and common mistakes

Not every edit restriction is caused by an array formula. If selecting Current array does not identify the range, consider other explanations before deleting cells. A worksheet may be protected, a cell may be part of an Excel table, or the workbook may be opened in a mode that limits editing. Check the wording of the message and whether Excel highlights a formula range.

  • Editing only one result: Select the full legacy array or edit the dynamic formula’s anchor cell.
  • Trying to type braces: Do not type the curly braces shown around a legacy array formula. Excel adds them when the formula is entered as an array.
  • Confusing #SPILL! with this message: A blocked dynamic spill needs space for its results; it is not solved by editing one spill cell.
  • Worksheet protection: If Excel says the sheet is protected, use Review > Unprotect Sheet if you have permission and the password, rather than treating it as an array-selection problem.
  • Copying a single result: Copying one cell may copy a formula relationship, not an independent value. Use Paste Special > Values when you need a fixed result.

Excel for the web also supports many array-formula workflows, but some editing and selection details can differ from the Windows desktop app. If the range-selection controls are missing or the workbook behaves differently, open it in Excel for Microsoft 365 on Windows or Mac and use the ribbon steps there.

Keep results without keeping the formula

Sometimes the desired fix is not to edit the array but to stop the results changing. For example, a report may need a snapshot of the current output before its source data is replaced. Copy the entire result range, choose an empty destination, and use Paste Special > Values. The pasted cells contain fixed values rather than the original array formula. Verify the copied results before clearing the source array, especially if the formula feeds other calculations.

If other formulas refer to the array range, removing or resizing it can affect those formulas. Check nearby calculations, charts, and downstream reports before clearing it. When a workbook is important, save a copy first; that provides a straightforward way to recover if the wrong range is selected.

Frequently Asked Questions

How do I fix Excel’s You can’t change part of an array error?
Select the complete legacy array range using Home > Find & Select > Go To Special > Current array, then edit or clear the whole selection. If it is a dynamic array, edit the formula in its top-left anchor cell.

How can I tell whether a cell is part of an array formula?
Click the cell and inspect the formula bar. For a legacy array, select Home > Find & Select > Go To Special > Current array to highlight its range. For a dynamic array, identify the anchor cell that contains the formula and the adjacent cells displaying its spilled results.

Can I delete just one cell from an array formula?
Not while that cell is part of the array output. Clear the whole legacy array, or change the dynamic formula at its anchor. If you only need to remove the formula from one displayed result, copy that result as a value to another location first.

Why can’t I edit a spilled cell in Excel 365?
A spilled cell is an output of the formula in the anchor cell, not an independent formula cell. Change the anchor formula to alter the results, or copy and paste the spill range as values if you need editable, fixed cells.

Will clearing an array formula delete its formatting?
Pressing Delete or choosing Home > Clear > Clear Contents removes the contents, including the formula, but normally leaves formatting in place. Use a separate clear-formatting command only if you also intend to remove the cell formatting.

Is this error the same as #SPILL!?
No. The array-editing message appears when you try to change only part of a formula’s array range. #SPILL! is a dynamic-array error that generally means Excel cannot place the results in the required spill area.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.