Array Formulas and Functions

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:

1. Highlight the range D4:D7
2. Enter the array formula =B4:B7*C4:C7
3. 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 that 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 Ctrl-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:

1. Highlight the output range D3:I4
2. Enter the array formula =TRANSPOSE(A3:B8)
3. 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. If the highlighted range is too small the output will be truncated, while if it is too large the extra cells will be filled with the error value #N/A.

As for array formulas the formula bar contains the array formula enclosed in curly brackets. As always, it is essential to press Ctrl-Shft-Enter.

Dynamic array formulas

Microsoft has recently introduced a new type of array formula, available only with Excel 365, called a dynamic array formula, which is easier to use. Click here for more information about these formulas.

Examples Workbook

Click here to download the Excel workbook with the examples described on this webpage.

References

Cheusheva, S. (2023) Excel array formulas, functions and constants – examples and guidelines
https://www.ablebits.com/office-addins-blog/array-formulas-functions-excel/#:~:text=There%20exist%20a%20few%20Excel,using%20Ctrl%20%2B%20Shift%20%2B%20Enter.

Microsoft Support (2020) Guidelines and examples of array formulas
https://support.microsoft.com/en-au/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7#:~:text=An%20arra