An array formula is a special formula that is converted from a normal formula by the Ctrl+Shift+Enter shortcut.

Excel automatically adds curly brackets “{}” at the beginning and end of an array formula. The essence of the array formula is a cell formula, used to explicitly notify the Excel calculation engine to perform multiple calculations on it.

Multiple calculations are the process of performing the relevant calculations separately and simultaneously on the elements of an array that have the corresponding relationships in the formula.

However, not all formulas that perform multiple calculations must be edited into array formulas. Using arrays in the arguments of functions of array type or vector type that return a single result, these functions can automatically perform multiple calculations without using array formulas, such as the SUMPRODUCT function, LOOKUP function, MMULT function and MODE.

Multi-cell array formula

In a single cell using an array formula for multiple calculations, sometimes you can return a set of results, but the cell can only display a single value (usually the first element of the array results), and can not display the results of the entire group. Using multi-cell array formulas, you can display each element of the result array in a different cell.

Multi-cell array formula example

Here is a sales table, if you want to calculate the sales volume of different salesmen, then you can multiply the unit price of E3:E10 by the quantity of F3:F10.

The steps are as follows:

STEP1# Select the G3:G10 cell area, and enter the following formula in the formula bar (excluding the curly brackets on both sides)

=E3:E10*F3:F10

STEP2# Press Ctrl+Shift+Enter shortcut keys to convert the formula into an array formula

STEP3# You can see that the sales volume of different salesmen can be calculated by an array formula.

This type of formula that uses the same formula in multiple cells and is converted by the Ctrl+Shift+Enter shortcut is called a “multi-cell array formula“.

The above formula multiplies the unit price of each product by the respective sales quantity to obtain a memory array. The memory array is as below:

{450;690;250;931;1131;1421;532;1862}

Then, they are all displayed in the G3:G10 cell area.

Note：The curly brackets "{}" at the beginning and end of an array formula are automatically generated by the Ctrl+Shift+Enter shortcut. If you enter curly brackets manually, Excel will recognize them as text characters, and they will not work correctly as formulas.

 Single-cell array formula

Single-cell array formulas are array formulas that perform multiple calculations in a single cell and return a single value.

Single-cell array formula example

If you want to calculate the total sales profit of all products, then you can use a single cell array formula to do the statistics.

You can use the following array formula in cell G12, and then press Ctrl+Shift+Enter to convert the formula to an array formula.

=SUM(E3:E10*F3:F10)*G1

The formula first multiplies the unit price and sales of each product, and then uses the SUM function to add up all the elements in the array to get the total sales. Finally, the total sales are multiplied by the profit margin in cell G1, and the returned value is the total sales profit for all products.

As the parameters of the SUM function are numeric types, they cannot directly support multiple calculations, so they must be in the form of an array formula to explicitly inform Excel to perform multiple operations.

The formula in this example can be replaced by the SUMPRODUCT function:

=SUMPRODUCT(E3:E10*F3:F10)*G1

The parameters of the SUMPRODUCT function are the array type, which directly supports multiple calculations, so you can enter the formula as a normal formula and get the correct result.