This section tackles array formulas, the poorly-documented underbelly of Excel. Since this feature first caught my eye, it’s proven to be an invaluable companion.
One of the best features of array formulas is that you can almost always cut and paste the logical pieces of your previous work as you build an application. In comparison, the more commonly used native Excel tools (e.g., pivot tables, native array formulas, and manual calculations) just seem clumsy, with awkward dialogue boxes, inconsistent syntax, and (gasp!) manual work.
Since this feature first caught my eye, it’s proven to be an invaluable companion.
Be warned: not all of Excel’s basic formulas are compatible with array formulas, so the transition takes a bit of adjustment. However, the approaches you adopt to use array formulas are compatible with native functions. As a result, I now find myself using the conventions that work in array formulas so that the transition is graceful when I inevitably find a reason to ‘go there’.
This section will present my approach to syntax in array formulas. As noted in the philosophy of this website, there are a gazillion – yes, literally a gazillion – ways to perform any task in Excel. While I will not tout my personal preferences as ‘best practices’, my hope is to make this domain a little more accessible for anyone interested to learn!