An Excel array formula works with multiple values at once, either returning one result or spilling a set of results into neighboring cells. For example, with quantities in A2:A4 and prices in B2:B4, enter =A2:A4*B2:B4 in C2 in Microsoft 365 for Excel to return 10, 21, and 24 in C2:C4.
What an Array Formula Means
A regular formula often processes one cell at a time. An array formula can instead operate on a range of cells as a group. Depending on the formula, it may return a single answer, such as a total, or several answers, such as a list of matching records. The word “array” refers to an ordered collection of values; a worksheet range is one common source of those values.
Array formulas are useful when the calculation naturally involves a set of items. Examples include multiplying each quantity by its price, testing a whole list against a condition, or extracting every row that meets a rule. They can also replace some helper columns, although helper columns may be easier to audit in a complicated workbook.
Excel has two broad kinds of array behavior. Dynamic array formulas, available in Microsoft 365 and newer Excel versions, can spill results from one entered formula into adjacent cells. Older Excel versions used traditional array formulas, which often required selecting the output range and pressing Ctrl+Shift+Enter. The exact behavior depends on the Excel version and the formula.
A Simple Array Formula Example
Suppose a small order table has quantities in A2:A4 and unit prices in B2:B4: 2 units at 5, 3 units at 7, and 4 units at 6. In Microsoft 365, type the following in C2 and press Enter. Excel calculates each corresponding pair of cells and spills the three results downward.
With quantities in A2:A4 and prices in B2:B4, enter this in C2; it returns 10, 21, and 24 in C2:C4:
=A2:A4*B2:B4
The first value comes from 2 multiplied by 5, the second from 3 multiplied by 7, and the third from 4 multiplied by 6. The ranges have the same number of rows, so Excel can match each quantity with the price on its row. C2 is the formula’s anchor cell; C3 and C4 are part of the spill range, not separate formulas.
This calculation demonstrates the array operation, but a practical sheet might also include a heading in C1, such as “Line total.” If you later add another order, extend the source ranges or use an Excel Table so the calculation can account for new rows. A formula using fixed endpoints will not automatically include data placed below its specified ranges.
Calculate One Total from Several Array Results
An array calculation does not have to spill. Excel can multiply corresponding items and then add those products to return one total. Using the same three order rows, the products are 10, 21, and 24, which add to 55—not 65. This illustrates why it is worth checking the arithmetic rather than relying only on a formula’s appearance.
With quantities in A2:A4 and prices in B2:B4, enter this in D2; it returns 55:
=SUM(A2:A4*B2:B4)
In Microsoft 365, pressing Enter is sufficient. Excel evaluates the range multiplication and passes the resulting values to SUM. In older Excel releases, this type of formula generally had to be committed as a traditional array formula with Ctrl+Shift+Enter; otherwise, the calculation might not evaluate the ranges as intended.
For this particular weighted-sum calculation, SUMPRODUCT is a common alternative. With the same quantities in A2:A4 and prices in B2:B4, enter this in D2; it also returns 55:
=SUMPRODUCT(A2:A4,B2:B4)
SUMPRODUCT multiplies corresponding entries and sums the products. It is often convenient when compatibility with older Excel files matters, while the SUM version makes the intermediate array operation more visible. Check the dimensions of the ranges: corresponding entries must be alignable for the calculation to make sense.
Return Matching Rows with FILTER
A dynamic array formula can return multiple cells of information, not just a column of calculated values. For example, a two-column list could contain product names in A2:A6 and order statuses in B2:B6. If the rows are Pen—Open, Notebook—Closed, Folder—Open, Binder—Open, and Tape—Closed, FILTER can return the products whose status is Open.
With names in A2:A6 and statuses in B2:B6, enter this formula in D2; it spills Pen, Folder, and Binder into D2:D4:
=FILTER(A2:A6,B2:B6="Open","No matches")
The first argument is the range to return, and the second is a condition that is checked row by row. The final argument supplies a message if no rows meet the condition. FILTER is available in Microsoft 365 and Excel 2021 or later. In versions without FILTER, users commonly use a filter command, a PivotTable, or a more elaborate legacy formula instead.
The source data remains unchanged: the formula produces a result elsewhere. If records are added, a fixed range such as A2:A6 will not include them automatically. An Excel Table can make source references expand as rows are added, which is useful for lists that grow over time.
How Dynamic Arrays Spill
In modern Excel, enter a dynamic array formula in one cell, and Excel places its results in the cells required for the output. The cell containing the formula is the anchor. The surrounding output area is called the spill range. The formula is stored in the anchor cell; selecting another cell in the spill range does not mean that cell contains a separate copy of the formula.
Spilling makes array formulas easier to edit than many traditional formulas. If the formula changes the number of returned rows, Excel can resize the output automatically, provided the cells it needs are available. You can refer to a spill range with the anchor cell followed by the spill operator, such as D2#. That reference is useful when another formula needs the complete current result, but it is not necessary for the examples above.
Dynamic arrays can also change the layout of a report as data changes. That is helpful for summaries and filtered lists, but it means downstream content should not be placed where the result might expand. Leave room for the expected spill area, and check that it will not overlap notes, totals, or other formulas.
Entering Array Formulas in Older Excel
Before dynamic arrays, many formulas that calculated over ranges were entered as traditional array formulas. A common method was to select the intended output cell or cells, type the formula, and press Ctrl+Shift+Enter instead of Enter. Excel then marked the formula as an array formula in its interface, traditionally showing braces around it in the formula bar. Do not type those braces yourself; they are an indicator, not part of the formula syntax.
For a single-result calculation such as the SUM example, older Excel typically required selecting the result cell before entering the formula with Ctrl+Shift+Enter. For a formula designed to return several results, the user often selected an output range of the appropriate size first. If the output range was too small or too large, the result could be incomplete or include errors.
On Windows, Ctrl+Shift+Enter is the traditional keystroke. On Mac, the corresponding legacy array-entry shortcut is generally Control+Shift+Return or Enter, depending on keyboard labeling and Excel version. Current dynamic array formulas normally need only Enter on either platform. Excel for the web supports many current dynamic array features, but its behavior and available editing tools can differ from the Windows desktop app; use the desktop version when maintaining older CSE formulas is important.
Choosing the Right Array Approach
Choose a method based on the result you need and the Excel versions that will open the workbook. A formula that spills is concise for a changing list, while a single-result formula is appropriate for a total. A familiar function may be preferable when colleagues use older software or need to inspect every intermediate calculation.
- Use direct range arithmetic when you want a result for every corresponding row, such as each quantity multiplied by its price.
- Use SUM around an array calculation when you need one aggregate answer and the workbook’s Excel versions support the formula as written.
- Consider SUMPRODUCT for a weighted total or compatibility with older workbooks.
- Use FILTER when you need a live list of rows that match a condition and the workbook will be used in Microsoft 365 or Excel 2021 and later.
- Use a helper column, AutoFilter, or PivotTable when transparency, easy manual review, or compatibility is more important than a compact formula.
Dynamic array functions such as FILTER, SORT, UNIQUE, and SEQUENCE require Microsoft 365 or Excel 2021 and later. A workbook opened in an older version may show a compatibility error or fail to calculate those functions correctly. If a file will be shared broadly, confirm the recipients’ Excel versions before relying on newer functions.
Common Problems and How to Fix Them
A #SPILL! error means Excel cannot place the complete dynamic result in the required cells. Look for existing values or formulas in the spill area, merged cells, or other obstructions. Clear or move the obstructing content, then check whether the result has room to expand. Excel can identify the blocking cell when you select the error.
A wrong result can also come from mismatched ranges. If quantities cover A2:A10 but prices cover B2:B9, the paired calculation does not describe matching rows. Make sure corresponding ranges start and end on the intended rows, and confirm that numbers stored as text have not been mistaken for numeric values.
With a traditional array formula, editing only part of a multi-cell array may be blocked because the selected cells belong to one array formula. Select the whole array range when changing or deleting it. Conversely, do not select a multi-cell range when entering a modern spill formula unless the formula specifically requires that procedure; usually you enter it only in the anchor cell.
When a FILTER result is empty, its optional third argument can display a helpful message instead of an empty-result error. If results appear incomplete, check the condition range and the source range for the same row count, and make sure the condition text matches the data, including spaces. A formula may be mathematically valid yet still return unexpected records if the source values are inconsistent.
Array Formulas Compared with Helper Columns
Array formulas can reduce the number of intermediate cells in a worksheet, but fewer cells do not always make a workbook easier to understand. A helper column can show the line total beside each transaction, making it simple to inspect a suspicious row. An array formula can produce the same calculations in one place or feed a summary directly, which may be more compact.
For a shared workbook, consider the person who will maintain it. If an array formula combines several operations that are hard to explain, breaking the work into named columns can improve traceability. If a list must update automatically when criteria change, a dynamic array formula may be simpler than manually copying results. The best choice is the one that stays correct as the data changes and remains understandable to its users.
Frequently Asked Questions
Do I still need Ctrl+Shift+Enter in Excel?
Usually not for new formulas in Microsoft 365 or Excel 2021 and later. Those versions support dynamic arrays and generally accept formulas with Enter. Older array formulas in legacy workbooks may still use Ctrl+Shift+Enter.
Can an array formula return just one value?
Yes. An array calculation can process many values internally and then return one result, as when SUM adds a set of calculated products. Other array formulas return multiple results that spill into adjacent cells.
Why does my formula show #SPILL!?
Excel cannot place the full result in the required cells. Check for content, merged cells, or other obstructions in the intended spill area, and make space for the result.
Can I type curly braces around an array formula?
No. In older Excel, braces were an indicator that the formula had been committed as a traditional array formula. They were added by Excel after the Ctrl+Shift+Enter entry method; typing braces manually does not create that behavior.
Will a dynamic array formula work in older Excel?
Not necessarily. Functions such as FILTER require Microsoft 365 or Excel 2021 and later. A workbook opened in an older release may not calculate the formula, so use a compatible alternative if recipients use older versions.
Related
- Excel Data Analysis: Skills, Tools, and Limits
- Excel for Literature Analysis: What It Can and Can’t Do
- Excel in Business Analysis: Uses, Limits, and Skills
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Sum Lookup: Sum Values That Match a CriterionTo add every value that matches a lookup criterion in Excel, use SUMIF. With product names in A2:A10,…
- Unique List From Excel Column: Formulas and OptionsTo create a unique list from an Excel column in Microsoft 365, enter =UNIQUE(A2:A100) in an empty cell,…
- SUMIF Function Examples: Text, Numbers, Dates in ExcelExcel’s SUMIF function adds numbers that meet one condition. With product names in A2:A6 and sales in B2:B6,…
- Edit a Custom Sort List in ExcelTo edit a custom sort list in Excel for Microsoft 365 on Windows, open File > Options >…
- Excel Split Cell by Character: Methods and FormulasTo split a cell at a repeated separator such as a comma, hyphen, or space, select the cells…
- Microsoft Excel Filter Duplicates: Find or Remove ThemExcel does not have a dedicated “filter duplicates” command in a column’s filter menu. To show repeated records,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.