If you have experience using formulas in 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:

Count the number of characters in a range of cells.

Sum numbers that meet certain conditions, such as the lowest values in a range or numbers that fall between an upper and lower boundary.

Sum every nth value in a range of values.
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.
A quick introduction to arrays and array formulas
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 onedimensional horizontal array), a column (a onedimensional vertical array), or multiple rows and columns (a twodimensional array). You can't create threedimensional arrays or array formulas in Excel.
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 multicell formula, and an array formula that resides in a single cell is called a singlecell formula.
Hot keys for controlling array formulas

Press F9 Calculates formulas that have changed since the last calculation, and formulas dependent on them, in all open workbooks. If a workbook is set for automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9 Calculates formulas that have changed since the last calculation, and formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9 Calculates all formulas in all open workbooks, regardless of whether they have changed since last time or not.

Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates all formulas in all open workbooks, regardless of whether they have changed since last time or not.
The examples in the next section show you how to create multicell and singlecell array formulas.
Try it!
This exercise shows you how to use multicell and singlecell array formulas to calculate a set of sales figures. The first set of steps uses a multicell formula to calculate a set of subtotals. The second set uses a singlecell formula to calculate a grand total.
To create a multicell array formula

Start Excel and open a new, blank workbook.

Copy this table and then paste it into the new workbook starting at cell A1. Use the Paste Options button 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


To multiply the values in the array (the cell range C2 through D11), select cells E2 through E11, and then enter the following formula in the formula bar:
=C2:C11*D2:D11

Press CTRL+SHIFT+ENTER.
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.
To create a singlecell array formula

In cell A13 of the workbook, type Total Sales.

In cell B13, enter the following formula, and then press CTRL+SHIFT+ENTER:
=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 singlecell formula is completely independent of the multicell 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 singlecell formula.
Array formulas also offer these advantages:

Consistency If you click any of the cells from E2 downward, you see the same formula. That consistency can help ensure greater accuracy.

Safety You cannot overwrite part of a multicell array formula. For example, click cell E3 and press DELETE. You have to either select the entire range of cells and change the formula for the entire array, or leave it as is.

Smaller file sizes You can often use a single array formula instead of several intermediate formulas. For example, the workbook you created for this exercise uses two array formulas. If you had used standard formulas (such as =C2*D2), you would have used 11 different formulas.
A look at array formula syntax
For the most part, array formulas use standard formula syntax. They all begin with an equal sign, and you can use any of the builtin Excel functions in your array formulas. The key difference is that when using array formulas, you must press CTRL+SHIFT+ENTER to enter your formulas. When you do this, 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 multicell function that you used earlier is the equivalent of:
=C2*D2
=C3*D3
and so on. The singlecell formula condenses all of those multiplication operations, plus the arithmetic required to add those subtotals: =E2+E3+E4, and so on.
Rules for entering and changing array formulas
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 singlecell and multicell formulas.
Whenever you work with multicell formulas, you also need to follow these rules:

You must select the range of cells to hold your results before you enter the formula. You did this in step 3 of the multicell array formula exercise.

You can't change the contents of an individual cell in an array formula. To try this, select cell E3 in the sample workbook and press DELETE.

You can move or delete an entire array formula, but you can't move or delete part of it. In other words, to shrink an array formula, you first delete the existing formula, and start over.

You can't insert blank cells into a multicell array formula.
Expanding an array formula
At times, you may need to expand an array formula (don't forget, you can't shrink them). The process is not complicated, but you must remember the rules listed in the previous section.

In the sample workbook, clear any text and singlecell formulas located below the main table.

Paste these additional lines of data into the workbook starting at cell A12. Use the Paste Options button to match the destination formatting.
Teal
Guzzler
6
2500
Belcher
7
1900
Wang
Guzzler
4
2200
Belcher
3
2000
Young
Guzzler
8
2300
Belcher
8
2100


Select the range of cells that contains the current array formula (E2:E11), plus the empty cells next to the new data (E12:E17).

Press F2 to start edit mode.

In a formula bar, Change C11 to C17, change D11 to D17, and then press CTRL+SHIFT+ENTER. Excel places an instance of the new formula in the new cells.
Disadvantages of using array formulas
Array formulas can work what seems like magic, but they also have some disadvantages:

You may forget to use CTRL+SHIFT+ENTER. Remember to press those keys whenever you enter or edit an array formula.

Other users may not understand your formulas. Array formulas are relatively undocumented, so if other people need to modify your workbooks, you should either avoid array formulas or make sure they understand how to change them.

Depending on the speed of your system, large array formulas can slow down calculations.
Comments
0 comments
Article is closed for comments.