To delete an array in Excel, remove its formula: select the entire output range for a legacy array formula and press Delete; for a dynamic array, select its top-left formula cell and press Delete. If you are unsure which kind you have, identify the formula first so you do not try to clear just one cell of a multi-cell array.
First, identify the array type
Excel has two common kinds of array formulas, and the way you delete them differs. Legacy array formulas, sometimes called Ctrl+Shift+Enter or CSE formulas, were entered into a selected range as one formula. Dynamic array formulas are entered in one cell and spill their results into neighboring cells automatically. Microsoft 365 supports dynamic arrays, while older workbooks may still contain legacy formulas.
Click a cell showing an array result and look at the formula bar. If the formula appears in braces, such as {=A2:A6*B2:B6}, it is a legacy array formula; the braces are added by Excel and are not typed as part of the formula. If the formula appears only when you click the top-left result cell, and the other result cells cannot be edited, it is likely a dynamic array. You can also click a spill result cell: Excel displays a colored outline around the spill range, while the formula bar for a non-anchor spill cell does not contain the formula.
Knowing the type matters because the visible results are not necessarily separate cell contents. In either kind, some or all of the displayed cells may be controlled by a single formula. Attempting to remove just one result can produce a warning or leave the underlying formula in place.
Delete a legacy array formula
A legacy array formula must be cleared as a complete range. If it occupies C2:C6, selecting only C3 and pressing Delete will not remove that individual result; Excel typically reports that you cannot change part of an array. Select every cell in the formula’s output range, then press Delete. This clears the formula and its results.
- Click any cell inside the legacy array formula.
- Select the full array range. On Windows, press Ctrl+/ (forward slash) to select the current array formula, if that shortcut works with your keyboard layout. Alternatively, select the known range directly in the worksheet.
- Check that the selection includes every cell in the array, not just the active cell.
- Press Delete. To clear contents through the ribbon instead, use Home > Clear > Clear Contents.
For example, if the formula fills C2:C6, select C2:C6 before clearing it. Excel treats the range as a unit even though each cell displays a separate result. If you do not know the range, click one of its cells and use Home > Find & Select > Go To Special, then choose Current array and select OK. The exact menu layout can vary by Excel version; manually selecting the full range is a reliable alternative. On Mac, use the ribbon’s Go To Special option if available, or select the array range directly; Windows Ctrl+/ is not a dependable Mac instruction.
Delete a dynamic array formula
A dynamic array formula lives in one anchor cell and spills results into adjacent cells. To remove the formula and all results it generates, select that anchor cell—the top-left cell of the spill range—and press Delete. You can also use Home > Clear > Clear Contents. The spill results disappear because they depend on the anchor formula.
For a concrete example, assume A2:A6 contains the unsorted numbers 4, 1, 5, 2, and 3. Enter this formula in C2:
=SORT(A2:A6)
In Microsoft 365, the formula in C2 returns 1, 2, 3, 4, and 5 down the cells below it. To delete those results, clear C2, not one of the cells underneath it. If you select a cell in the spill range and try to delete or type into it, Excel may tell you that you cannot change part of an array. Find the anchor cell by following the spill outline to its top-left corner, or click a spill result and inspect the formula bar and worksheet selection.
Deleting the anchor removes the formula but does not delete the worksheet cells or shift nearby data. It clears the formula’s output; formats applied to those cells may remain. If you only want to stop the formula from returning results temporarily, consider editing or moving the formula instead of deleting it.
Keep the displayed results but remove the formula
Sometimes “delete the array” means you want to keep its current numbers or text but no longer have them update. In that case, convert the results to fixed values rather than clearing them. This is useful before sharing a report, archiving a calculation, or editing individual cells that were previously controlled by an array formula.
- For a legacy formula, select its entire output range. For a dynamic formula, select the full spill range, including the anchor cell.
- Copy the selection with Ctrl+C on Windows or Command+C on Mac.
- Without changing the selection, choose Home > Paste > Paste Values. Depending on your ribbon layout, Paste Values may appear as a clipboard icon or in the Paste menu.
- Click a cell and check the formula bar. The cells should now contain fixed values rather than the array formula.
Paste Values replaces formulas with the results currently displayed, so later changes to source cells will no longer update those results. Check that the selected range is correct before pasting: pasting values over an array formula is a deliberate replacement, and Undo may help if you act immediately, but it is safer to save a copy of important workbooks first. If the results include errors, Paste Values will preserve the displayed error values rather than repair them.
Fix common deletion problems
- “You can’t change part of an array.” You selected only part of a legacy array range or a dynamic spill range. For a legacy formula, select the whole array. For a dynamic formula, clear the anchor cell to remove the spill, or select the whole spill range if converting its results to values.
- The results return after you clear a cell. You likely cleared a spill result instead of the formula’s anchor cell, or the formula is still present in another cell. Locate the anchor and clear it.
- Excel reports #SPILL!. This is not a deletion error. A dynamic formula cannot place its results because something obstructs the required spill range, such as existing content or merged cells. If you want to keep the formula, clear or move the obstruction. If you want to remove the array, clear the formula in the anchor cell.
- The surrounding formatting remains. The Delete key clears cell contents, not formatting. That is usually desirable when removing a formula. To remove formatting as well, select the cells and use Home > Clear > Clear Formats; be cautious because this also removes number formats and other styling.
- Nearby cells move or disappear. You may have used the worksheet’s Delete command to delete cells or a row, rather than pressing the Delete key to clear contents. Deleting cells can shift other data. To remove only a formula, select the relevant array cell or range and clear its contents.
Before clearing anything, check whether the cells contain other information. A legacy array’s full range may include cells you did not expect, and a spill range can expand when its source data changes. Selecting the formula range carefully helps prevent accidental removal of notes, inputs, or neighboring calculations.
Clear contents, delete cells, or remove a formula?
These actions are different. Pressing Delete or choosing Clear Contents removes formulas and values from selected cells while leaving the cells themselves in place. The cells retain their formatting, and other worksheet content does not shift. This is generally the right action when your goal is to remove an array formula.
Deleting cells is a structural change: Excel may shift surrounding cells up or left, depending on the option you choose. Deleting an entire row or column changes the worksheet structure too. Use those commands only when you intend to remove the cells themselves, not simply the contents of an array.
Removing a formula while preserving its results is a third action: copy the complete output and use Paste Values. Afterward, the results are ordinary values and can be edited individually. They no longer recalculate from the original source data. Choose among these actions based on whether you want to remove the result, preserve a snapshot, or eliminate worksheet cells.
Frequently Asked Questions
Can I delete just one cell from an array formula?
No, not while that cell remains part of the array. A legacy CSE formula must be cleared or replaced as a complete range. A dynamic array’s spill results are controlled by its anchor formula. To keep selected results as independent cells, copy the output and paste values first.
How do I delete a spilled array without deleting its source data?
Clear the formula in the spill range’s top-left anchor cell. This removes the generated results but leaves the source cells used by the formula untouched. If the formula itself is in a source cell, consider moving or editing it carefully so you do not remove data you still need.
Does pressing Delete remove formatting?
No. In Excel, pressing Delete clears the selected cells’ contents, such as formulas and values, but usually leaves formatting in place. Use Home > Clear > Clear Formats only if you also intend to remove styling.
Can I remove the formula and keep its current results?
Yes. Copy the entire legacy array range or dynamic spill range, then use Home > Paste > Paste Values. The displayed results become fixed values, so they will not change when the original inputs change.
Why can’t I edit a cell inside an array?
Excel protects cells that are controlled by a multi-cell array formula. With a legacy formula, select the full array range to change or clear it. With a dynamic array, edit or delete the anchor formula; spill cells cannot be edited independently while they belong to that spill.
Related
- Excel Nested Group Rows: How to Create and Use Them
- Excel Named Ranges: How They Work and When to Use Them
- Excel Named Range Table: How They Work and When to Use
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMPRODUCT Excel: Calculate a Weighted AverageIn Excel, calculate a weighted average with =SUMPRODUCT(A2:A5,B2:B5)/SUM(B2:B5), assuming A2:A5 contains the values and B2:B5 contains their weights.…
- Excel Date Plus Weeks: Formulas, Dates, and ExamplesTo add weeks to a date in Excel, multiply the number of weeks by 7 and add the…
- VLOOKUP Excel Using Two Spreadsheets: Steps and TipsYes. VLOOKUP can find a value in one Excel workbook and return related information from another; the workbooks…
- Excel Formula for Age at a Certain Date Using DATEDIFTo calculate someone’s age on a particular date in Excel, use =DATEDIF(A2,B2,"Y"), where A2 contains the birth date…
- Break-Even Excel Formula: Calculate Units and SalesThe basic break-even formula in Excel is fixed costs divided by the contribution margin per unit: =B2/(B3-B4). If…
- Excel Test If Two Cells Are Equal: Formulas and FixesTo test whether two cells are equal in Excel, enter =A1=B1 in another cell, replacing A1 and B1…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.