How to Enter an Array Formula in Excel

To enter an array formula in Excel 2019 or earlier, select the cell or output range, type the formula and press Ctrl+Shift+Enter instead of Enter; Excel confirms it as an array (CSE) formula and wraps it in curly braces { }, which you never type yourself. In Microsoft 365 and Excel 2021 or later, just press Enter: formulas that return several values spill into the adjacent cells automatically.

What's the difference between dynamic arrays and CSE array formulas?

Dynamic array formulas (Microsoft 365, Excel 2021 and later) spill their results into neighboring cells when you press Enter, while legacy CSE array formulas (Excel 2019 and earlier) must be confirmed with Ctrl+Shift+Enter and only fill the cells you selected before entering them. Which one you get depends on your Excel version.

• 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.

How do you enter an array formula in Microsoft 365 or Excel 2021?

Type the formula in a single cell and press Enter; you don't need Ctrl+Shift+Enter. Excel spills the results into the cells below or to the right.

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.

How do you enter a Ctrl+Shift+Enter array formula in Excel 2019 and earlier?

Select the output range, type the formula, and press Ctrl+Shift+Enter instead of Enter. Excel then adds the curly braces that mark it as an array formula.

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

Each example notes the keystroke it needs in modern and legacy Excel.

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

Edit the anchor cell of a dynamic spill, or select the whole range of a legacy CSE array before editing.

• 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

Delete the anchor cell of a dynamic spill, or select the entire range of a legacy CSE array and press Delete.

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

Tips for Dynamic Array Formulas in Excel

Enter the formula once in the top-left cell, keep the spill area empty, and point other formulas at the whole result with the # operator instead of a fixed range. These habits prevent most dynamic array problems in Microsoft 365 and Excel 2021:

• Reference the whole spill with #. =SUM(D2#) sums everything spilled from D2 and adjusts when the spill grows or shrinks. A fixed range like D2:D10 misses new rows.
• Keep spilling formulas out of Excel Tables. Spilled arrays aren't supported inside a Table, so the formula returns #SPILL!. Put the formula next to the Table and reference the Table instead. For a Table named Sales, =FILTER(Sales, Sales[Region]="West") picks up new rows automatically.
• Avoid full-column references. =A:A*2 entered anywhere below row 1 returns #SPILL! because the result would run past the bottom of the sheet. Use a bounded range such as A2:A5000 or a Table column.
• Use @ when you want one value. =@A2:A10 returns only the value on the same row as the formula (implicit intersection). If Excel adds @ to a formula from an older workbook, it's preserving the single-value result that version returned.
• Name intermediate arrays with LET. It keeps nested formulas readable, and each named step is calculated once.

=LET(west, FILTER(A2:C100, A2:A100="West"),
     SORT(west, 3, -1))
// Filters the West rows, then sorts them by column 3, largest first.

For a tour of the functions built for spilling (FILTER, UNIQUE, SORT, SORTBY, SEQUENCE), see Excel dynamic arrays.

Troubleshooting

These are the errors and messages that come up most often when entering array formulas.

• "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.
• Typed curly braces don't create an array formula — if you type {=A2:A10*B2:B10} yourself, Excel stores it as text. 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.