In Microsoft Excel for Microsoft 365 on Windows, enter a dynamic array formula in one cell and press Enter; Excel will spill its results into nearby cells. To enter a traditional, or legacy, array formula, select the intended result cell or range, type the formula, and press Ctrl+Shift+Enter instead. The right method depends on the formula and the Excel version you need to support.
What an array formula does
An array formula processes multiple values as a group rather than treating every calculation as an isolated, single-cell operation. It can return one answer, such as a total, or a set of answers that occupy several cells. This makes array formulas useful for calculations across ranges, filtering records, and returning lists of matches.
For a concrete example, suppose B2:B4 contains 2, 3, and 4, while C2:C4 contains 10, 20, and 30. Multiplying the two ranges pair by pair produces 20, 60, and 120. An array formula can return those three products or add them together to return a single total of 200. The method you use to enter the calculation determines whether Excel handles the results as a dynamic spill or a legacy array formula.
Array formulas are not a separate kind of cell or worksheet. They are formulas that perform operations on arrays of values. Some regular Excel functions can also process arrays, and newer Excel versions can pass arrays between formulas automatically.
Enter a dynamic array formula
In Microsoft 365, most formulas that return multiple results are dynamic array formulas. Enter the formula in the top-left cell where you want results to appear, then press Enter. Excel places the additional results in adjoining cells, a behavior called spilling. Do not select the whole destination range first.
With 2, 3, and 4 in B2:B4 and 10, 20, and 30 in C2:C4, enter this in D2 and press Enter:
=B2:B4*C2:C4
Excel returns 20 in D2, 60 in D3, and 120 in D4. The formula itself is stored in D2; the other results are part of its spill range. You can see the spill boundary by selecting the formula cell. The formula bar shows the formula in the anchor cell, not separate formulas in each result cell.
To produce one total from the same ranges, enter the following in D2 and press Enter:
=SUM(B2:B4*C2:C4)
The layout is unchanged: B2:B4 contains 2, 3, and 4, and C2:C4 contains 10, 20, and 30. The result is 200, because Excel multiplies the pairs and sums 20 + 60 + 120.
Dynamic arrays are available in Microsoft 365 and Excel 2021 and later. Excel for the web also supports many dynamic array formulas, though some features and behavior can differ from desktop Excel. If a formula needs to work in older editions, check compatibility before relying on automatic spilling.
Enter a legacy array formula with Ctrl+Shift+Enter
Older Excel versions used Ctrl+Shift+Enter, often shortened to CSE, to tell Excel to evaluate a formula as an array formula. This entry method is still relevant when maintaining older workbooks or when a formula must be entered as a legacy array formula in desktop Excel.
- Select the cell where a single answer should appear, or select the full output range if the formula is meant to return several values.
- Type the formula. Do not add curly braces yourself.
- Press Ctrl+Shift+Enter together, rather than pressing Enter alone.
- Check the result. Excel displays curly braces around a legacy array formula in the formula bar, for example {=…}. Those braces are added by Excel and should not be typed into the formula.
Using the same values as before, put this formula in D2 and press Ctrl+Shift+Enter:
=SUM(B2:B4*C2:C4)
With 2, 3, and 4 in B2:B4 and 10, 20, and 30 in C2:C4, the answer is 200. In a legacy version of Excel, CSE tells Excel to perform the multiplication across the ranges before summing. In current Excel, the same formula can generally be entered with Enter because dynamic array calculation handles the array operation.
For a legacy formula that must display several results, select the intended output cells before entering it. For example, if a legacy formula is expected to return three values, select three vertical cells, type the formula, and press Ctrl+Shift+Enter. The output range size matters: selecting too few cells can omit results, while selecting too many can leave extra cells displaying an error or an unwanted value.
Choose the right entry method
Use Enter for a new formula in current Excel unless you have a specific reason to create a legacy array formula. Dynamic arrays are generally easier to edit because the formula is entered once and the spill range can resize as results change. CSE remains useful for preserving older workbooks and maintaining formulas whose behavior depends on the traditional array mechanism.
- Use Enter for dynamic array formulas in Microsoft 365 or Excel 2021 and later, including formulas that spill multiple results.
- Use Ctrl+Shift+Enter when an older Excel version requires a legacy array formula, or when you are following instructions for an existing CSE formula.
- Check your audience’s Excel versions before sharing a workbook. A formula that relies on newer dynamic-array behavior may not calculate as intended in an older release.
- Use a regular formula when it is clearer. Array calculations are powerful, but a conventional formula copied down a column can be easier for someone else to inspect and maintain.
On Excel for Mac, the legacy array entry shortcut is Control+Shift+Return. The Windows shortcut is Ctrl+Shift+Enter. If a keyboard or system setting intercepts a shortcut, use Excel’s formula entry behavior and consult the shortcut reference for the installed version. Excel for the web supports many modern formulas, but use desktop Excel when you need to create or edit a legacy CSE formula.
How spill ranges work
A dynamic array formula has one anchor cell: the cell containing the formula. Results fill into a spill range extending from that cell. The range can grow or shrink when the source data changes, so cells underneath or beside it need to remain available. You generally cannot edit an individual result cell in the spill range; edit the formula in the anchor cell instead.
For instance, with names in A2:A5, enter this formula in C2 and press Enter:
=A2:A5
If the four source cells contain four names, Excel returns those four names in C2:C5. If the source range changes, the displayed results can change too. This simple example also shows that an array formula does not always need a specialized function: a range reference can return an array in a version of Excel that supports dynamic arrays.
Functions such as FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP, and XMATCH are available in Microsoft 365 and Excel 2021 or later. They can return or work with arrays, but they are not available in every older Excel release. Newer functions including VSTACK, HSTACK, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, and LAMBDA require Microsoft 365 or Excel 2024. Confirm function availability when a workbook will be opened on other computers.
Common errors and how to fix them
A #SPILL! error means Excel cannot place all the results in the intended spill range. A cell containing data, a merged cell, or another obstruction may be in the way. Select the formula cell and inspect the highlighted spill area; clear or move the blocking content, or place the formula somewhere with enough space. A dynamic formula can also return an unexpectedly large result if its source range is broader than intended.
If you see #VALUE! or an unexpected total, inspect the source ranges and the operation. Array calculations are position-based: in the multiplication example, the first value in one range is paired with the first value in the other. The ranges should have compatible dimensions, and text or error values in source cells may affect the result. Check individual pairs in spare cells if you need to locate a problematic row.
If a legacy formula shows an error after pressing Enter, verify whether that Excel version or workbook requires CSE. Select the intended output range again, enter the formula, and use Ctrl+Shift+Enter on Windows or Control+Shift+Return on Mac. Do not type curly braces manually; typed braces do not convert an ordinary formula into a recognized CSE formula.
Another common misunderstanding is trying to change one value inside a spill range. Since the spill results are controlled by the anchor formula, edit that formula or change its source data. Also check whether a formula has an @ operator. Excel may use this implicit-intersection operator in formulas from older workbooks to return a single value where a newer version might otherwise process a range as an array. Its presence is not automatically an error; it can affect whether a formula returns one result or an array.
Legacy formulas versus newer alternatives
Many calculations that once needed CSE can now be handled by dynamic arrays, but the two approaches are not identical. A dynamic formula can spill a variable-sized result, while a traditional multi-cell CSE formula is tied to the output range selected when it is entered. In a CSE range, Excel treats the selected formula cells as a unit, making it harder to insert or remove only part of the result.
For a simple total of paired products, SUMPRODUCT can be an alternative that does not require CSE. With 2, 3, and 4 in B2:B4 and 10, 20, and 30 in C2:C4, enter this in D2 and press Enter:
=SUMPRODUCT(B2:B4,C2:C4)
The result is 200: SUMPRODUCT multiplies corresponding values and adds the products. It is a helpful option in older workbooks, but it is not a universal replacement for every array formula. When selecting an approach, consider readability, version support, the shape of the result, and whether the formula needs to return a list rather than one answer.
Editing and maintaining array formulas
To change a dynamic array formula, select its anchor cell, edit the formula in the formula bar or directly in the cell, then press Enter. Do not overwrite a cell that belongs to the spill range unless you intend to clear or replace the formula’s output. If the spill size changes, check that the newly required cells are empty.
To edit a legacy multi-cell CSE formula, select the entire array range, make the change, and confirm it with Ctrl+Shift+Enter on Windows or Control+Shift+Return on Mac. Excel may prevent editing only one cell in that range because all cells belong to the same legacy formula. If you need to replace or remove it, select the full array range first.
When documenting a workbook, identify its expected Excel version and explain which cell contains the formula. This is especially helpful when a workbook mixes legacy CSE formulas and modern spill formulas. Test the workbook after changing source data, inserting rows, or moving formulas, since those edits can affect range references and spill space.
Frequently Asked Questions
Do I still need Ctrl+Shift+Enter in Excel 365?
Usually not for new formulas in Microsoft 365. Press Enter for formulas that use modern dynamic array behavior. Use CSE when maintaining a legacy array formula or when a particular older-workbook workflow requires it.
Do I type the curly braces around an array formula?
No. For a legacy CSE formula, Excel adds the braces in its display after you confirm the formula with the array-entry shortcut. Typing braces yourself does not create a legacy array formula.
Why does my array formula show #SPILL!?
Excel cannot place the full dynamic result in its output area. Check for occupied or merged cells in the spill range, clear the obstruction, and ensure there is enough room for the results.
Can I edit one cell in a spilled result?
No. Edit the formula in the anchor cell or change its source data. The other displayed values are results of that formula, not independent formulas that can be edited separately.
Can I create a legacy array formula in Excel for the web?
Use the Excel desktop app for creating or editing legacy CSE formulas. Excel for the web supports many modern dynamic array formulas, but its support for legacy array-formula workflows is limited compared with desktop Excel.
Related
- Redo in Excel: How to Restore an Undone Action
- Easiest Way to Superscript in Excel: Quick Steps and Examples
- Combine Excel Spreadsheets: Easy Methods for Your Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Attendance Sheet With Time: Setup and FormulasTo make an Excel attendance sheet with time, create columns for the date, employee, scheduled start, clock-in, clock-out,…
- Excel Truth Table Formulas: Build & Use ThemThe core way to build a truth table in Excel is to use logical functions like AND, OR,…
- Dependent Dropdown in Excel Using INDIRECT: Setup GuideYes. Create a named range for each parent choice, then use INDIRECT in the child dropdown’s Data Validation…
- Oracle SPLIT Function: Split Strings in SQLOracle Database does not provide a general built-in SQL function named SPLIT for turning a delimited string into…
- Excel Joining Two Text Columns: Formulas and ExamplesTo join two text columns in Excel, enter a formula such as =A2&" "&B2 in a third column,…
- Excel Formula for Creating a CalendarThe simplest Excel formula for creating a calendar is =SEQUENCE(6,7) combined with date arithmetic, but the most practical…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.