Array Formula in Excel: How It Works

Formulas & functions · Updated November 21, 2026

An array formula in Excel works with multiple values at once and can return either one result or a range of results. In Microsoft 365, formulas that return several results usually spill into neighboring cells automatically; older Excel versions often require you to enter certain array formulas with Ctrl+Shift+Enter.

What an array formula means

An array is a group of values, such as a column of quantities or a rectangular range of records. An array formula uses one or more such groups in a calculation. For example, multiplying two columns can calculate a result for every corresponding row, while a formula that totals those products can return a single number.

The term covers more than one Excel feature. It can refer to a formula that performs calculations on arrays internally, a legacy formula entered with a special key combination, or a modern dynamic array formula that places multiple results in cells. The formula’s behavior depends on the Excel version and on whether the result is one value or several.

Array formulas are useful when you want to calculate across a range without writing a separate formula for every row, or when you want a formula to produce a changing list of results. They are not a special type of cell or a separate file format: they are formulas that handle multiple values.

How dynamic arrays work

In Microsoft 365 and newer perpetual versions such as Excel 2021 and Excel 2024, many formulas can return a range of results and spill them into adjacent cells. Enter the formula in the upper-left cell of the intended output area. Excel fills the other cells as needed, and the formula itself remains in that first cell.

The spilled cells are not separate formulas. Select one of them and the formula bar shows the formula in the anchor cell; edit that formula to change the whole result. Excel draws a border around the spill range when the anchor cell is selected. You can also refer to the complete spill range using the anchor cell followed by the spill operator, such as A1#.

Spill ranges can grow or shrink when source data changes. This is useful for reports and lists that update automatically, but it means cells below or beside the formula need to remain available. Dynamic array formulas are supported in Excel for Microsoft 365, Excel 2021 or later, and Excel for the web. Some newer functions have narrower version requirements, so check availability when sharing with people using older Excel releases.

Practical array formula examples

For a row-by-row calculation, suppose A2:A4 contains quantities 2, 3, and 5, while B2:B4 contains prices 4, 6, and 2. In Microsoft 365 or Excel 2021 and later, enter this in C2 with the cells below it clear:

=A2:A4*B2:B4

The formula spills 8, 18, and 10 into C2:C4. Each quantity is multiplied by the price on the same row. If you wanted only the combined total instead, with the same values in A2:A4 and B2:B4, use:

=SUMPRODUCT(A2:A4,B2:B4)

The result is 46: 2 × 4, plus 3 × 6, plus 5 × 2. SUMPRODUCT returns one result and does not need Ctrl+Shift+Enter in modern Excel. It is also available in older Excel versions, making it a practical option for some calculations that combine corresponding ranges.

To return only matching records, suppose A2:A5 contains item names Pen, Paper, Marker, and Folder, and B2:B5 contains their statuses Open, Closed, Open, and Closed. With the source data in those cells, enter:

=FILTER(A2:B5,B2:B5="Open","No open items")

The formula spills two rows containing Pen and Open, followed by Marker and Open. FILTER is available in Microsoft 365 and Excel 2021 or later. The third argument provides a message if there are no matches; leaving it out can result in a #CALC! error when the filter finds nothing.

To generate a simple numbered list, with the formula entered in A1 and at least five empty cells below it, use:

=SEQUENCE(5)

The result is the numbers 1 through 5 in A1:A5. SEQUENCE can also generate rows and columns when given additional arguments, which is handy for sample data or numbered schedules. Like FILTER, it requires Microsoft 365 or Excel 2021 or later.

Entering a legacy array formula

Before dynamic arrays, some Excel formulas that calculated across ranges had to be confirmed as an array formula. In Excel for Microsoft 365 on Windows, enter the formula and press Ctrl+Shift+Enter when working with a legacy formula that requires this method. Excel displays braces around the formula in the formula bar, but do not type the braces yourself.

For example, suppose A2:A4 contains quantities 2, 3, and 5, and B2:B4 contains prices 4, 6, and 2. In a version that requires legacy array entry, enter this formula in a single result cell and confirm it with Ctrl+Shift+Enter:

=SUM(A2:A4*B2:B4)

