In this lesson, we will get acquainted with the multi-cell array formula, analyze a good example of its use in Excel, and also note some usage features. If you are not familiar with array formulas, we recommend that you first turn to the lesson, which describes the basic principles of working with them.
Applying a multicell array formula
The figure below shows a table with the name of the product, its price and quantity. Cells D2:D6 calculate the total cost of each type of product (taking into account the quantity).
In this example, the range D2:D6 contains five formulas. A multi-cell array formula allows you to calculate the same result using a single formula. To use an array formula, follow these steps:
- Select the range of cells where you want to display the results. In our case, this is the range D2:D6.
- As with any formula in Excel, the first step is to enter the equal sign.
- Select the first array of values. In our case, this is the range with the prices of goods B2:B6.
- Enter the multiplication sign and extract the second array of values. In our case, this is a range with the number of products C2:C6.
- If we were to enter a regular formula in Excel, we would end the entry by pressing the key Enter. But since this is an array formula, you need to press the key combination Ctrl + Shift + Enter. This will tell Excel that this is not a regular formula, but an array formula, and it will automatically enclose it in curly braces.
Excel automatically encloses an array formula in curly braces. If you insert brackets manually, Excel will interpret this expression as plain text.
- Note that all cells in the range D2:D6 contain exactly the same expression. The curly braces around it indicate that it is an array formula.
- If we selected a smaller range when entering the array formula, for example, D2:D4, then it would return only the first 3 results to us:
- And if the range is larger, then in the “extra” cells there would be a value #N/A (no data):
When we multiply the first array by the second, their respective elements are multiplied (B2 with C2, B3 with C3, B4 with C4, etc.). As a result, a new array is formed, which contains the results of calculations. Therefore, to get the correct result, the dimensions of all three arrays must match.
Benefits of multicell array formulas
In most cases, using a single multi-cell array formula in Excel is preferable to using multiple individual formulas. Consider the main benefits it offers:
- Using a multi-cell array formula, you are 100% sure that all formulas in the calculated range are entered correctly.
- The array formula is more protected from accidental change, since only the entire array as a whole can be edited. If you try to change part of the array, you will fail. For example, if you try to delete a formula from cell D4, Excel will issue the following warning:
- You will not be able to insert new rows or columns in a range where an array formula is entered. To insert a new row or column, you will have to redefine the entire array. This point can be considered both an advantage and a disadvantage.
So, in this lesson, you got acquainted with multi-cell array formulas and analyzed a small example. If you want to learn even more about arrays in Excel, read the following articles:
- Introduction to array formulas in Excel
- Single cell array formulas in Excel
- Arrays of constants in Excel
- Editing array formulas in Excel
- Applying array formulas in Excel
- Approaches to editing array formulas in Excel