How to Fix an Array in Excel and Repair Formula Errors

To fix an array in Excel, identify whether it is a modern spilled formula or a legacy Ctrl+Shift+Enter formula, then edit the formula’s source cell or correct the full legacy array range. If a spill formula shows #SPILL!, clear the cells blocking its output. Before changing anything, save a copy of the workbook so you can restore the original if needed.

What You’ll Need

Excel for Microsoft 365 on Windows, or a version that supports the array formula you are repairing. Some newer functions are available only in Microsoft 365 or specific newer releases.
The workbook containing the formula, plus permission to edit it. If the sheet is protected, you may need the password or help from its owner.
A spare copy of the workbook or a known-good copy of the formula, especially if the array feeds other calculations.

Step-by-Step

Save a working copy and locate the error. Use File > Save As to create a separate copy before editing. Note the affected cell, the exact error text, and whether other cells depending on it are also wrong. Click the cell and read the formula bar; the visible value alone does not tell you whether Excel is calculating an array or displaying a fixed result. If the formula bar is hidden, turn on View > Show > Formula Bar.
Check what kind of array formula you have. Select a cell that appears to contain the formula. A modern dynamic array formula is entered in one source cell and can spill results into adjacent cells. Selecting a result cell in its spill range usually shows a formula that cannot be edited separately. A legacy array formula was traditionally entered with Ctrl+Shift+Enter and may occupy a selected block of cells; Excel displays braces around it in the formula bar, but you do not type those braces yourself. If in doubt, select the cells and try to identify the source cell or the full legacy range before changing anything.
Read the specific error instead of replacing the formula immediately. #SPILL! usually means Excel cannot place a dynamic result in the required cells. #VALUE! can indicate incompatible values or dimensions, while #REF! points to a broken reference. A wrong-looking answer may instead come from incorrect input data, an unintended blank, or a formula copied with relative references. Click the warning icon, if present, and review Excel’s explanation before deciding what to change.
Unblock a dynamic array spill range. Select the cell showing #SPILL! and look for Excel’s dashed outline showing the intended output area. Check every cell inside that area for values, spaces, formulas, merged cells, or other obstructions. Select only the unwanted obstruction and press Delete; do not delete the source formula. If a cell looks blank but blocks the result, select it and check the formula bar for a space or an invisible character. If the obstruction is a merged cell, select the merged area and use Home > Merge & Center to unmerge it, then reassess the layout.
Edit a modern array formula at its source. Click the top-left formula cell, make the correction in the formula bar or press F2 to edit in place, and press Enter. Do not try to type over an individual spilled result: change the source formula instead. For a concrete example, with numbers in B2:B6 and matching quantities in C2:C6, entering =B2:B6*C2:C6 in D2 in Microsoft 365 returns five results in D2:D6. If B2:B6 contains 2, 3, 7, 4, 5 and C2:C6 contains 5, 8, 3, 8, 9, the results are 10, 24, 21, 32, and 45. Make sure D3:D6 are clear before entering the formula. If the formula is inside an Excel table, move it outside the table because dynamic arrays do not spill within table cells.
Repair a legacy array formula as one unit. First select the entire array range, not just one cell. You can often click a cell in the array and use Home > Find & Select > Go To Special > Current array to select its full range. Press F2 to edit the formula, make the correction, then press Ctrl+Shift+Enter to confirm it as a legacy array formula. On Mac, use Command+Shift+Return where the installed Excel version uses the legacy array-entry shortcut. If you need to replace or remove the formula, select the whole array range first; Excel normally prevents editing or deleting just one cell in a legacy array.
Check the formula’s ranges and dimensions. Array calculations usually need compatible ranges. For example, if one part of a formula refers to B2:B6 and another to C2:C5, the ranges have different lengths and may produce an error or an unintended result. For a total of the five pairwise products in the example above, with values in B2:B6 and C2:C6, enter =SUM(B2:B6*C2:C6) in D2. The example data returns 132 because 10 + 24 + 21 + 32 + 45 = 132. In current Microsoft 365, press Enter; in older Excel versions that require legacy array entry for this calculation, confirm with Ctrl+Shift+Enter. Check that the calculation matches the intended rows before using it elsewhere.
Verify the result and restore dependent formulas if needed. Compare a few output cells with hand-calculated results, then check the cells that rely on the array. If you changed a range, confirm that it includes all intended records and excludes headings or totals. If the output is displayed as text or the formula appears unchanged, check that the cell is not formatted as Text: select it, choose Home > Number Format > General, then edit the formula and press Enter. Save the workbook after confirming the results.

Common Mistakes

