Excel Tutorials: How to use Single Cell Array Formula

Single Cell Array Formula Overview

Excel Array Formula Tutorial

© Ted French

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

Single Cell Array Formula Overview

A single cell array formula differs from regular Excel formulas in that it performs multiple calculations in one cell in a worksheet without the need for nesting functions.
An example of a single cell array formula would be:

{ =SUM (D1:D3 * E1:E3 ) }

How a Single Cell Array Formula Works

Single cell array formulas normally first carry out a multi cell array calculation - such as multiplication - and then use a function such as or AVERAGE or SUM to combine the output of the array into a single result.
In the image above the array formula first multiplies together those elements in the two ranges D1:D3 and E1:E3 that reside in the same row in the worksheet.
The results of these multiplication operations are then added together by the SUM function.
Another way of writing the above array formula would be:

(D1 * E1) + (D2 * E2) + (D3 * E3)

Single Cell Array Formula Tutorial

The following steps in this tutorial cover
creating the single 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

Adding the SUM Function

Excel Array Formula Tutorial

© Ted French

Adding the SUM Function

The next step in creating the single cell array formula is to add the sum function to cell F1 - the location where the single cell array formula will be located.

Tutorial Steps

For help with these steps see the image above.

1. Click on cell F1 - this is where the single cell array formula will be located.


2. Type an equal sign ( = ) to begin the sum function.


3. Type the word sum followed by a left round bracket " ( ".


4. Drag select cells D1 to D3 to enter these cell references into the sum function.


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


6. Drag select cells E1 to E3 to enter these cell references into the function.


7. Type a right round bracket " ) " to close the ranges that will be summed.


8. 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 sum function located in cell F1 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 cell F1 will contain the number " 71 " as seen in the image above.


4. When you click on cell F1 the completed array formula { =SUM (D1:D3 * E1:E3) } appears in the formula bar above the worksheet.