An array formula is a special formula available in Excel 2010 that operates on a range of values. An array formulas require 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. Instead of using the formula =B6*C6, create the following array formula in any range:

={B6:B10*C6:C10}

array-formula-2013-04-26

This array formula multiplies each of the hourly rates in the 5 x 1 array in the range B6:B10 with each of the hours worked in the 5 x 1 array in the range C6:C10. This same formula is entered into all cells of the array range (C14:C18) as soon as you complete the formula in the active cell C14. To see how this is done, follow along with the steps required to build this array formula:

  1. Make cell C14 the active cell, and then select the array range C14:C18 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 B6:B10 that contains the hourly rate for each employee, type * (an asterisk for multiplication), and then select the range C6:C10 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 {=B6:B10*C6:C10} into each of the cells in the array range C14:C18.

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 wage table after completing all the array formulas in three ranges: C14:C18, D13:D18, and E14:E18. In the second cell range, D14:D18, the following array formula was entered to calculate the hourly wages for the second week of the pay period:

{=B6:B10*D6:D10}

array-formula-2013-04-26-2

The following array formula was entered in the third cell range, E14:E18, to calculate the total wages paid to each employee in the pay period:

{=B6:B10+E6:E10}

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.