Excel Tutorials: How to use Multi Cell Array Formula

Excel Array Formulas

Excel Array Formula Tutorial

© Ted French

For more Excel tips and tricks go to my static page:  Excel Tutorials and Tricks Series. 

Excel Array Formulas

In Excel, an array formula is a formula that carries out calculations on one or more elements in an array.
An array is range or group of related data values. In a spreadsheet program such as Excel the values in an array are normally stored in adjacent cells.
Array formulas in Excel are surrounded by curly braces "{ } ". These are added to a formula by pressing the CTRL, SHIFT, and ENTER keys after typing the formula into a cell or cells.
For this reason an array formula is sometimes referred to as a CSE formula in Excel.

Types of Array Formulas

There are two types of array formulas - those that are located in multiple cells in a worksheet (multi cell array formula) and those located in a single cell (single cell array formula).

Multi Cell Array Formula Tutorial

This step by step tutorial covers creating a multi cell array formula in Excel.

Multi Cell Array Formula Overview

Excel Array Formula Tutorial

© Ted French

Multi Cell Array Formula Overview

A multiple cell or multi cell array formula is an array formula that is located in more than one cell in
a worksheet. An example of a multiple cell array formula would be:

{=D1:D3 * E1:E3}

How a Multi Cell Array Formula Works

In the image above this array formula is located in cells F1:F3 and it carries out the same mathematical operation of multiplication on the data ranges of D1:D3 and E1:E3.
Even though the same formula is located in cells F1:F3, because it is an an array formula each instance or copy of the formula uses different data in its calculations and each instance produces different results.
For example:

• The instance of the array formula in cell F1 multiplies the data is cell D1 by the data in cell E1.
• The instance of the array formula in cell F2 multiplies the data is cell D2 by the data in cell E2.
• The instance of the array formula in cell F3 multiplies the data is cell D3 by the data in cell E3.

Multi Cell Array Formula Tutorial

The following steps in this tutorial cover creating the multi cell array formula seen in the image above.

Entering the Tutorial Data

Excel Array Formula Tutorial

© Ted French

Entering the Tutorial Data

To begin the tutorial it is necessary to enter our data into an Excel worksheet as seen in the image above.

Cell    Data
 D1  -  2
 D2  -  3
 D3  -  6

 E1  -  4
 E2  -  5
 E3  -  8

Creating the Base Formula

Excel Array Formula Tutorial

© Ted French

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.

Tutorial Steps

For help with these steps see the image above.

1. Drag select cells F1 to F3 to highlight them - 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. Drag select cells D1 to D3 to enter these cell references into the base formula.


4. Type an asterisk symbol ( * ) on the keyboard since we are multiplying the data in column D by the data in column E.


5. Drag select cells E1 to E3 to enter these cell references 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

Excel Array Formula Tutorial

© Ted French

Creating the Array Formula

The last step in the tutorial is turning the base formula located in the range F1:F3 into an array formula.
Creating an array formula in Excel is done by pressing the CTRL, SHIFT, and ENTER keys on the keyboard.
The effect of pressing these keys together is to surround the formula with curly braces: { } indicating that it is now an array formula.

Tutorial Steps

For help with these steps see the image above.

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 F1 to F3 will be surrounded by curly braces and each cell will contain a different result as seen in the image above:

               Cell     Result
                 F1        8 - formula multiplies the data in cells  D1 * E1
                 F2      15 - formula multiplies the data in cells  D2 * E2
                 F3      48 - formula multiplies the data in cells  D3 * E3
   



4. When you click on any of the three cells in the range F1:F3 the completed array formula { = D1:D3 * E1:E3 } appears in the formula bar above the worksheet.