Editing a single cell in a legacy array. A legacy array formula applies to its selected block. Trying to change one cell can produce a message that part of an array cannot be changed. Select the entire array and edit or remove it as a unit.
Deleting the formula source instead of clearing the blocker. With #SPILL!, the formula is usually still valid. Keep the top-left source cell and remove or move the content preventing the result from spilling.
Typing curly braces around a formula. The braces shown for legacy arrays are Excel’s indicator, not characters to enter. Use the legacy array-entry shortcut when required.
Using ranges that do not line up. Check their starting rows and number of cells. Similar-looking ranges can still include different rows or a header by mistake.
Assuming every array formula works in every Excel version. Dynamic arrays and functions such as FILTER and UNIQUE are not available in every older release. Opening a workbook in an older version may show compatibility problems or an unfamiliar formula.

Tips

Use the formula bar to inspect the exact formula, and use Formulas > Formula Auditing > Show Formulas to display formulas on the worksheet instead of their results. Select the command again to return to the normal view.
When a spill is blocked, keep the intended output area clear. Put notes, totals, or manually entered values outside that area so future results have room to expand.
For a dynamic formula, make changes only in its source cell. If you need a fixed value rather than a live result, copy the spill range and use Home > Paste > Paste Values in a separate location.
Before replacing a working formula, copy it to a note or a spare cell in the backup workbook. This makes it easier to compare the original and revised references without relying on memory.
Excel uses regional settings for formula separators. The examples here use commas; if Excel expects semicolons on your computer, use the separator shown by your installation when entering a formula.

Troubleshooting

The source cell still shows #SPILL!: Select it and inspect the outlined spill area again. Check for cells containing formulas that return an empty string, spaces, merged cells, or objects that overlap the output. Clear or move only the blocking content. If the formula is inside a table, move it to a normal worksheet range. If Excel reports that the spill range is too large, review the formula’s referenced range and avoid asking it to return more rows or columns than intended.

The formula returns #VALUE! or the wrong number of results: Check that the inputs are numeric where arithmetic is expected, and compare the size and alignment of all referenced ranges. Look for text stored in number cells, error values in source data, and one range that starts or ends on the wrong row. Test a small portion of the data in a spare area before applying a correction to the full formula.

You cannot edit or clear the formula: If Excel says part of an array cannot be changed, select the full legacy array range using Home > Find & Select > Go To Special > Current array. If the worksheet is protected, use Review > Unprotect Sheet if you have permission. For a dynamic array, edit the source cell rather than a result cell. If the source is on another sheet, navigate to that cell and check whether the workbook is read-only.

The formula is visible as text or does not recalculate: Change the cell format from Text to General, edit the formula, and press Enter. If the worksheet displays formulas everywhere, turn off Formulas > Formula Auditing > Show Formulas. If a correct formula shows an old result, check Formulas > Calculation Options and select Automatic if that is appropriate for the workbook; then press F9 to recalculate. On Mac, use the Formulas tab for calculation settings; shortcut keys may differ by keyboard and Excel version.

The formula works in one version but not another: Confirm which Excel versions will open the workbook. Newer functions may not be recognized by older desktop releases. If collaborators use older Excel, consider a compatible formula or provide a values-only copy for viewing, while retaining the working formula in the main file.

Frequently Asked Questions

How do I know whether a formula is an array formula?

A dynamic array formula is entered in one source cell and its results spill into nearby cells. A legacy array formula may show braces in the formula bar and occupy a multi-cell range. Click a result cell: if Excel identifies a spill range, edit its source cell; if it says part of an array cannot be changed, select the whole legacy array.

Can I change just one result in a spilled array?

No. The individual results are produced by the source formula, so you cannot type over one of them. Change the formula or its input data. If you need to alter the results independently, copy them to another range and paste values there first.

Why does my array formula show #SPILL! when nearby cells look empty?

A cell can contain a space, an empty-looking formula, or another invisible entry and still block a spill. Select the source cell to see Excel’s outlined spill range, then inspect each cell in that area. Merged cells and a spill formula placed inside a table can also prevent the results from appearing.

Do I always need Ctrl+Shift+Enter for an array formula?

No. Microsoft 365 supports dynamic arrays, and many array calculations can be entered with Enter. Ctrl+Shift+Enter is mainly needed when entering or repairing a legacy array formula in Excel versions that use that method. Follow the formula’s existing structure and the requirements of the Excel version used to open the file.

Can I delete an array formula without damaging other cells?

Yes, but first identify its full affected range. Select the complete legacy array before deleting it, or clear the source cell of a dynamic array to remove its spill results. Save a backup first, and check dependent formulas afterward because they may refer to the cells you removed.

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.