How to Build an Array Formula in Excel 

An array formula is a special formula that operates on a range of values in Excel . When you build an array formula in a worksheet, you press Ctrl+Shift+Enter to insert an array formula in the array range.

To get an idea of how you build and use array formulas in a worksheet, consider the example below. This worksheet is designed to compute the biweekly wages for each employee. It will do this by multiplying each employee's hourly rate by the number of hours worked in each pay period. Use an array formula instead of creating the following formula in cell R10 and copying it down to cells R11 through R13:

Building an array formula to calculate hourly wages for the first pay period.

=A4*R4

You can create the following array formula in the array range:

={A4:A7*R4:R7}

This array formula multiplies each of the hourly rates in the 4 x 1 array in the range A4:A7 with each of the hours worked in the 4 x 1 array in the range R4:R7. This same formula is entered into all cells of the array range (R10:R13) as soon as you complete the formula in the active cell R10. To see how this is done, follow along with the steps required to build this array formula:

1.      Make cell R10 the active cell, and then select the array range R10:R13 and type = (equal sign) to start the array formula.

You always start an array formula by selecting the cell or cell range where the results are to appear. Note that array formulas, like standard formulas, begin with the equal sign.

2.      Select the range A4:A7 that contains the hourly rate for each employee, type * (an asterisk for multiplication), and then select the range R4:R7 that contains the total number of hours worked during the first pay period.

3.      Press Ctrl+Shift+Enter to insert an array formula in the array range.

Excel inserts braces around the formula and copies the array formula {=A4:A7*R4:R7} into each of the cells in the array range R10:R13.

When entering an array formula, you must remember to press Ctrl+Shift+Enter instead of just the Enter key because this key combination tells Excel that you're building an array formula, so that the program encloses the formula in braces and copies it to every cell in the array range.

The figure below shows you the February wage table after completing all the array formulas in three ranges: R10:R13, AI10:AI13, and AJ10:AJ13. In the second cell range, AI10:AI13, the following array formula was entered to calculate the hourly wages for the second pay period in February:

Hourly wage spreadsheet after entering all three array formulas.

{=A4:A7*AI4:AI7}

The following array formula was entered in the third cell range, AJ10:AJ13, to calculate the total wages paid to each employee in February 2010:

{=R10:R13+AI10:AI13}

When you enter an array formula, the formula should produce an array with the same dimensions as the array range that you selected. If the resulting array returned by the formula is smaller than the array range, Excel expands the resulting array to fill the range. If the resulting array is larger than the array range, Excel doesn't display all the results. When expanding the results in an array range, Excel considers the dimensions of all the arrays used in the arguments of the operation. Each argument must have the same number of rows as the array with the most rows and the same number of columns as the array with the most columns.