The worksheet formulas that we described in Excel Spreadsheets return a single value that is assigned to the cell that contains the formula. Excel also allows you to define a formula that assigns values to a range of cells at the same time. These are called **array formulas **and they will be used quite often throughout this website.

### Array formulas

We now show how to create formulas that modify multiple cells at the same time.

**Example 1**: Calculate the revenues for each item in the worksheet of Figure 1.

**Figure 1 – Array formula**

Given that the revenue for each item is unit price times quantity, we can enter the formula =B4*C4 in cell D4 and then copy this formula into cells D5, D6 and D7 (e.g. by clicking on cell D4, pressing **Ctrl-C**, then highlighting the range D5:D7 and pressing **Ctrl-V**, or by highlighting the range D4:D7 and pressing **Ctrl-D**).

Another way to do this is via an array formula, using the following steps:

- Highlight the range D4:D7
- Enter the array formula =B4:B7*C4:C7
- Press
**Ctrl-Shft-Enter**(i.e. hold down the**Ctrl**and**Shift**keys and press the**Enter**key)

It is essential to press **Ctrl-Shft-Enter** (step 3) and not simply **Enter** as for an ordinary formula. Note that the formula that appears in the formula bar is {=B4:B7*C4:C7}. The curly brackets indicate that this is an array formula. If the range B4:B7 is given the name UnitPrice and C4:C7 is given the name Quantity, then the array formula can be entered as =UnitPrice*Quantity (step 2);

The array formula appears in all four cells in the range D4:D7. To make changes to the formula you must edit the entire range and not just one, two or three of these cells. Similarly you can’t copy or delete a part of the range, but must copy or delete the entire range. If you attempt to modify a part of the range you will receive an error message. If you get stuck and get a series of such error messages you just need to press the escape key Esc to recover.

You can erase a range that contains an array formula by highlighting the entire range and pressing the **Delete** key. You can write over the array function replacing it by a value or another formula. The important thing is to use the entire range and not a part of the range.

You can also extend the range covered by an array formula by clicking on any one of the four corners of the range and then highlighting a range which includes the original range. Finally you click anywhere on the formula bar and press **Ctrl-Shft-Enter**.

Note too that you can also use array formulas such as {=SUM(B4:B7*C4:C7)}. This returns the value which is the sum of the revenues of the four types of equipment. Even though this formula returns a single value, and so may be placed in a single cell such as D8, it must be entered as an array formula (since the formula contains an embedded array formula). This means that you need to type =SUM(B4:B7*C4:C7) and then press Ctrl-Shft-Enter. If you forget to press C**trl-Shft-Enter** and only press **Enter**, you will get an error message.

### Array functions

A few of Excel’s built-in functions are array functions, where the output of the function is an array. These functions are managed as described above for array formulas.

**Example 2**: Change the data range in columns A and B of Figure 2 into an equivalent row range.

**Figure 2 – Array function**

This can be accomplished by means of Excel’s TRANSPOSE array function using the following steps:

- Highlight the output range D3:I4
- Enter the array formula =TRANSPOSE(A3:B8)
- Press
**Ctrl-Shft-Enter**

Note that the output range (step 1) must be of the right size. In this case, the input range is 6 rows by 2 columns, and so the output range must be 2 rows by 6 columns. As for array formulas the formula bar contains the array formula enclosed in curly brackets. Once again it is important to press **Ctrl-Shft-Enter**.

gets stuck -> get stuck

Konrad,

Thanks for catching this typo. I have now corrected it on the webpage.

Charles

Dear Dr. Zaiontz,

Your website and the Real Statistics add-in for Excel are AMAZING!!! Thank you so much.

Iain Peters, PhD