Array Function Not Working in Excel? Fixes That Work

Errors & fixes · Updated November 19, 2026

If an array function is not working in Excel, first check whether your Excel version supports it, whether the formula has room to spill, and whether an older array formula needs Ctrl+Shift+Enter. In Microsoft 365 for Windows, most array formulas calculate by pressing Enter; the error message or result usually points to the specific fix.

What Excel means by an array formula

An array is a set of values, such as a range of cells or the results of a calculation across several cells. An array formula works with multiple values at once. Some formulas return one answer, while dynamic array formulas can return a whole range of results that fills neighboring cells automatically.

Excel has two broad array behaviors. Older, or legacy, array formulas may need to be entered into a selected range with Ctrl+Shift+Enter. Newer dynamic arrays, available in Microsoft 365 and Excel 2021 or later for functions such as FILTER and SEQUENCE, generally spill their results from one formula cell. A workbook created in an older Excel version may still contain legacy formulas even when opened in Microsoft 365.

For example, with quantities in B2:B4 and unit prices in C2:C4, a calculation can multiply corresponding cells and add the products. If the rows contain 2 and 5, 3 and 6, and 4 and 7, the result is 56: 2×5 + 3×6 + 4×7. In current Excel, the formula can be entered normally. In older Excel, it may require the legacy array-entry method.

Check the Excel version and formula name

A formula may fail simply because the installed Excel version does not include that function. FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP, XMATCH, and LET require Excel 2021 or Microsoft 365. LAMBDA, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, and HSTACK require Microsoft 365 or Excel 2024. Excel 2019 and earlier do not gain these functions just because a workbook was saved in a newer version.

When Excel does not recognize a function, it may display #NAME?. Check the spelling, confirm that the function exists in your version, and ensure the function name is not enclosed in quotation marks. If a newer function is unavailable, use an older function or a helper column where practical. For example, a lookup can sometimes be rebuilt with INDEX and MATCH, while filtering can be done with Excel’s built-in filter controls, though those alternatives do not necessarily produce the same live spilled result.

In Excel for the web, many Microsoft 365 dynamic array functions work, but some workbook features and editing options can differ from desktop Excel. On Mac, the function availability depends on the Excel version and update channel; check the version under Excel > About Microsoft Excel.

Enter the formula the right way

In current Microsoft 365 Excel for Windows, enter a dynamic array formula in one cell and press Enter. Do not select a large output range first unless the particular formula or instruction specifically requires it. Excel determines the result size and places the returned values in adjacent cells.

For example, with source records in A2:C20 and status values in C2:C20, enter this formula in an empty cell to return rows whose status is Open:

With records in A2:C20 and statuses in C2:C20, enter =FILTER(A2:C20,C2:C20="Open","No matches"). It returns every matching row across the three source columns, or the text No matches if no row qualifies.

Legacy array formulas are different. Select the intended output range if the formula returns several cells, type the formula, and press Ctrl+Shift+Enter on Windows. Excel displays braces around the formula in the formula bar to indicate array entry; do not type those braces yourself. In Excel for Mac, the corresponding legacy entry is generally Control+Shift+Return. New dynamic array formulas should not be forced into this older entry method.

Resolve #SPILL! errors

#SPILL! means Excel cannot put the dynamic array results where they need to go. Select the formula cell and use the error indicator, if shown, to see the cause. The most common fix is to clear cells in the expected output area. Existing values, formulas, or even a cell containing a space can block the spill.

  • Clear or move content from the cells blocking the output.
  • Check for merged cells in the spill area; unmerge them if appropriate.
  • Make sure the formula is not inside an Excel table when it needs to spill. Dynamic array formulas generally spill outside tables, so place the formula in a regular worksheet range.
  • Check whether the result would extend beyond the worksheet edge.

For instance, with a number entered in A1, a formula intended to return three cells downward cannot spill if A2 or A3 already contains data. Clearing those cells allows the result to appear. A spill range is controlled by its top-left formula cell; editing an individual returned cell is not the way to change one result.

To generate 1, 2, and 3 vertically, with the formula entered in E2 and cells E3:E4 empty, use =SEQUENCE(3,1,1,1). It returns three rows and one column. If any destination cell is occupied, Excel reports a spill problem rather than silently overwriting it.

Understand common error messages

The error text is useful evidence, not just a failure label. #SPILL! usually concerns the output area. #NAME? often means an unsupported or misspelled function. #VALUE! commonly signals an incompatible value or an argument problem. #REF! indicates an invalid reference, which can happen after referenced cells or sheets are deleted. A formula that returns #CALC! may have no results to return or may encounter a calculation case the function cannot handle.

