If an Excel array formula is not working, first check whether it needs to spill into empty cells or be entered as a legacy array formula with Ctrl+Shift+Enter. In Microsoft 365 for Windows, most modern array formulas need only Enter; blocked spill ranges, incompatible Excel versions, incorrect references, and calculation settings are frequent causes of trouble.
How array formulas work in current Excel
An array formula works with multiple values at once. In Microsoft 365, dynamic array formulas can return several results, and Excel places those results in neighboring cells automatically. The formula goes in one cell, called the anchor cell; the cells filled with its results make up the spill range. A thin outline may appear around that range when you select the formula cell.
For example, a function such as FILTER can return every row that meets a condition rather than requiring a separate formula for each row. Other formulas, including calculations that combine two ranges, can also return arrays. Some functions accept arrays internally but return one answer: SUMPRODUCT, for instance, can multiply corresponding values and add the products.
Older Excel versions use a different approach for many array calculations. A legacy array formula may need to be entered with Ctrl+Shift+Enter (CSE), and some older formulas must be placed into a selected output range. This difference is important: pressing CSE for every formula in Microsoft 365 is not a general fix, and pressing Enter for a legacy formula in an older version may not produce the intended result.
Check whether the formula should spill
If a dynamic formula returns only one value or displays #SPILL!, inspect the cells where its results need to go. A non-empty cell in the way can block the output, even if it appears blank because it contains a space or a formula returning an empty string. Clear the obstructing cell, or move the formula to an area with enough room. Do not delete data until you have confirmed it is safe to remove.
- Select the formula cell and check whether Excel shows a spill-range outline or a
#SPILL!warning. - Inspect the expected output cells for existing values, formulas, spaces, or merged cells.
- Make sure the spill range is not inside an Excel table. Dynamic array formulas cannot spill within table cells; place the formula outside the table instead.
- Check that the result has enough rows and columns available. A formula that returns 100 rows cannot spill into a range blocked after row 20.
When Excel identifies a spill obstruction, selecting the warning icon beside the cell can show a reason. A formula may also return an error for a different reason, so read the displayed error rather than assuming every array issue is a blocked spill.
Enter modern and legacy formulas the right way
In Microsoft 365, enter a dynamic array formula in its anchor cell and press Enter. Do not select several output cells first unless the particular instructions for an older formula require that. The results of a spilled formula are controlled by the anchor formula; you generally cannot edit an individual result cell separately.
With a legacy array formula, the entry method depends on what the formula is designed to return. A single-result array calculation may be entered in one cell using Ctrl+Shift+Enter. A formula designed to return several results may require selecting the destination range first, entering the formula, and then pressing Ctrl+Shift+Enter. Excel displays braces around many legacy array formulas in the formula bar; those braces are added by Excel, not typed by hand. To edit a multi-cell legacy array formula, you may need to select the entire array range first.
On Excel for Mac, the legacy array-entry shortcut is typically Control+Shift+Return; keyboard settings and versions can affect shortcuts. Dynamic array formulas in current Microsoft 365 for Mac are normally entered with Return, just as modern formulas on Windows are entered with Enter. Excel for the web supports many dynamic array functions, but keyboard shortcuts and some workbook features can differ from the desktop app.
Look for formula and reference mistakes
Array formulas are sensitive to range dimensions. When a calculation pairs values from two ranges, those ranges should generally have matching shapes. For example, multiplying a five-row range by a four-row range cannot produce a straightforward row-by-row result. Confirm the first and last rows, and check whether an inserted row or column changed only one reference.
Also verify that the formula is actually being treated as a formula. If Excel displays the characters =SUM(A1:A3) in the cell instead of a result, the cell may be formatted as Text, the formula may start with an apostrophe, or there may be a space before the equals sign. Change the cell format to General, remove any leading apostrophe or space, then press F2 and Enter to re-enter the formula. If many formulas appear as text at once, check whether Show Formulas is enabled; Ctrl+` toggles that view in many Windows layouts.
Argument separators can vary with regional settings. The examples here use commas, as in =SUM(A1:A3). If Excel rejects a formula copied from an example, your installation may expect semicolons between arguments. Use the separator Excel accepts in your locale, and retain commas that are part of quoted text or other formula content only when the syntax requires them.
Understand common errors and what they indicate
#SPILL!usually means Excel cannot place a dynamic result in its intended range. Look for obstructing data, merged cells, table placement, or an output range that extends beyond available worksheet space.#VALUE!often points to an incompatible value or calculation input. Check for text where a number is expected, mismatched array dimensions, and errors already present in the referenced cells.#NAME?can mean Excel does not recognize a function name. Check spelling and whether your Excel version supports that function.#REF!indicates an invalid reference, which can happen if a referenced row, column, or sheet was deleted or a formula was copied in a way that changed its references.- A blank-looking result may be intentional. A formula can return an empty string, or a number format can hide a displayed value. Check the formula bar and cell format before concluding that calculation failed.
Functions such as FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP, XMATCH, and LET require Excel 2021 or Microsoft 365. A workbook opened in an older edition may show an unsupported-function error or fail to calculate the intended result. TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, HSTACK, and LAMBDA require Microsoft 365 or Excel 2024. Availability can also depend on update channel and platform, so confirm the version used by everyone who needs to open the workbook.
Use small examples to isolate the cause
A short test can reveal whether the problem lies in the formula, the source data, or the worksheet layout. Suppose A2:A5 contains East, West, East, North, while B2:B5 contains 10, 7, 12, 5. With those values in place and an open area below the formula, enter this in D2:
With regions in A2:A5 and amounts in B2:B5, this formula returns the amounts for East in D2:D3: =FILTER(B2:B5,A2:A5="East"). The expected results are 10 and 12. If it works in a new blank area but not in the original location, inspect the original spill range for obstructions or table placement.
A formula that returns a single count can help test whether the condition and source ranges make sense without needing a spill range. With regions in A2:A5 and amounts in B2:B5, this formula returns 2 because East appears twice: =SUMPRODUCT(--(A2:A5="East")). If that result is wrong, check the spelling and extra spaces in the region values. The count does not depend on an output area below the formula being clear.
For a more reliable check, compare the formula’s result with a few rows calculated by hand. If the formula uses criteria, test one matching and one non-matching row. If it multiplies arrays, verify a single pair of corresponding values. This is often quicker than replacing a complex formula before identifying what part has gone wrong.
Check calculation settings and workbook behavior
If formulas remain unchanged after you edit their inputs, the workbook may be using manual calculation. In Excel for Microsoft 365 on Windows, go to Formulas > Calculation Options and select Automatic. With manual calculation, press F9 to recalculate open workbooks; Shift+F9 recalculates the active worksheet. On a Mac, use the Formulas tab to find calculation options; available shortcuts can differ by keyboard and Excel version.
Calculation settings can affect an entire workbook session, so changing them may also update other open workbooks. Before relying on a result, verify that dependent cells recalculate and that the displayed values are current. If only one formula is incorrect while other formulas update normally, a manual calculation setting is less likely to be the cause than a reference, data, or spill-range issue.
A practical troubleshooting order
Work from the simplest checks toward changes that could affect more of the workbook:
- Read the cell’s exact result or error, and inspect the formula bar for the formula Excel is evaluating.
- Confirm your Excel version supports every function in the formula.
- Check the ranges, criteria, and cell values, including unexpected spaces and text-formatted numbers.
- For a dynamic result, clear or relocate obstructions and keep the formula outside a table if it needs to spill.
- For a legacy formula, follow its CSE or selected-range instructions rather than applying a modern spill workflow.
- Check calculation mode if input changes are not updating formulas, then test a simplified version in a blank area.
Keep a copy of the original formula before changing it, especially in a shared or important workbook. If a formula works in a test cell but not in its original location, compare the two locations’ formats, surrounding cells, and references; the contrast usually narrows the cause without disturbing the source data.
Frequently Asked Questions
Do I still need Ctrl+Shift+Enter in Excel 365?
Usually not for modern dynamic array formulas. Enter them in one cell and press Enter. CSE is mainly relevant when working with legacy array formulas or older Excel versions that require it.
Why does my array formula show only one result?
It may be a formula designed to return one answer, or it may be a legacy formula used in a version that does not support dynamic spilling. If a modern formula should return multiple values, check for an error, a blocked output range, or a table that contains the formula.
Can I type the braces around a legacy array formula?
No. Excel adds braces when a legacy array formula is committed with the required method. Typing braces yourself does not enter the formula as an array formula.
Why does FILTER return #SPILL!?
Excel may be unable to place all returned rows in the cells below or beside the formula. Clear obstructing cells, check for merged cells, and move the formula outside an Excel table if necessary.
Why does an array formula work on one computer but not another?
The computers may have different Excel versions, update levels, or regional formula separators. A newer function may not exist in an older edition, while different regional settings can require different argument separators.
Related
- Excel Named Range Lookup: Find and Use Named Ranges
- Excel Named Range Greyed Out? Causes and Fixes to Try
- Excel Named Data Range: Create, Use, and Manage Names
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- YEAR Formula Not Working? Excel FixesIf Excel’s YEAR formula is not working, first check that its reference contains a real Excel date, not…
- Excel Arrow Keys Not Moving Cells? Fix ItIf Excel’s arrow keys are scrolling the worksheet instead of moving the active cell, turn off Scroll Lock:…
- Excel LET Not Working? Common Errors and How to Fix ThemExcel LET usually fails because the installed Excel version does not support it, the formula has the wrong…
- Google Sheets Date Format Not Working? Here’s How to Fix ItIf Google Sheets date formatting is not working, first check whether the cell contains a real date value…
- Excel Not Recognizing Numbers? Fix Text-Formatted CellsExcel usually fails to recognize numbers because the cells contain text that looks numeric, often after a paste…
- Microsoft Excel Circular Reference Error: How to Fix ItA Microsoft Excel circular reference error means a formula depends on its own result, either directly or through…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.