Excel Multi-Cell Array Formulas

Content
  1. Perform calculations in multiple cells with one Excel array formula
  2. Array Formula Types
  3. How a multi-cell formula works
  4. Create a basic formula
  5. Multi-cell formula example
  6. Create a basic formula
  7. 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;

  • multi-element array formula – formulas that perform the same calculations in different cells of the sheet.

How a multi-cell formula works

In the above image, the multi-cell array formula is located in cells C2-C6 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

Multi-cell 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 multi-cell array formula shown in the above image in cells C2-C6:

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

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

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

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

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

  6. 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.

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

  2. Release the keys ctrl and Shift

  3. If done correctly, the formulas in cells C2-C6 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.

Leave a Reply

Your email address will not be published.