Excel 2013 Array Formulas: How to Enter and Use Them

Formulas & functions · Updated November 22, 2026

In Excel 2013, an array formula performs calculations on one or more sets of values and may return a single result or several results. To enter one, type the formula, then press Ctrl+Shift+Enter instead of Enter; Excel adds curly braces around it in the formula bar.

What an array formula does

A standard formula usually works with one value at a time. An array formula can apply an operation to each item in a range, then combine the resulting values or return them as a group. This can make a compact formula possible when you need to calculate across multiple rows without adding a helper column.

For example, suppose a worksheet lists quantities and unit prices in separate columns. An array formula can multiply each row’s quantity by its price and add those products. The formula does not require the products to be calculated and stored in another column first. Array formulas can also test multiple conditions, perform calculations that ordinary formulas cannot conveniently express, or return a series of results to a selected range.

“Array formula” describes how Excel evaluates the formula, not a single function. Functions such as SUM, IF, INDEX, and MATCH can appear in array formulas, but those functions do not automatically make every formula containing them an array formula. In Excel 2013, the key distinction is often how you confirm the formula: a legacy array formula is committed with Ctrl+Shift+Enter.

How to enter one in Excel 2013

  1. Select the cell where you want the result, or select the entire output range if the formula should return several values.
  2. Type the formula, including its leading equals sign.
  3. Press Ctrl+Shift+Enter together. Do not type the braces yourself.

Afterward, Excel displays curly braces around the formula in the formula bar, such as {=SUM(B2:B6*C2:C6)}. The braces are Excel’s indicator that the formula was committed as an array formula; they are not part of the text you type. Pressing Enter alone does not create the same legacy array formula.

If you need to change a single-cell array formula, select its cell, edit the formula, and confirm it again with Ctrl+Shift+Enter. For an array formula entered across multiple cells, select the whole array range before editing or deleting it. Excel generally prevents changes to only part of that range because the cells belong to one array formula.

Excel 2013 was a Windows desktop release. There was no Excel 2013 release for Mac; Mac users have different version numbers. In current Excel for Mac, the legacy array-formula shortcut is generally Control+Shift+Return, but the exact keyboard behavior can depend on the Mac keyboard and Excel version.

Example: multiply rows and total the results

Imagine that quantities are in B2:B6 and unit prices are in C2:C6. If the quantities are 2, 3, 1, 4, and 2, and the corresponding prices are 5, 4, 8, 2, and 6, enter this formula in D2 and confirm it with Ctrl+Shift+Enter:

=SUM(B2:B6*C2:C6)

Excel multiplies corresponding items, producing 10, 12, 8, 8, and 12, then sums them. The result is 50. The calculation is equivalent to first working out each row’s quantity times price and adding those five amounts, but no helper column is needed.

For this particular task, SUMPRODUCT is often easier because it performs this kind of array calculation without requiring Ctrl+Shift+Enter. With the same quantities in B2:B6 and prices in C2:C6, enter =SUMPRODUCT(B2:B6,C2:C6) in D2 and press Enter; it also returns 50. SUMPRODUCT is useful when you want a formula that is less dependent on remembering the special keystroke. It is not a replacement for every array formula, however, especially when a formula needs to return multiple results.

Example: sum values that meet a condition

Suppose region names are in A2:A6 and sales amounts are in B2:B6. The regions are East, West, East, North, and East, with sales of 100, 150, 200, 50, and 75. To add sales only for East, enter this formula in D2 and confirm it with Ctrl+Shift+Enter:

=SUM((A2:A6="East")*(B2:B6))

The comparison creates TRUE or FALSE values for the region test. In this multiplication, Excel treats TRUE as 1 and FALSE as 0, so only sales on rows marked East contribute to the sum. The result is 375 (100 + 200 + 75).

If you only need a conditional sum, SUMIF is usually simpler. With the same region names in A2:A6 and sales in B2:B6, enter =SUMIF(A2:A6,"East",B2:B6) in D2 and press Enter; the result is 375. Array formulas become more useful when the calculation involves combinations of conditions or operations that SUMIF does not express as directly.

Example: look up a value using two conditions

A regular lookup often searches for one key. An array formula can combine two tests—for instance, to find the price for a particular product from a particular supplier. Assume products are in A2:A6, suppliers in B2:B6, and prices in C2:C6. Put the product to find in E2 and the supplier in F2. If a matching row exists and is unique, enter this formula in G2 and confirm it with Ctrl+Shift+Enter:

=INDEX(C2:C6,MATCH(1,(A2:A6=E2)*(B2:B6=F2),0))

The two comparisons produce matching TRUE/FALSE patterns. Multiplying them leaves a 1 only where both conditions are true; MATCH finds that position, and INDEX returns the price from the corresponding row. If the matching row’s price is 24.50, the formula returns 24.50. If no row matches, MATCH returns an error, and duplicate matches return the first match. Keep the three data ranges the same size so each row’s product, supplier, and price stay aligned.

