An array formula performs multiple calculations on one or more items in an array, a set of values, instead of working one cell at a time. In current Excel, a formula that returns several values spills them into the neighboring cells automatically. In older versions, the same formula has to be confirmed with Ctrl+Shift+Enter, which wraps it in curly braces.
Those are the two eras of the same idea, and both still turn up in real workbooks: braces in a model built in 2015, spilled ranges and # references in a file built last quarter. This guide covers what array formulas do, how the old Ctrl+Shift+Enter entry works, how spilling changed the rules, and when the keystroke is still required.
What is an array formula?
An ordinary formula takes single values and returns a single value. An array formula works on whole sets of values at once. The classic example multiplies two columns pairwise and sums the results, with no helper column in between:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Qty | Price | Total |
| 2 | Pens | 12 | 0.50 | 63.80 |
| 3 | Pads | 10 | 1.20 | |
| 4 | Binders | 4 | 7.25 | |
| 5 | Tape | 6 | 2.80 |
The multiplication B2:B5*C2:C5 doesn’t produce one number. It produces an intermediate array of four, one per row, and SUM collapses them into the total. That is what makes it an array formula: the calculation flows through an array of values on its way to the result. Some array formulas end in a single number, like this one. Others keep the whole array as the result, which is where the two eras of Excel part ways.
What does Ctrl+Shift+Enter do?
In Excel versions without dynamic arrays, typing an array formula isn’t enough. In those versions, Microsoft’s instructions are to select the output range first, type the formula in its top-left cell, and press Ctrl+Shift+Enter to confirm it. The keystroke tells Excel to treat the whole thing as one array calculation, and Excel marks the result by surrounding the formula with curly braces:
You cannot type the braces yourself. Microsoft is blunt about the consequence: “if you type the braces manually, your formula will be converted to a text string, and it won’t work.” The braces are a display convention, nothing more, and the entry method gave these formulas their nickname. They are commonly referred to as CSE formulas, after Ctrl+Shift+Enter.
For a single-cell result like the total above, selecting one cell first is enough. For a formula that returns a block of results, legacy Excel makes you select the entire output range in advance, which means you have to know how big the result will be before you enter it. That requirement is exactly what dynamic arrays removed.
What are dynamic arrays and spill?
Beginning with the September 2018 update for Microsoft 365, any formula that can return multiple results automatically spills them down or across into the neighboring cells. Microsoft calls the behavior spill: the formula produced multiple values, and Excel placed them in the neighboring cells. You enter the formula in one cell, press plain Enter, and Excel sizes the output range for you.
Dynamic arrays came with functions built for this behavior. Excel 2021’s release notes list dynamic arrays among its new capabilities alongside six new functions: FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, and RANDARRAY. Each is built to return an array. SORT and FILTER are the formula counterparts of the Data tab commands: sorting a range and filtering it do the same jobs as one-time steps, while these functions recompute as the source data changes. Two of them together turn a repetitive column into a clean list:
| A | B | C | |
|---|---|---|---|
| 1 | Region | Distinct regions | |
| 2 | North | East | |
| 3 | South | North | |
| 4 | North | South | |
| 5 | East | ||
| 6 | South | ||
| 7 | East |
Only C2 contains a formula. C3 and C4 hold spilled results: they show values, but you can’t edit them individually, because they belong to the array anchored at C2. Change the data in column A and the spill recalculates and resizes on its own. Spilling is also how XLOOKUP returns several columns from a single formula.
Versions matter here. Dynamic arrays exist in Excel for Microsoft 365 and in the perpetual releases from Excel 2021 onward. Excel 2019 and 2016 do not have them, which is why guidance for formulas like the multi-criteria lookup in the INDEX and MATCH guide still carries a Ctrl+Shift+Enter caveat.
How do I reference a spilled range?
A spilled range has a size that can change, so pointing a normal reference at it goes stale. The spilled range operator solves this: put # after the anchor cell to reference the entire spill range, however large it currently is.
With the regions example above, C2# means the three cells C2:C4, so the count is 3. Change one of the duplicate regions in column A to a fourth name and the spill grows to four cells; C2# follows automatically, no edits needed. One documented limit: the operator does not work on references into closed workbooks, where it returns #REF! until the workbook is opened.
What blocks a spill?
A spilled formula needs empty cells to land in. If anything occupies the intended spill range, the formula doesn’t calculate partially or overwrite the obstruction. It returns #SPILL! and waits. Clearing the blockage is usually quick: select the error alert, choose Select Obstructing Cells, and move or delete what it finds. Excel tables and merged cells are the two structural blockers to know about, since spilled formulas don’t work inside a table and can’t spill into merged cells. The full walkthrough, alongside the other error values, is in the errors guide’s #SPILL! section.
Why is there an @ in my formulas?
Open an old workbook in current Excel and you may find @ signs inside formulas that nobody typed. They come from the same upgrade that introduced spilling. Old Excel had a silent rule called implicit intersection: when a formula received many values where one was expected, Excel reduced them to a single value, invisibly. Current Excel marks the places where implicit intersection could occur with the @ operator instead of doing it silently. The value doesn’t change; the previously invisible behavior now has a visible marker.
Whether to remove an @ depends on what the expression returns. Microsoft’s rule of thumb: if the expression returns a single value, which is the most common case, removing the @ changes nothing; if it returns a range or an array, removing the @ makes it spill. In a formula that was built to return one value per row, the @ is doing real work. Leave it alone unless you specifically want the spill.
Do you still need Ctrl+Shift+Enter?
For new work in current Excel, no. Microsoft’s guidance is unambiguous: legacy CSE array formulas “are still supported for back compatibility reasons, but should no longer be used.” A dynamic array formula does everything the CSE version did, resizes itself, and shows a spill border around its output; a legacy CSE formula can do neither.
The keystroke still matters in three situations:
- Someone opens your file in Excel 2019 or 2016. Those versions have no dynamic arrays. Your spilled formulas are shown there as legacy CSE formulas, and any array-style formula entered in those versions needs Ctrl+Shift+Enter, including patterns as common as the multi-criteria INDEX/MATCH or the TRIM-inside-VLOOKUP fix for lookup values with stray spaces.
- You maintain an older workbook. Braces in the formula bar mean legacy array formulas are present. They keep calculating correctly in current Excel, so there is no urgency to convert them, but any edit made in an old version has to be confirmed the old way.
- You audit someone else’s model. Knowing that
{=...}braces mark an array formula, and that they can’t be typed, tells you how the formula was entered and that it predates, or ignores, dynamic arrays.
The practical rule: build with dynamic arrays, and reach for Ctrl+Shift+Enter only when a workbook or a colleague’s Excel version forces the legacy path.
The short version
An array formula calculates across a set of values instead of one cell at a time. Modern Excel enters them like any other formula and spills multi-value results into the neighboring cells, resizing as the data changes; # after the anchor cell references the whole spill, and #SPILL! means the landing zone is blocked. Older Excel needs the output range selected first and Ctrl+Shift+Enter to confirm, with curly braces as the telltale marker. The two systems coexist in most organizations, so a shared model can hold both. TRANSPOSE is one such dynamic-array function: it spills a rotated copy of a range and updates as the source changes. And when someone reworks the array formulas in a workbook other people rely on, put the old and new versions side by side and compare them before trusting the new numbers.
