Excel Array Formulas and Dynamic Arrays Explained

Excel array formulas can calculate many values at once. In current versions of Excel, one formula can return an entire set of results and let those results spill into nearby cells. The same idea can also produce a single answer, such as a grand total, without a helper column.

The part that often causes confusion is version history. Older Excel releases required Ctrl + Shift + Enter for many array calculations. Microsoft 365, Excel 2021, and Excel 2024 support dynamic arrays, so new formulas normally need only Enter. This guide explains the modern method first, then shows how legacy array formulas differ.

What is an array formula in Excel?

An array is a collection of values. Instead of working with just one cell, an array formula can process a row, a column, or a rectangular range in one calculation. Its result can be either:

• One value: a function such as SUM reduces the calculated array to a single total.
• Multiple values: a dynamic array formula returns a set of results that spills into neighboring cells.

For example, if quantities are in C2:C7 and unit prices are in D2:D7, the expression C2:C7*D2:D7 creates six line totals. Enter that expression by itself and current Excel returns all six results. Put it inside SUM and Excel returns one combined total.

Microsoft continues to support legacy CSE formulas for compatibility, but its dynamic-versus-legacy guidance recommends dynamic arrays for new workbooks. If a file must work in Excel 2019 or earlier, test it in the oldest required version because newer functions such as FILTER, UNIQUE, SORTBY, and SEQUENCE may not be available.

Build a sample data set

Enter this small sales list in A1:D7. The examples throughout the guide use the same cells, making it easier to see what each array formula changes.

Keep enough empty worksheet space for each result. The first example spills vertically through F2:F7; later examples use G2 and H2 as criteria cells and should be entered in a separate clear area. If your Excel installation uses semicolons as argument separators, replace the commas in the examples with semicolons.

Return multiple results with one formula

To calculate every line total without filling a formula down the worksheet, select F2, enter the following formula, and press Enter:

=C2:C7*D2:D7

Entering =C2:C7*D2:D7 in F2 returns multiple results and spills the calculated line totals down the worksheet.

Excel multiplies each quantity by the price on the same row. The formula in F2 spills six results down through F7: 15.00, 17.00, 18.75, 62.00, 12.60, and 37.50.

The cell containing the formula is the anchor cell. Select a different cell in the spilled range and Excel shows a ghosted copy of the formula, but that cell cannot be edited independently. To change the calculation, edit F2. To remove it, delete the formula from F2.

Return one result from an array calculation

Sometimes Excel should perform the same element-by-element multiplication but return only the final total. Wrap the array expression in SUM:

=SUM(C2:C7*D2:D7)

Using =SUM(C2:C7*D2:D7) lets Excel multiply each row in memory and return a single grand total in one cell.

In current Excel, press Enter. The formula calculates the six line totals in memory and adds them, returning 162.85 in one cell. This is useful when the intermediate values are not needed on the worksheet.

A broadly compatible alternative is SUMPRODUCT:

=SUMPRODUCT(C2:C7,D2:D7)

This formula also returns 162.85 and does not require legacy array entry. Whichever approach you choose, the paired ranges must have compatible dimensions.

Filter rows with a dynamic array formula

The FILTER function returns only rows that meet a condition. Type a category such as Tech in G2, then enter this formula in an empty area:

=FILTER(A2:D7,B2:B7=G2,"No matches")

The FILTER function returns only the records that match the selected category in G2, spilling the matching rows into a new range.

The expression B2:B7=G2 tests every category and creates a temporary array of TRUE and FALSE values. FILTER keeps the rows whose corresponding result is true. With Tech in G2, the spill contains the Cable and Mouse rows. The final argument displays No matches instead of an empty-array error when no row qualifies.

Apply more than one condition

Enter a minimum quantity in H2. This formula returns records in the selected category whose quantity is at least that minimum:

=FILTER(A2:D7,(B2:B7=G2)*(C2:C7>=H2),"No matches")

This FILTER formula combines two conditions to return only rows where the category matches G2 and the quantity is at least the value in H2.

Multiplication acts like an AND operation because a row survives only when both tests evaluate to true. Addition can act like OR in a Boolean array test, although a row satisfying both conditions can produce 2; functions such as FILTER still treat that nonzero result as included.

Extract, sort, and generate arrays

Dynamic array functions become especially useful when they are nested. To create an alphabetized list of distinct categories, enter:

=SORT(UNIQUE(B2:B7))

Combining UNIQUE with SORT returns each category once and arranges the results alphabetically in a spilled range.

UNIQUE first returns each category once. SORT then arranges the result as Home, Stationery, and Tech. If a new category is added within the referenced source range, the output recalculates.

To sort the complete records by calculated line total from largest to smallest, use:

=SORTBY(A2:D7,C2:C7*D2:D7,-1)

SORTBY orders the full record set by calculated line totals, placing the highest-value rows at the top of the spilled result.

The final argument -1 requests descending order. For the sample data, the first two products are Mouse and Lamp because their line totals are 62.00 and 37.50.

