Array functions belong to the past

Long ago, Excel was not as clever and advanced as it is today, and the so-called array functions are an inheritance of this past. However, most people don’t know about them, and don’t understand them. This is perhaps because of the unintuitive method that activates these formulas (CTRL-SHIFT ENTER instead of just ENTER).

Array function explained

An array formula is, simply stated, a formula that very cleverly combines multiple calculations as an array. For example, SUM (B2: B10 * C2: C10) is the sum of (B2 * C2) + (B3 * C3) etcetera. The result of the array function will only be shown after you double click the cell with the formula to select it and then press CTRL+SHIFT+ENTER.

The danger of using array functions

It’s better not to use array functions unless it’s truly necessary because, your spreadsheet might later be maintained by someone who does not understand array functions. They might change something in the array formula without knowing that it is an array…so they won’t press CTRL-Shift-Enter and your nice functionality is lost and gone. Or they might make a change in the input column of the array without realizing the consequences, and everything goes wrong.

Alternatives to array functions

Fortunately, there are alternative methods to get to the same results. For example, use an extra column for an intermediate calculation (B2 * C2, as in the example). There is rarely a lack of space since Excel 2010. You can also choose to use new smart built-in Excel features like SUMIFS, MAXIF, and AVERAGEIFS, which can also be combined to attain similar results as those that come with an Array function.

Exceptions

When is it safe to use array functions? If you’re sure nobody else ever has to worry about it and/or you are the only user of the spreadsheet (but how do you know?). They can also be used in situations where the spreadsheet’s memory usage needs to be kept very limited, because array formulas are memory efficient.