How To Enter An Array Formula In Excel | Ctrl+Shift+Enter

To enter a legacy array formula, press Ctrl+Shift+Enter. In modern Excel, a regular Enter creates dynamic arrays that spill automatically.

Array formulas handle multiple calculations inside a single formula, processing whole ranges instead of individual cells. But the keystroke you use to enter them depends entirely on which Excel version you are running — older versions lock in the array behavior with Ctrl+Shift+Enter, while Excel 365 and Excel 2021 simply spill the results across cells with a normal Enter. Using the wrong method either breaks the formula or limits what it can do.

What Exactly Is An Array Formula In Excel?

An array formula performs several calculations on one or more items in an array of values. Instead of returning a single result, it can return multiple results — either as a single cell output or across a range of cells. Microsoft distinguishes two generations: legacy array formulas (the original style that has existed for decades) and dynamic array formulas (the modern approach introduced with Excel 365).

The core difference is how you enter them and how Excel displays the results. Legacy formulas require you to confirm them with a special key combination, while dynamic formulas handle everything automatically.

Entering An Array Formula In Excel: Legacy Vs Dynamic Arrays

Which method you use comes down to your Excel version. If you own Excel 365, Excel 2021, or any version that supports dynamic arrays, you can type a formula and press Enter — the results spill into adjacent cells on their own. If you work in an older Excel version, or you are intentionally writing a legacy array formula for compatibility, you must press Ctrl+Shift+Enter to tell Excel to treat it as an array.

The table below shows the key differences at a glance.

How To Enter A Legacy Array Formula (Ctrl+Shift+Enter Step By Step)

For legacy array formulas, the entry method differs slightly depending on whether you want a single result or multiple results. Microsoft’s official guidelines and examples of array formulas document both procedures.

Single-Cell Array Formula

1. Click the cell where you want the result.
2. Type the formula using ranges instead of individual values. Example: =SUM(IF(A1:A10>100, A1:A10)).
3. Press Ctrl+Shift+Enter.
   When it works: the formula bar shows the formula wrapped in curly braces { } — you never type those braces yourself. Excel adds them.

Multi-Cell Array Formula

1. Select the entire range where results should appear (the cells must be the same size as the output array).
2. Type the formula that returns multiple values.
3. Press Ctrl+Shift+Enter.
   When it works: every selected cell fills with the array result. If you see only the first value or an error, you either forgot the keystroke or the selected range is the wrong size.

How Dynamic Arrays Work (Just Press Enter)

In Excel 365 and Excel 2021 or later, dynamic arrays change the workflow completely. You type the formula in one cell and press the Enter key. Excel automatically determines how big the result range needs to be and spills the values into the neighboring cells.

The spill range is outlined with a blue border, and the original cell shows the formula with a #SPILL! reference if anything blocks the output cells. No pre-selecting a range, no special keystroke — just Enter.

If you edit a dynamic array formula, you simply edit the single cell and press Enter again. Excel recalculates and re-spills the results automatically.

Working With Array Constants

An array constant is a hard-coded set of values you type directly into a formula. Microsoft documents the syntax using braces and separators.

• Commas separate items in a row: ={1,2,3} creates a horizontal array.
• Semicolons separate rows: ={1,2,3;4,5,6} creates a 2×3 array.
• Unlike legacy array formulas, you do type the braces yourself for array constants — Excel does not add them.
• For dynamic arrays, enter the constant normally and press Enter. For legacy arrays, select the output range first and press Ctrl+Shift+Enter.

A common use is converting a spilled result into a static array constant: press F2 to edit the cell, then press F9 to replace the spill reference with the literal values, then press Enter.

Editing And Managing Array Formulas

Editing a legacy array formula requires care. If you change even one cell in a multi-cell array formula without re-entering the whole thing with Ctrl+Shift+Enter, the formula breaks and may return an incorrect result or a #VALUE! error. You must select the entire array range, make your edit, then press Ctrl+Shift+Enter again.

Dynamic arrays are much simpler. Edit the single cell that contains the formula, make your change, and press Enter. The spill range updates automatically.

To convert a dynamic spill range into static values, select the spill area, copy it, and paste as values. Or use the F2, F9, Enter sequence mentioned above for single-cell references.

Common Array Formula Mistakes To Avoid

Which Entry Method Should You Use?

Your Excel version decides the workflow. Run through this quick check whenever you start a new array formula.

• Using Excel 365, Excel 2021, or newer? Type the formula and press Enter. Dynamic arrays handle the rest. Reserve Ctrl+Shift+Enter only for legacy workbooks you must keep compatible with older Excel versions.
• Using Excel 2019 or older? Always press Ctrl+Shift+Enter after typing the formula. Without it, the formula processes only the first value in the range.
• Sharing a workbook with people on different Excel versions? Use legacy array formulas with Ctrl+Shift+Enter to ensure everyone gets the correct result.
• Unsure which method your version supports? Test with a simple formula like =SUM(A1:A10*B1:B10). If pressing Enter spills the result across cells, you have dynamic arrays. If it returns a single value that you expected to be an array, switch to Ctrl+Shift+Enter.