Delete an Array Formula in Excel Without Losing Data

Formulas & functions · Updated November 19, 2026

To delete an array formula in Excel for Microsoft 365 on Windows, select the formula cell and press Delete. For a legacy array formula entered across several cells, select the entire array first—Home > Find & Select > Go To Special > Current array—then press Delete. Excel does not let you remove just one cell from an array formula; save any results you need before clearing it.

What an array formula is—and why deletion differs

An array formula calculates one or more results as a group. In current Excel, a single dynamic-array formula can return results into neighboring cells, called its spill range. For example, with cell A1 available as the starting cell, entering =SEQUENCE(3) returns 1, 2, and 3 in A1:A3. The formula itself is in A1; the other two cells display its results.

Older Excel worksheets may instead contain a legacy array formula entered into several selected cells at once, commonly with Ctrl+Shift+Enter. All cells in that legacy array belong to one formula, even if the formula bar appears to show a result or formula only when a particular cell is selected. Excel protects both kinds of arrays from partial edits because changing one member independently could break the calculation.

This distinction matters when you want to remove an array, change its calculation, or keep some of its output. With a dynamic array, you generally remove the formula at its top-left anchor cell. With a legacy array, select the complete formula range before clearing it.

Identify the formula type and its full range

Click a cell displaying an array result and look at the formula bar. If it contains a formula and Excel outlines or highlights the range receiving results, it is likely a dynamic array. The formula is entered in the anchor cell; the other spill cells are outputs, not separate formulas. If you see a legacy formula, it may appear with braces in the formula bar, such as {=SUM(B2:B5*C2:C5)}. Do not type those braces yourself: Excel adds them to formulas entered as legacy arrays.

If you are unsure which cells belong to a legacy array, use Excel’s Current array selection rather than guessing the range:

  1. Select a cell that is part of the array.
  2. Choose Home > Find & Select > Go To Special.
  3. Select Current array, then choose OK. Excel selects the full legacy array range.

For a dynamic formula, select its anchor cell—the cell containing the formula. A spill range is often indicated by a colored border when you select that anchor. If you have inherited a workbook, check the surrounding cells and formula bar before deleting anything: an output cell may look like ordinary data even though it depends on a formula elsewhere.

Delete a dynamic array formula and its spill results

To remove a dynamic array formula and all results it currently spills, select its anchor cell and press Delete on Windows. This clears the formula; Excel then removes the spill results automatically. You can also use Home > Clear > Clear Contents. That ribbon command is useful if you want to avoid confusion between clearing a cell’s contents and deleting a worksheet row or column.

Do not try to clear only one of the displayed spill cells. Those cells are controlled by the anchor formula, so Excel normally prevents you from changing an individual result. If you want to change the output, edit the formula in the anchor cell instead. If you want to remove the formula but keep the displayed results, first copy the results to another location as values; the next section explains that workflow.

Clearing the anchor removes the formula and its current output, but it does not delete the cells around the spill range or shift other worksheet content. If another formula refers to the array’s results, that dependent formula may show an error or recalculate differently after the source is removed. Check important dependent calculations before clearing an array in a shared or complex workbook.

Delete a legacy array formula across multiple cells

A legacy array formula cannot be removed by selecting just one cell in its range. First select the entire array using Home > Find & Select > Go To Special > Current array. With the full range selected, press Delete, or choose Home > Clear > Clear Contents. The formula and the results it populated are cleared together.

If you select only part of a legacy array and try to delete or edit it, Excel displays a message that you cannot change part of an array. That message is a safeguard, not evidence that the worksheet is broken. Cancel the attempted change, select a cell in the array, use Current array to select the complete range, and then repeat the clear action.

Remember that deleting the formula is different from deleting worksheet cells. Pressing Delete clears the selected contents while leaving the worksheet layout in place. By contrast, the Delete command on a cell’s context menu can offer options that shift cells or remove rows and columns. For removing an array formula, clear its contents rather than shifting the grid.

Keep the results but remove the formula

