Excel Multi-Cell Array Formulas

01
of 02

Perform Calculations in Multiple Cells with One Excel Array Formula

Perform Calculations in Multiple Cells with One Excel Array Formula
Perform Calculations in Multiple Cells with One Excel Array Formula. © Ted French

In Excel, an array formula carries out calculations on one or more elements in an array.

Array formulas are surrounded by curly braces "{ } ". These are added to a formula by pressing the Ctrl, Shift, and Enter keys together after typing the formula into a cell or cells.

Types of Array Formulas

There are two types of array formulas:

  • single cell array formulas - formulas carrying out multiple calculations in  single worksheet cell;
  • multi-cell array formula - formulas carrying out the same calculations in multiple worksheet cells.

How a Multi-Cell Array Formula Works

In the image above, the multi-cell array formula is located in cells C2 to C6 and it carries out the same mathematical operation of multiplication on the data in the ranges of A1 to A6 and B1 to B6

Because it is an array formula, each instance or 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 is cell A1 by the data in cell B1 and returns a result of 8;
  • The instance of the array formula in cell C2 multiplies the data is cell A2 by the data in cell B2 and returns a result of 18;
  • The instance of the array formula in C3 multiplies the data is cell A3 by the data in cell B3 and returns a result of 72.
02
of 02

Creating the Base Formula

Selecting Ranges for a Multi-Cell Array Formula
Selecting Ranges for a Multi-Cell Array Formula. © Ted French

Multi-Cell Array Formula Example

The formula in the image above multiplies the data found in column A by the data in column B. To do this, ranges are entered rather than individual cell references as found in regular formulas:

{= A2:A6 * B2:B6}

Creating the Base Formula

The first step in creating a multi-cell array formula is to add the same base formula to all cells where the multi-cell array formula will be located.

This is done by highlighting or selecting the cells before beginning the formula.

The steps below cover creating the multi-cell array formula shown in the image above in cells C2 to C6: 

  1. Highlight cells C2 to C6 - these are the cells where the multi-cell array formula will be located;
  2. Type an equal sign ( = ) on the keyboard to begin the base formula.
  3. Highlight cells A2 to A6 to enter this range into the base formula;
  4. Type an asterisk symbol ( * )  - the multiplication operator - following the range A2:A6;
  5. Highlight  cells B2 to B6 to enter this range into the base formula;
  6. At this point, leave the worksheet as is - the formula will be completed in the last step of the tutorial when the array formula is created.

Creating the Array Formula

The last step is turning the base formula located in the range C2:C6 into an array formula.

Creating an array formula in Excel is done by pressing the ​CtrlShift, and Enter keys on the keyboard.

Doing so surrounds the formula with curly braces: { } indicating that it is now an array formula.

  1. Hold down the Ctrl and Shift keys on the keyboard then press and release the Enter key to create the array formula.
  2. Release the Ctrl and Shift keys.
  3. If done correctly, the formulas in cells C2 to C6 will be surrounded by curly braces and each cell will contain a different result as seen the first image above.​
    Cell Result
    C2: 8 - formula multiplies the data in cells A2 * B2
    C3: 18 - formula multiplies the data in cells A3 * B3
    C4: 72 - formula multiplies the data in cells A4 * B4
    C5: 162 - formula multiplies the data in cells A5 * B5
    C6: 288 - formula multiplies the data in cells A6 * B6
    

When you click on any of the five cells in the range C2:C6 the completed array formula:

 {= A2:A6 * B2:B6} 

appears in the formula bar above the worksheet.

Format
mla apa chicago
Your Citation
French, Ted. "Excel Multi-Cell Array Formulas." ThoughtCo, Jun. 4, 2017, thoughtco.com/excel-multi-cell-array-formulas-3123733. French, Ted. (2017, June 4). Excel Multi-Cell Array Formulas. Retrieved from https://www.thoughtco.com/excel-multi-cell-array-formulas-3123733 French, Ted. "Excel Multi-Cell Array Formulas." ThoughtCo. https://www.thoughtco.com/excel-multi-cell-array-formulas-3123733 (accessed May 24, 2018).