Tom’s Tutorials For Excel: Explaining Array Formulas
Here’s a video explaining what array formulas are, how to enter them, and a look behind the scenes to show how and why they work. Array formulas are a different animal, requiring extra care to understand how, when, and why it’s a good idea to use them.
As the video explains, an array is simply a collection of items. The items can be cell values (as this video demonstrates), or the items can be some other kind of data that Excel holds in memory, and then acts upon when called to do so.
An array formula is a special kind of formula that can perform multiple calculations simultaneously, on one or more items in the array.
There are two kinds of array formulas. They can return one value in one cell, as this video tip shows, or they can return several values in several cells, which I’ll cover in future video tips.
In the video, a range of data that is potentially hundreds of thousands of rows deep contains two columns whose pairs of numbers are being multipied with each other, then summed, all by a single array formula. This bypasses the need for helper formulas in another column, meaning a single array formula is doing the work of hundreds of thousands of non-array formulas.
In edit mode of any formula — and it’s shown with the array formulas in the video — you can select the arguments within the SUM function’s parentheses, press the F9 key, and observe how the array formula calculates and stores its items to display the final result you see in the cell.
Take some time to understand the basic logic of array formula construction. You’ll find array formulas to be a valuable tool in your Excel problem-solving set of skills.