It returns 46. In current Excel, the same formula can generally be entered with Enter because modern Excel handles the calculation without the old key combination. If an older workbook already contains a legacy array formula, Excel may require you to select its full output range before editing or deleting it. On Excel for Mac, the legacy array-entry shortcut is Control+Shift+Return. Shortcuts and legacy formula editing can differ in Excel for the web, so use the desktop app if you need to maintain an older CSE formula.

Choosing between dynamic and legacy formulas

For new workbooks, dynamic arrays are usually the simplest choice when a formula should return a changing list or several calculated values. They need just one formula, and Excel adjusts the spill range when the result changes. Use an ordinary formula copied down when each row needs an independent calculation, especially if users may need to edit individual rows.

Legacy CSE formulas matter mainly when you maintain a workbook built for an older Excel version or need compatibility with people who cannot use newer functions. A formula using FILTER or SEQUENCE will not work in Excel 2019 or earlier; users may see a #NAME? error. Consider whether a traditional formula, a PivotTable, or another supported method is more suitable before sharing a workbook with older installations.

Array formulas are not automatically faster or clearer. A formula that processes a very large range can make a workbook slower, and a single complicated formula can be harder to audit than a few straightforward helper columns. Match the approach to the task: use a dynamic array for a generated list, a normal copied formula for row-level logic, and an aggregate function when the desired answer is one total.

Common spill errors and fixes

A #SPILL! error means Excel cannot place a dynamic formula’s results where they need to go. Select the error cell to see a warning that can identify the obstruction. Typical causes include existing content in the destination cells, merged cells in the spill area, or a formula entered in a location where spilling is restricted.

  • Clear any values or formulas blocking the output area. A cell that looks empty but contains a space can still block a spill.
  • Unmerge cells in the proposed output range if they prevent the results from filling normally.
  • Move the formula outside an Excel table. Dynamic array formulas cannot spill within table cells, although a formula outside the table can refer to table data.
  • Check that the spill area has enough rows and columns, particularly if a filter may return more matches as the source list grows.

If a result is wrong rather than blocked, check that paired ranges have compatible dimensions. For example, multiplying A2:A10 by B2:B9 does not provide a matching value for every row. Also confirm that numbers have not been stored as text and that filter conditions refer to the intended rows. A function such as FILTER may return no results if the condition does not match the source values exactly.

Compatibility and sharing a workbook

Modern Excel functions can affect whether a workbook works for everyone who opens it. FILTER, SEQUENCE, UNIQUE, SORT, XLOOKUP, and XMATCH require Microsoft 365 or Excel 2021 or later. LET requires Microsoft 365 or Excel 2021 or later. Functions introduced more recently, including TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, HSTACK, and LAMBDA, require Microsoft 365 or Excel 2024. Availability can also depend on the update channel and installed build.

When sharing, ask which Excel version the recipient uses and avoid newer functions if they need to edit the file in an older release. Excel may show a compatibility warning when saving in an older file format, but saving does not turn a modern function into an older equivalent. A recipient may be able to see calculated values without being able to recalculate the workbook correctly.

Excel for the web supports many modern dynamic array functions, though the editing experience and available features can vary from the desktop application. The core idea—one formula returning results into neighboring cells—is the same. If a workbook relies on legacy CSE formulas or specialized compatibility behavior, verify it in the desktop version used by the people who maintain it.

Frequently Asked Questions

What does an array formula do in Excel?
An array formula performs a calculation using one or more groups of values. It may return a single answer, such as a total, or multiple answers that fill a range of cells.

Do I still need Ctrl+Shift+Enter in Excel?
Usually not for new formulas in Microsoft 365 or newer Excel versions. The shortcut is relevant to legacy array formulas in older Excel and to maintaining some workbooks created with that method.

Why is my array formula showing #SPILL!?
Excel cannot place all the results in the required cells. Clear the blocked cells, check for merged cells, and move the formula outside a table if the table is preventing the spill.

Can I edit one cell in a spilled result?
No. The spilled cells are outputs of the formula in the anchor cell. Change that formula or its source data to alter the results; the output cells themselves are not independent formulas.

Can I use dynamic array formulas in an Excel table?
A dynamic array formula cannot spill within a table. Put the formula in the worksheet grid outside the table and refer to the table as its source if you want the results to expand.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.