How to Simplify Your Calculations in Excel Using Array Formulas
Perform Your Calculations in One Step and Lose the Extra Columns.
If you do a lot of number crunching in Excel that involve arrays and hate the thought of having to create extra columns of numbers to display intermediate results, then you have come to the right place. As it turns out, there is a simpler and more elegant way to carry out multi-step calculations involving arrays. Excel has long enabled the use of user-designed formulas that can accept arrays as arguments, although these features still remain relatively unknown except to intermediate and advanced users. Known as array formulas, they are also sometimes referred to as CSE formulas, because in order for them to be invoked, the user must hit CTRL SHIFT ENTER (rather than the usual ENTER) after entering the formula. Depending on the types of calculations you need to perform, an array formula may either reside in one cell or in more than one if the results consist of more than one value.
As will be shown below, array formulas enable you to perform, in fewer steps, many different types of calculations not covered by Excel's list of native functions. If this is your first time using array formulas and you want to get familiar with their use, this introductory tutorial will walk you through the basic steps of performing some simple one step calculations using single-cell array formulas. Also, this article illustrates ways to construct your own array formulas using some simple examples.
Have you ever heard of array formulas in Excel?
Step One: Check Excel's List of Native Functions.
Array formulas enable you to bypass the process of having to create extra columns of intermediate results in your workbook so that you can get at the final result of your calculations more directly. They not only make it easier to organize your workbook but also cut down on its size. There is a tradeoff, however, since the information in (what used to be) those extra columns is stored temporarily in Excel's memory. For less powerful computers, array formulas can sometimes result in a substantial performance hit. This is more problematic if you work with very large data sets or use many array formulas and have your workbook set for automatic updates.
Therefore, the first step is to double check Excel's list of native functions to see if there is already one that can perform the calculations you have in mind. One way to do this is to check the functions lists within the "Insert Function" dialog window, as illustrated above. A native function will not only save you the extra work of typing in a custom-designed array formula, but it will most likely execute faster. For example, let's say you want to calculate the sum of squares of a column of numbers. In the old days (ca. 1990s), you would have had to create an extra column of numbers consisting of the squares of each number in your original data set, then use these new values as your array argument in Excel's SUM function. In later versions of Excel, the SUMSQ function finally became available so that you no longer have to perform this extra step.
Although later versions of Excel are increasingly incorporating more native functions of greater complexity that accept arrays as arguments, they cannot possibly anticipate all prospective user needs. For example, if you want to perform the sum of cubes, or the sum of some higher-order polynomial with more than one term, Excel's native functions do not, as yet, have these capabilities. For situations such as these, array formulas are pretty much the only viable alternative.
For Steps 2-4, I will use as an example taking the sum of cubes in an array since for Excel 2007, the version I will be using throughout this tutorial, a native function is not yet available.
Step Two: Choose the Range of Data on Which You Want to Perform Your Calculations.
In an array formula, you can either enter your data array as a range (e.g., A2:A7), or you can give it a name and use that as the argument.
When I use array formulas, my personal preference is to give my data array a name, which is the method I will be using throughout this tutorial. When you do it this way, if you should ever have to change the number of rows or columns, all you would have to do is make these changes only once within "Name Manager," rather than having to retype and re-enter the array formula. (This also minimizes the possibility of introducing errors during editing, especially for more complicated array formulas in which the data range may appear as an argument more than once.)
To name your data array,
- Select the array of data on which you want to perform your calculations.
- Click on the Formulas Tab at the top.
- Within the "Defined Names" area, click on "Define Name."
- In the dialog window, enter your array name (e.g., MyArray).
Note: The above instructions apply to Excel 2007, the version used for this tutorial, and may differ slightly from one version of Excel to the next.
Step Three - Enter Your Array Formula.
Here, we will be computing the sum of cubes of the numbers in the data array, A2:A7, which I have named MyArray (see Step Two above). The array formula calculations shown here make use of the Excel native functions, SUM and POWER.
- Select the cell in which you want your array formula to reside. (This is where the final result will be displayed.)
- Enter the equals sign (=).
- Right after the = sign, enter your formula in the formula bar. Since we are computing the sum of cubes for each element in MyArray, enter the following: SUM(POWER(MyArray,3))
- Press the CTRL+SHIFT+ENTER buttons simultaneously. You should see the results in the cell in which you entered your array formula (shown above outlined in red). You should also see curly brackets around the formula you just entered in the formula bar as shown in the screenshot above.
Step Four: Test Your Array Formula.
- Click on the cell in which you entered your array formula and verify that it is surrounded by curly brackets in the formula bar.
- Make some changes in one or more cells within your range and verify that the correct final answer is displayed in the cell in which you entered your array formula.
As shown in the above screenshot, by increasing the value of A7 from 6 to 60, the sum of cubes, as shown in Cell B2, updates correctly from 441 (the original value shown above in Step Three) to 216225.
If you forget to press CTRL+SHIFT+ENTER and accidentally press ENTER instead, you will either get an error message or, worse, erroneous results, depending on your version of Excel. Since it is easy to forget, always double check the formula bar for those curly brackets!
How Do I Compute the Total Number of Characters in a Collection of Cells?
For this example, the data reside in Cells A1-A6 (see screenshot), so MyArray = A1:A6. The array formula for computing the total number of characters in these cells (and the final result) reside in Cell B1 (outlined in red).
The number of characters for any string residing in cell A1 is easily found from the LEN function, i.e.,
To find the total number of characters for each member of an array, substitute the array name or range for "A1" in the formula above and use it as the argument for the SUM function, i.e.,
Entering the above as an array formula and you should see the following in the formula bar:
How Do I Determine the Total Number of Words in a Collection of Cells With Strings?
For this example, the data reside in Cells C1-C3, so MyArray = C1:C3. The array formula for computing the total number of words in these three cells (and the final result) reside in Cell D1 (outlined in red).
For a single cell, C1, the number of words can be found by counting the number of times the space character occurs and adding 1, i.e.,
To find the total number of words for each cell in an array, substitute the array name or range for "C1" in the formula above and use that as the argument for the SUM function, i.e.,
Entering the above as an array formula using CTRL+SHIFT+ENTER, you should see the following in the formula bar (as shown in the screenshot above):
I Cannot Enter Anything in the Formula Bar Because Excel's "Insert Function" Dialog Box Keeps Getting in the Way!
Do not click on the "Insert Function" icon to the left of the formula bar (shown here crossed out in red). Instead, select the cell in which you want your formula to reside and type in an equals sign (=).
The equals sign will then appear in the formula bar, so that you can type in whatever formula you wish.