Welcome to Bo's Excel Array Formula Set Page
If you have some experience using formulas in Microsoft Excel, you know that you can perform some fairly sophisticated operations. For example, you can calculate the total cost of a loan over any given number of years. However, if you really want to master formulas in Excel, you need to know how to use array formulas. You can use array formulas to do the seemingly impossible, such as:
... and more. This column introduces array formulas and explains how to enter, edit, and troubleshoot them.
Note You may see array formulas referred to as "CSE formulas," because you press CTRL+SHIFT+ENTER to enter them into your workbooks. The only difference is the name.
If you've done even a little programming, you've probably run across the term array. For our purposes, an array is just a collection of items. In Excel, those items can reside in a single row (called a one-dimensional horizontal array), a column (a one-dimensional vertical array), or multiple rows and columns (a two-dimensional array).
An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and calculate a column or row of subtotals. You can also place a formula in a single cell and calculate a single amount. An array formula that resides in multiple cells is called (logically enough) a multi-cell formula, and an array formula that resides in a single cell is called a single-cell formula. Finally, you can't create three-dimensional arrays or array formulas in Excel.
The examples in the next section show you how to create multi-cell and single-cell array formulas.
This exercise shows you how to use multi-cell and single-cell array formulas to calculate a set of sales figures. The first set of steps uses a multi-cell formula to calculate a set of subtotals. The second set uses a single-cell formula to calculate a grand total.
to match the destination formatting. | Sales Person | Car Type | Number Sold | Unit Price | Total Sales |
| Barnhill | Guzzler | 5 | 2200 | |
| Belcher | 4 | 1800 | ||
| Ingle | Guzzler | 6 | 2300 | |
| Belcher | 8 | 1700 | ||
| Jordan | Guzzler | 3 | 2000 | |
| Belcher | 1 | 1600 | ||
| Pike | Guzzler | 9 | 2150 | |
| Belcher | 5 | 1950 | ||
| Sanchez | Guzzler | 6 | 2250 | |
| Belcher | 8 | 2000 |
Note: If you paste this into your Excel Workbook
Total Sales |
| =C2:C11 *D2:D11 |
| =C2:C11 *D2:D11 |
| =C2:C11 *D2:D11 |
| =C2:C11 *D2:D11 |
| =C2:C11 *D2:D11 |
| =C2:C11 *D2:D11 |
| =C2:C11 *D2:D11 |
| =C2:C11 *D2:D11 |
| =C2:C11 *D2:D11 |
| =C2:C11 *D2:D11 |
and find that the cells are only showing the formula, then there is a chance that you copied some of the code for this webpage into your Excel worksheet.
If total sales looks like this
Then you need to highlight the cells E2 to E11 then click
Edit | Clear
Or, right click and select
Clear Contents from the right click context menu options and then paste the formulae into
the formula bar and use Shift+Ctrl+Enter key combinations. Once this is done, the formulae
will work as described.
=C2:C11*D2:D11
Excel surrounds the formula with braces ({}) and places an instance of the
formula in each cell of the selected range. This happens very quickly, so what you see in
column E is the total sales amount for each car type for each sales person.
=SUM(C2:C11*D2:D11)
In this case, Excel multiplies the values in the array (the cell range C2 through D11) and then uses the SUM function to add the totals together. The result is a grand total of $111,800 in sales. Do you see how powerful this type of formula can be? For example, say you have 15,000 rows of data. You can sum part or all of that data with a single formula in a single cell.
Also, notice that the single-cell formula is completely independent of the multi-cell formula. This points to another advantage of using array formulas - flexibility. You can take any number of actions, such as changing the formulas in column E or deleting that column altogether, without affecting the single-cell formula.
Array formulas also offer these advantages:
For the most part, array formulas use standard formula syntax. They all begin with an equal sign, and you can use any of the built-in functions of Excel in your array formulas. The key difference is the need to press CTRL+SHIFT+ENTER to enter your formulas. This is a must. Excel surrounds your array formula with braces — and you cannot type the braces manually.
The next thing you need to understand is that array functions are a form of shorthand. For example, the multi-cell function that you used earlier is the equivalent of:
=C2*D2
=C3*D3
and so on. The single-cell formula condenses all of those multiplication operations, plus the arithmetic required to add those subtotals: =E2+E3+E4, and so on.
We've already harped on the primary rule, but we're doing it again: Press CTRL+SHIFT+ENTER whenever you need to enter or edit an array formula. That rule applies to both single-cell and multi-cell formulas.
Whenever you work with multi-cell formulas, you also need to follow these rules:
At times, you may need to expand or shrink an array formula.
| Teal | Guzzler | 6 | 2500 |
| Belcher | 7 | 1900 | |
| Wang | Guzzler | 4 | 2200 |
| Belcher | 3 | 2000 | |
| Young | Guzzler | 8 | 2300 |
| Belcher | 8 | 2100 |
Array formulas can work what seems like magic, but they also have some disadvantages:
