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.
Nice post on arrays! Your video was nice to follow (I haven’t used the F9 for formula auditing, and it’s always good to learn new shortcuts).
I often tell people that arrays are some of the true power to Excel, and I think more people should become experts with arrays. They really aren’t too difficult with a bit of practice, and they can save a ton of time!
The most practical use I’ve found for arrays are essentially to meet the need of “A SumIf with Multiple Criteria.” They make it easy. So, in my mind, once you learn the basics, then it’s time to jump into arrays!
Thanks for the post!
Thank you for your nice comments Brady. And thanks for following my blog! More examples and videos on the way.
Happy Christmas sir.
You nicely explained it. Thank you sir.
We want more videos.. Success is with us.
Thank you! Many more videos coming in 2016, starting in February.
The video is not available. Pl make it available.
Thanks for letting me know. The video is visible now.
The video is not available now.
Thanks for letting me know, Jan. I’ll look into it today and post back when I have the video linked properly.