Sometimes you want to freeze a calculation—for example, before sending a workbook to someone who should see the current figures but not depend on the source formula. Preserve a separate copy of the displayed results before removing the array. Select the output cells, copy them, and paste them into an unused area using Home > Paste > Paste Values. Confirm that the pasted cells contain the results you expect; then clear the original array using the appropriate method above.

For a dynamic array, copy the visible spill range, not just its anchor, if you need every result. For a legacy array, select the full array range when copying. Pasting values elsewhere gives you ordinary cell values that no longer update when the original inputs change. That is useful for a snapshot, but it also means those values can become outdated.

Do not assume you can paste values directly over only part of an array. Excel may block the change because that would alter part of the array formula. A safer sequence is to paste a copy into a separate blank range, verify it, and then clear the original array. If you need the preserved values in the original location, plan where to place them before removing the formula, since clearing it also clears its results.

Common errors and how to resolve them

  • “You can’t change part of an array.” Select the whole legacy array with Go To Special > Current array. For a dynamic array, edit or clear the anchor cell instead of a spill cell.
  • The spill range is blocked. A #SPILL! error usually means Excel cannot place the dynamic results in the required cells, often because something occupies part of the destination range. If your goal is to delete the formula, clear the anchor. If your goal is to restore the results, inspect the indicated spill area and move or clear only content you are sure is not needed.
  • Only one value disappeared—or nothing changed. Check whether you selected the anchor formula or the full legacy array. A displayed value may be a spill result rather than a formula of its own.
  • Formatting remains after clearing. Delete clears contents, not necessarily formatting, comments, or other cell features. If you also need to remove formatting, use Home > Clear > Clear All only after checking what else it will erase.

Before clearing a formula in a workbook with linked calculations, inspect formulas that depend on its output. Removing an array can cause dependent cells to display errors such as #REF!, or can change totals and charts. If a result is important, save a copy of the workbook first. You can usually reverse a recent clear with Ctrl+Z on Windows, but undo history can be lost after other actions or after closing the workbook.

Windows, Mac, and Excel for the web

The steps above describe Excel for Microsoft 365 on Windows. On Windows, the Delete key clears selected cell contents; Ctrl+Z undoes the most recent action. On a Mac, use the Excel ribbon’s Home > Clear > Clear Contents command if the keyboard’s Delete or Backspace behavior is unclear. Command+Z is the usual Mac undo shortcut. Ribbon labels and the location of commands can vary slightly with the Excel version and window layout, but selecting the complete legacy array remains the important requirement.

Excel for the web supports many modern dynamic-array calculations, but workbook features and editing options can differ from the desktop app. If the web version does not let you select or change a legacy array as needed, open the workbook in the desktop version of Excel and use Go To Special > Current array. If a workbook is important, make sure your edits are saved and synced before switching apps.

Frequently Asked Questions

Can I delete just one cell from an array formula?
No, not while it is part of the array. A legacy array must be selected and cleared as a whole, and a dynamic array’s individual spill results cannot be cleared independently. Edit the anchor formula or replace the results with values in a separate range if you need a different outcome.

How do I remove an array formula without deleting the results?
Copy the full output range to another location and use Home > Paste > Paste Values. Verify the copied values, then clear the original formula. The pasted values are fixed snapshots and will not recalculate when inputs change.

Why does Excel say I cannot change part of an array?
You selected fewer than all the cells belonging to a legacy array, or you tried to alter a dynamic spill result instead of its anchor. For a legacy formula, select a member cell and choose Home > Find & Select > Go To Special > Current array. For a dynamic formula, work from its anchor cell.

Does pressing Delete remove the formatting too?
Usually no. Pressing Delete clears the selected cell contents, including a formula or value, while leaving formatting in place. Use Home > Clear > Clear All only when you also intend to remove formatting and other cell contents or features covered by that command.

Can I undo deleting an array formula?
Immediately after clearing it, press Ctrl+Z on Windows or Command+Z on Mac. If you have already performed other actions, closed the workbook, or lost the undo history, recovery may not be available; a saved backup or earlier workbook version may be needed.

Related

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.