SEQUENCE creates a numbered array without source cells. This formula spills the integers 1 through 6 vertically:

=SEQUENCE(6)

You can also specify columns, a starting value, and a step. For example, =SEQUENCE(4,1,10,5) returns 10, 15, 20, and 25. These functions are documented in Microsoft’s references for FILTER, UNIQUE, SORTBY, and SEQUENCE.

Refer to an entire spill range with the # operator

A spilled range can grow or shrink, so a fixed reference such as F2:F7 can become outdated. Add the spill operator # to the anchor cell to refer to its complete current output:

=SUM(F2#)

The formula =SUM(F2#) sums the entire dynamic array spilling from F2, so the reference adjusts automatically when the spill size changes.

If F2 contains the line-total formula, F2# means “the whole array spilling from F2.” The total reference adjusts automatically if the spill changes size. You can also use a spill reference as the source for charts, data validation, or another formula where the feature accepts a range.

Dynamic arrays have limited support between workbooks. Microsoft notes that a linked dynamic-array formula can return #REF! when its source workbook is closed, so cross-workbook models need extra testing.

Use Excel Tables as sources, not spill destinations

Converting source data to an Excel Table is helpful because structured references expand as rows are added. Spilled array formulas, however, are not supported inside Tables. Put the dynamic array formula in the regular worksheet grid outside the table.

If the sample data is in a table named Sales, a calculated array of line totals can use structured references:

=Sales[Quantity]*Sales[Unit price]

Place this formula outside the table and leave enough empty cells for the result. As the source table grows, the structured references expand and the spill can resize with them.

Understand legacy Ctrl+Shift+Enter array formulas

Before dynamic arrays, Excel used legacy array formulas, also called CSE formulas. To evaluate the total in an older version, you may need to type the formula and confirm it with Ctrl + Shift + Enter:

=SUM(C2:C7*D2:D7)

Excel then displays braces around the formula in the formula bar, similar to {=SUM(C2:C7*D2:D7)}. Do not type those braces yourself; Excel adds them after the correct key combination.

A multi-cell legacy array is more restrictive than a dynamic spill. You select the complete output range before entering the formula, and that range does not automatically resize. You also cannot edit or delete just one cell in the array. Select the whole legacy array range before changing it.

Recognize the @ implicit intersection operator

Current Excel may add an @ to a formula created in an older version. The symbol makes implicit intersection visible: it reduces a range or array to one value, usually the value associated with the formula’s row.

For example, a formula such as =@A2:A7 requests a single value rather than allowing the range to spill. Removing @ can therefore change the result from one value to several. Do not delete it merely because it looks unfamiliar; first determine whether the workbook expects one result or a spilled array. Microsoft’s implicit intersection guide explains why the operator can appear in upgraded workbooks.

Use array constants for fixed values

An array constant stores literal values directly in a formula. In many English-language Excel installations, commas separate columns and semicolons separate rows:

={10,20,30}

This horizontal constant spills across three columns. The vertical form ={10;20;30} spills down three rows. Regional settings can use different separators, and constants cannot contain cell references or formulas. They are best for small values that truly will not change; worksheet cells or a lookup table are easier to maintain when the values need editing.

Fix common array formula errors

For a blocked dynamic array, Excel outlines the intended spill area with a dashed border. That visual cue is usually the fastest way to find an occupied cell that looks empty because it contains a space or a formula returning "".

Design dependable array formulas

1. Decide whether the answer should be one value or many. Use an aggregating function such as SUM for one answer; leave an array-producing expression unaggregated when you want a spill.
2. Match array dimensions. Element-by-element operations work most predictably when the referenced ranges have the same height and width.
3. Keep spill space clear. Put dynamic formulas in a dedicated output area outside Excel Tables and merged cells.
4. Use bounded references. A range such as C2:C1000 is generally easier to audit than an unnecessary whole-column array calculation.
5. Separate inputs from outputs. Store criteria in labeled cells instead of burying frequently changed text or thresholds inside long formulas.
6. Plan for recipients. If colleagues use older Excel versions, avoid unsupported functions or provide a tested alternative.

When a result looks wrong, evaluate the expression from the inside out. Select part of a formula in the formula bar and press F9 to preview that part’s calculated values, then press Esc so you do not replace the formula with those values. For complex workbooks, Excel’s Evaluate Formula command offers a safer step-by-step view.

Frequently asked questions

Make arrays easier to reason about

The simplest mental model is to ask what the formula creates before its outer function acts. C2:C7*D2:D7 creates six values. SUM reduces them to one; FILTER, UNIQUE, SORTBY, and SEQUENCE can return collections that spill.

For new workbooks, start with dynamic arrays and press Enter. Reserve legacy CSE entry for maintaining files that require it, keep spill destinations clear, and use the # operator when another formula must follow a changing output range. Microsoft’s overview of dynamic arrays and spilled behavior provides additional platform-specific details.