Array Formulas in Excel: What They Are and When to Use One

An ordinary formula takes single values and returns one answer. An array formula takes whole ranges, performs the calculation on every item, and either returns one summarised answer or spills a set of results across the sheet.

The distinction sounds academic until you meet a problem that ordinary formulas cannot solve in one cell. It is also the kind of advanced formula work the MOS Excel Expert exam tests.

The clearest example

You have quantities in B2:B20 and unit prices in C2:C20, and you want the total value. The obvious approach is a helper column of =B2*C2, then SUM that column.

An array formula does it in one cell, with no helper column:

=SUM(B2:B20*C2:C20)

Excel multiplies each pair, holds the 19 results in memory, and sums them. The intermediate column never exists.

In Excel 2019 and earlier you must confirm this with Ctrl+Shift+Enter rather than Enter. Excel then displays it wrapped in braces — {=SUM(B2:B20*C2:C20)} — which you never type yourself. In Microsoft 365 and Excel 2021 plain Enter is enough.

What Ctrl+Shift+Enter was actually for

Before dynamic arrays, Excel assumed a formula returned one value. Ctrl+Shift+Enter was how you told it otherwise, so it would evaluate the whole range instead of quietly picking the value on the same row.

That assumption is what made the old behaviour so confusing. Enter a multi-cell formula without CSE and you often got no error at all — just a plausible, wrong number taken from whichever row the formula happened to sit on.

Formulas entered this way are usually called CSE formulas or legacy array formulas. They still work in current Excel; you just no longer need to create them.

The two kinds of result

Summarised to one cell. The example above returns a single total. So do most array formulas wrapped in SUM, MAX, COUNT or similar.

Spilled across many cells. In Microsoft 365, a formula that produces multiple results fills the cells beneath and beside it automatically. The range it fills is the spill range, marked with a thin blue border, and only the top-left cell contains the formula.

=B2:B20*C2:C20

Entered alone in 365, this spills 19 individual line totals down the column. In Excel 2019, the same formula returns only the first value unless you pre-select 19 cells and use Ctrl+Shift+Enter. If you need to test both behaviours on one machine, see running two versions of Excel side by side.

Conditional counting without COUNTIFS

The pattern most worth knowing is boolean multiplication, the same idea behind SUMPRODUCT summing on several conditions. A comparison like (A2:A20="North") produces an array of TRUE and FALSE. Multiplying two of them coerces to 1 and 0, and only 1 × 1 survives:

=SUM((A2:A20="North")*(B2:B20>100))

That counts rows meeting both conditions, and the same multiplication trick is what lets you look up a value on two criteria. COUNTIFS does the same thing more readably, so use COUNTIFS when it fits — but the array version handles cases COUNTIFS cannot, such as comparing two columns to each other:

=SUM((C2:C20>B2:B20)*1)

There is no COUNTIFS syntax for “count rows where column C exceeds column B on the same row”. The array formula does it directly.

The #SPILL! error

New in the dynamic-array era, and almost always the same cause: something is in the way. A spilled formula needs its whole output range empty. A single stray value — even a space — inside that range blocks it.

Click the cell, open the warning triangle and choose Select Obstructing Cells. Excel takes you straight to the culprit.

The other cause is entering a spilling formula inside an Excel Table. Tables have their own row-by-row calculation model and do not accept spill ranges. Move the formula outside the table.

When not to use one

Array formulas over large ranges are slow, because every cell in the range is evaluated on every recalculation. A workbook with a few hundred array formulas across full-column references will crawl.

Two habits keep this in check. Reference only the rows you use — B2:B20, not B:B. And when a helper column would make the sheet clearer to a colleague, use the helper column; a formula nobody dares edit is its own kind of problem.