Perform calculations on data
- By Curtis Frye
- 4/9/2019
Use array formulas
Most Excel formulas calculate values to be displayed in a single cell. For example, you could add the formulas =B1*B4, =B1*B5, and =B1*B6 to consecutive worksheet cells to calculate shipping insurance costs based on the value of a package’s contents.
A worksheet with data to be summarized by an array formula.
Rather than add the same formula to multiple cells one cell at a time, you can add a formula to every cell in the target range at the same time by creating an array formula. To create an array formula, you enter the formula’s arguments and press Ctrl+Shift+Enter to identify the formula as an array formula. To calculate package insurance rates for values in the cell range B4:B6 and the rate in cell B1, you would select a range of cells with the same shape as the value range and enter the formula =B1*B4:B6. In this case, the values are in a three-cell column, so you must select a range of the same shape, such as C4:C6. When you press Ctrl+Shift+Enter, Excel creates an array formula in the selected cells. The formula appears within a pair of braces to indicate that it is an array formula.
A worksheet with an array formula ready to be entered.
IMPORTANT
If you enter the array formula into a range of the wrong shape, Excel displays duplicate results, incomplete results, or error messages, depending on how the target range differs from the value range.
IMPORTANT
You can’t add braces to a formula to make it an array formula. You must press Ctrl+Shift+Enter to create it.
In addition to creating an array formula that combines a single cell’s value with an array, you can create array formulas that use two separate arrays. For example, a company might establish a goal to reduce sorting time in each of four distribution centers. This worksheet stores the previous sorting times in minutes in cells B2:B5, and the percentage targets in cells C2:C5. The array formula to calculate the targets for each of the four centers is =B2:B5*C2:C5 which, when you enter it into cells D2:D5 by pressing Ctrl+Shift+Enter, would appear as {= B2:B5*C2:C5}.
)
To edit an array formula, you must select every cell that contains the array formula, click the formula bar to activate it, edit the formula in the formula bar, and then press Ctrl+Shift+Enter to re-enter the formula as an array formula.
TIP
Many operations that used to require an array formula can now be calculated by using functions such as SUMIFS and COUNTIFS.
To create or edit an array formula
Select the cells to include in the array formula.
Enter or edit your array formula.
Press Ctrl+Shift+Enter.