Dynamic formulas can also show an unexpected single value if Excel applies implicit intersection. In newer Excel, the @ symbol in a formula can indicate that Excel is reducing an array result to one value for compatibility. If you expected multiple results, inspect the formula for @ and confirm that the function and formula context are intended to return an array. Do not remove the symbol blindly: it may preserve how an older workbook was designed to calculate.

Check ranges for matching dimensions as well. A formula that compares one range with another often expects corresponding ranges to have the same number of rows. A mismatch can lead to an error or an unintended result, depending on the function and expression.

Check calculation settings and source data

If the formula is present but its answer does not update, check whether calculation is set to Automatic. In Excel for Windows, select Formulas > Calculation Options > Automatic. This setting can be workbook-dependent. After changing it, press F9 to recalculate formulas; on Mac, use Fn+F9 if the function keys are configured as media keys, or use the Excel calculation command. Be aware that a large workbook may take time to recalculate.

Also inspect the source values. Numbers stored as text may not behave like numeric values in a calculation, and cells that look blank may contain spaces or formulas returning an empty string. Dates can also be text rather than actual Excel date values. Use a few source cells as a small test before assuming the array function itself is broken.

Confirm that references point to the intended rows and columns, and that copied formulas have not shifted their ranges. Dollar signs make a reference absolute when copied, but they do not repair a range that points to the wrong data. If a formula is part of a table, remember that table calculated columns and spilled arrays follow different behaviors.

Choose between a dynamic array and a legacy formula

Use a dynamic array formula when you want results to expand or shrink automatically as the source data changes. FILTER is useful for returning records that meet a condition; UNIQUE can list distinct entries; SORT can order a result. These functions are especially convenient for reports because one formula can produce a changing output range.

Use a legacy array formula only when compatibility with an older workbook or Excel release requires it. Legacy formulas can be harder to edit because their output ranges are fixed, and changing one cell in a multi-cell array may be restricted. If you need compatibility with older versions, test the workbook in the actual target version rather than assuming a current function will translate.

For simple totals involving paired ranges, SUMPRODUCT is often a readable alternative to a legacy Ctrl+Shift+Enter formula. With quantities in B2:B4 and prices in C2:C4, enter =SUMPRODUCT(B2:B4,C2:C4). For the sample values 2, 3, 4 and 5, 6, 7, it returns 56. The ranges must correspond row by row for this example to represent the intended multiplication and total.

A practical troubleshooting sequence

Work through these checks in order rather than rewriting a complicated formula immediately:

  1. Read the exact error, and note whether the formula returns an error, one value, or no visible result.
  2. Confirm the Excel version supports the function and check its spelling and argument separators.
  3. For a modern dynamic formula, enter it in one cell with Enter; for a confirmed legacy formula, use the required array-entry method.
  4. If Excel shows #SPILL!, inspect and clear the intended output area, including merged cells and table placement.
  5. Check calculation mode, source data types, range dimensions, and references.
  6. Test a shortened version of the formula on a few rows, then restore the full ranges once the cause is clear.

On Windows, F2 lets you edit the selected formula cell and Esc cancels an edit. On Mac, the equivalent edit shortcut is commonly Control+U, while Esc cancels. Shortcuts can vary with keyboard settings, so the formula bar is another way to inspect or edit a formula without changing its calculation.

Frequently Asked Questions

Why does my array formula show only one result?
It may be a legacy formula, an implicit-intersection calculation, or a formula whose conditions match only one item. In Microsoft 365, check whether the function should return multiple values, look for an @ in the formula, and confirm that the destination cells are available.

Do I still need Ctrl+Shift+Enter in Excel 365?
Usually not for modern dynamic array formulas. Press Enter for functions such as FILTER or SEQUENCE. Ctrl+Shift+Enter is for legacy array formulas that require it, often because a workbook or formula is designed for older Excel behavior.

Why does FILTER return #CALC! when there are no matches?
Without a specified empty-result value, FILTER can return #CALC! when nothing meets the condition. Add an appropriate third argument, such as No matches, or verify that the criteria text exactly matches the source values. Spaces and inconsistent spelling can prevent a match.

Can an array formula spill inside an Excel table?
A dynamic array formula generally cannot spill from within a table’s data area. Put the formula in a normal worksheet range outside the table, leaving the expected output cells clear. A table can still be the source data for the formula.

Why is a formula not recalculating after I change a value?
Check Formulas > Calculation Options > Automatic on Windows. If calculation is manual, switch it to Automatic or recalculate with F9. Also check whether the displayed value is formatted in a way that hides a small change, or whether the changed cell is actually included in the formula’s reference.

When the formula still fails

If none of these checks resolves the problem, make a copy of the workbook and reduce the formula to a small reproducible example. Record the Excel version, exact formula, error text, and a few representative input values. This makes it easier to distinguish a version limitation from a blocked spill range, a data issue, or a formula logic error. Avoid replacing a working calculation with a different function until you have compared the results on known sample data.

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.