How to Enter an Array Formula in Excel

The keystroke depends on your Excel version. In Microsoft 365 / Excel 2021, you press Enter — formulas spill into adjacent cells automatically. In Excel 2019 and earlier, you must press Ctrl+Shift+Enter to confirm a formula as an array (often called a CSE formula). Excel wraps the formula in curly braces { } to mark it. You never type the braces yourself.

The Two Eras of Array Formulas

• Dynamic arrays (Microsoft 365 / 2021): any formula that returns multiple values automatically spills into surrounding cells. Just press Enter. Functions like FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP are designed for this.
• Legacy CSE arrays (Excel 2019 and earlier, or M365 in compat mode): select the output range first, type the formula, then press Ctrl+Shift+Enter. The formula appears with { } in the formula bar.

Step-by-Step: Modern Excel (Microsoft 365 / 2021)

1. Click a single cell — say, D2.
2. Type the formula: =A2:A10*B2:B10
3. Press Enter.
4. The result spills down into D2:D10. The first cell shows a regular value; the spilled cells show ghost values with a thin blue border when D2 is selected.

If a non-empty cell is in the spill range, you'll see a #SPILL! error. Clear the blocking cells.

Step-by-Step: Legacy CSE (Excel 2019 and Earlier)

1. Select the entire output range first — e.g., D2:D10. The selection size must match what the formula returns.
2. Type the formula: =A2:A10*B2:B10 (don't press Enter yet).
3. Press Ctrl+Shift+Enter. On a Mac it's Cmd+Return or Ctrl+Shift+Return.
4. Excel fills D2:D10 and shows the formula as {=A2:A10*B2:B10} in the formula bar for every cell.

Five Worked Examples

1. Sum the Product of Two Ranges (No Helper Column)

=SUM(A2:A10 * B2:B10)
// Modern: just Enter.
// Legacy: Ctrl+Shift+Enter on a single cell.
// Equivalent to SUMPRODUCT(A2:A10, B2:B10) — works without CSE in any version.

2. Count Cells Matching Two Conditions

=SUM((A2:A100="West") * (B2:B100>1000))
// Counts rows where region is "West" AND amount > 1000.
// Legacy needs Ctrl+Shift+Enter. Modern: Enter. COUNTIFS is usually clearer.

3. Conditional Average Without AVERAGEIF

=AVERAGE(IF(A2:A100="West", B2:B100))
// Averages B where A = "West".
// Legacy: Ctrl+Shift+Enter is required (the IF is what makes it an array).

4. Return Multiple Values With FILTER (Modern Only)

=FILTER(A2:C100, A2:A100="West")
// Returns every "West" row across columns A:C.
// Spills automatically — no CSE.

5. Build a Sequence of Numbers

=SEQUENCE(10, 1, 1, 1)
// Returns 1, 2, 3, ... 10 down a column.
// Modern only. Legacy equivalent needs ROW(INDIRECT(...)) tricks.

How to Edit an Existing Array Formula

• Modern dynamic arrays: click the anchor cell (the top-left of the spill), edit the formula, press Enter.
• Legacy CSE: select the entire array range, press F2 to edit, then Ctrl+Shift+Enter to confirm. You cannot edit just one cell of a CSE array — Excel returns "You cannot change part of an array."

How to Delete an Array Formula

• Modern: delete the anchor cell. The whole spill disappears.
• Legacy: select the entire array range, then press Delete.

Troubleshooting

• "You cannot change part of an array" — you tried to edit one cell of a legacy CSE array. Select the full range first.
• #SPILL! — a cell in the spill range isn't empty. Clear the blockers, or shrink the formula's input range.
• #CALC! — usually a nested empty array or a formula returning zero rows from FILTER. Add an if_empty argument: =FILTER(rng, cond, "no match").
• Formula returns one value when you expected many — in legacy mode, you forgot to select the full output range before pressing Ctrl+Shift+Enter.
• Curly braces appear when you type them — Excel ignores typed braces. Only Ctrl+Shift+Enter creates a real CSE formula.

Should You Still Use Ctrl+Shift+Enter?

If your file might be opened in Excel 2019 or earlier, yes — CSE formulas are the portable option. If everyone is on Microsoft 365, prefer dynamic arrays. They're easier to read, easier to edit, and let you use the newer functions (FILTER, UNIQUE, SORTBY) that don't have CSE equivalents.