 Perform calculations in multiple cells with one Excel array formula
 Array Formula Types
 How a multicell formula works
 Create a basic formula
 Multicell formula example
 Create a basic formula
 Create an array formula
Perform calculations in multiple cells with one Excel array formula
In Excel, an array formula performs calculations on one or more elements in an array.
Array formulas are enclosed in braces” † † They are added to the formula by pressing the keys at the same time Ctrl, Shift and Enter after entering a formula in a cell or cells.
Array Formula Types
There are two types of array formulas:

single cell array formulas – formulas that perform multiple calculations in one cell of the sheet;

multielement array formula – formulas that perform the same calculations in different cells of the sheet.
How a multicell formula works
In the above image, the multicell array formula is located in cells C2C6 and performs the same math operation to multiply the data in the range A1 to A6 and B1 to B6.
Since this is an array formula, any example or a copy of the formula is exactly the same, but each instance uses different data in its calculations and produces different results.
For example:

The instance of the array formula in cell C1 multiplies the data in cell A1 by the data in cell B1 and returns the result 8;

The instance of the array formula in cell C2 multiplies the data in cell A2 by the data in cell B2 and returns the result 18;

The instance of the array formula in C3 multiplies the data in cell A3 by the data in cell B3 and returns the result 72.
Create a basic formula
Multicell formula example
The formula in the image above multiplies the data in column A by the data in column B. To do this, enter ranges instead of individual cell references, as in regular formulas:
{= A2: A6 * B2: B6}
Create a basic formula
The first step in creating a multicell array formula is to add the same base formula to all cells that contain the multicell array formula.
This is done by highlighting or selecting cells before starting the formula.
The steps below are about creating the multicell array formula shown in the above image in cells C2C6:

Select cells from C2 to C6 – these are the cells where the array formula of different cells will be placed;

Enter an equal sign ( † ) on the keyboard to start the basic formula.

Select cells A2 to A6 to enter this range into the basic formula;

Enter the asterisk ( † ) – multiplication operator – in the range A2: A6;

Select cells B2 to B6 to enter this range into the basic formula;

Leave the worksheet as it is – the formula will be completed in the last step of the tutorial when the array formula is created.
Create an array formula
The last step is to convert the base formula, which is in the range C2:C6, into an array formula.
Creating an array formula in Excel is done by pressing the keys ctrl † Shift and Enter on keyboard. †
In this case, the formula is surrounded by braces: {}, indicating that it is now an array formula.

Keep the keys pressed ctrl and Shift on the keyboard, then press the key Enter to create a matrix formula. †

Release the keys ctrl and Shift †

If done correctly, the formulas in cells C2C6 will be enclosed in braces and each cell will contain a different result, as shown in the first image above.
Результат ячейки C2: 8  формула умножает данные в ячейках A2 * B2 C3: 18  формула умножает данные в ячейках A3 * B3 C4: 72  формула умножает данные в ячейках A4 * B4 C5: 162  формула умножает данные в ячейках A5 * B5 C6: 288  формула умножает данные в ячейках A6 * B6
When you click on one of the five cells in the range C2:C6, the completed array formula is:
{= A2: A6 * B2: B6}
appears in the formula bar above the worksheet.