Single-cell results and multiple-cell results

A single-cell array formula performs array calculations but displays one answer, as the SUM examples do. A multi-cell array formula returns a group of results. In Excel 2013, you must select the full destination range before entering the formula. The selected cells then belong to a single array formula rather than a set of independently editable formulas.

For example, if quantities are in B2:B6 and prices are in C2:C6, select D2:D6, type =B2:B6*C2:C6, then press Ctrl+Shift+Enter. The five cells in D2:D6 show 10, 12, 8, 8, and 12, respectively, using the sample values above. If you select only D2, the formula is not set up to fill the five-cell output range.

Excel 2013 does not have the modern spill behavior found in newer versions. In Microsoft 365 or Excel 2021 and later, some formulas can return an array that spills automatically into neighboring cells. Excel 2013 instead requires you to select the destination range for a multi-cell legacy array formula. Do not expect a formula that spills in a newer workbook to behave the same way when opened in Excel 2013.

When to use an array formula instead of another method

Array formulas are valuable when they make a real calculation clearer or avoid a large collection of helper formulas. They are not automatically better than ordinary formulas. Consider these options before choosing:

  • Use a regular function when it directly solves the task. SUMIF and COUNTIF are often more straightforward for one condition.
  • Use SUMPRODUCT for many row-by-row calculations that need one combined result and can be expressed with its arguments.
  • Use a helper column when intermediate results should be visible, checked, or reused by other calculations.
  • Use a legacy array formula when the calculation needs array behavior that the simpler alternatives do not provide.

Array formulas can be harder for another person to maintain because the special entry method and range behavior are not obvious from the cell display alone. In a shared workbook, label the result, document unusual logic, and avoid selecting a much larger range than necessary. A formula covering only the data rows is usually easier to inspect than one applied to entire columns.

Common errors and how to fix them

  • The braces are missing: If the formula requires legacy array entry, edit it and press Ctrl+Shift+Enter. Do not add braces by typing them; Excel supplies them when the formula is committed correctly.
  • Excel says part of an array cannot be changed: Select the entire array range before editing or deleting the formula. To replace it, work with the complete output range rather than one cell inside it.
  • The result is wrong or an error appears: Check that related ranges have equal lengths and that each row’s values line up. In the two-condition lookup, also check that the requested product and supplier actually occur together.
  • The formula is slow: Avoid unnecessarily large ranges, particularly when the formula repeats comparisons or calculations. Limit the ranges to the used rows and check whether a standard function or helper column would be simpler.

When debugging, check the inputs and the result range before rewriting the formula. In Excel 2013, pressing F2 edits the active cell’s formula; after making a change to a legacy array formula, confirm it again with Ctrl+Shift+Enter. For a multi-cell formula, select the full array range first. If you replace the formula with an ordinary formula intentionally, make sure you understand whether the calculation still needs array behavior.

Excel 2013 compared with newer Excel

Excel 2013 uses the older, Ctrl+Shift+Enter style of array formula. Newer Microsoft 365 and Excel 2021 versions support dynamic arrays, which can return multiple results and spill them into adjacent cells after a normal Enter. Functions such as FILTER, SORT, UNIQUE, and SEQUENCE are not available in Excel 2013, so examples using those functions will not work there.

This difference matters when sharing workbooks across versions. A modern dynamic-array formula may not calculate in Excel 2013, while a legacy array formula can still be useful in older workbooks. If a workbook must work in both Excel 2013 and newer versions, choose functions supported by both and test the formulas in the oldest version that needs to open the file. A newer Excel installation may recalculate some legacy formulas, but that does not give Excel 2013 access to newer functions or spill behavior.

Frequently Asked Questions

How do I know if a formula is an array formula in Excel 2013?
When you select a legacy array-formula cell, Excel shows curly braces around the formula in the formula bar. The braces are added by Excel; they are not typed into the formula.

Do I always need Ctrl+Shift+Enter in Excel 2013?
No. You need it for formulas that you want Excel 2013 to commit as legacy array formulas. Ordinary formulas and functions such as SUMPRODUCT can often complete similar calculations with Enter alone.

Can I edit one cell in a multi-cell array formula?
Not independently while it remains part of the array formula. Select the entire formula range to edit or remove it, then enter the revised formula for the full range if needed.

Why does my formula show an error when no match exists?
A lookup that uses MATCH returns an error when its search value is absent. Check the criteria values and source ranges; if a missing match is a normal possibility, you can add error handling, but choose the displayed result to suit the worksheet’s purpose.

Do Excel 2013 array formulas spill automatically?
No. Excel 2013 does not have dynamic-array spill behavior. To return several results with a legacy array formula, select the intended output range before entering the formula with Ctrl+Shift+Enter.

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.