Array Functions are Past Times

Long ago, Excel was not as clever and advanced as today, but the so-called array functions have been around for a long time. However, people do not know them, and do not understand them well. Perhaps because of the very different nature of the formulas or the strange way of activating (CTRL-SHIFT ENTER instead of just ENTER).

Excel Guideline 09: Array Functions are Past Times :: PerfectXL

What is an Array Function?

An array formula is briefly 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.

Excel Guideline 09: Array Functions Are Past Times - Example :: PerfectXL

The Danger of using Array Functions

It’s better not to use array functions unless it’s really necessary. Because, imagine your spreadsheet will later be maintained by someone who does not understand array functions very well. 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 will be lost and gone. Or they make a change in the input column of the array without realizing the consequences and everything goes wrong.

Alternatives

Fortunately, there are alternative methods to get to the same results. For example, use an extra column for an intermediate calculation (A1 * B1, as in the example). There is rarely a lack of space since Excel 2010. Or use new smart built-in Excel features like SUMIFS, MAXIF, and AVERAGEIFS, which can also be combined.

Excel Guideline 09: Array Functions Are Past Times - Example :: PerfectXL

When to Use Array Functions

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?) And if the spreadsheet’s memory usage needs to be kept very small. Because array formulas surely are efficient!

Never miss a Guideline!