The array formula is probably one of the most powerful tools in Excel – but also the most under-used. For some reason it is a tool that is shunned by many – even advanced users – of Excel. But just because most people don’t know how to use them, does that mean they should be avoided in a financial model?
If you want your model to be user-friendly and able to be used by other people – then, yes. Let’s face it; having a fantastic model no one else can understand is not really a great achievement. Heaven forbid that a modeller would put an array formula in a model just to show off their Excel skills! But surely we shouldn’t have to “dumb down” our models for other people to understand? It depends on who our audience is. If the user, (like most of my clients) is an intermediate Excel user who is likely to want to tinker with their model, then it is best to avoid array formulas.
For most Excel users, coming across the formula {=TRANSPOSE(B1:E1)} is a little perplexing, especially when you can’t edit it. Array formulas don’t follow the usual rules of an ordinary formula (e.g. “Trace precedents” and “Trace dependents” will work on arrays, but don’t help very much!).
Before we go any further, however, let me firstly explain what all the fuss is about, just in case you’re one of the many Excel users who aren’t at ease with array formulas. Array formulas are an advanced type of Excel calculation. An array is basically just a collection of data of the same type that can be treated as a single entity, and an array formula treats the entire array as a single input to the formula.
In order to seriously consider yourself an Excel power user, you should know how to build an array formula – or at least be able to recognise and edit one if you come across it. Array formulas are a very powerful tool, and there are many advantages to using them. One of the main advantages is security – nobody can accidentally delete part of the array block when using an array formula. Also, because the data can be manipulated as a whole block and used in the formula as a single unit, it’s a lot harder to make a mistake when building the formula.
You can tell if someone has used an array formula because it includes curly brackets ({) in the formula. Data tables are a fairly commonly used type of array formula – in fact, I think I need to devote another article to the wonderful world of scenario modelling using data tables.
Advantages of using Array Formulas
- They ensure consistency because all formulas in the table are exactly the same.
- A model containing arrays will use less memory and be more efficient.
- Because it’s not possible to change a single cell on its own within an array formula, it is unlikely that you or someone else will change your formula accidentally.
- Because array formulas are difficult to understand, it means those with only basic Excel knowledge are less likely to change (and mess up!) your formulas.
- Array formulas do make it possible to perform some calculations that would otherwise be impossible using ordinary formulas.
Disadvantages of using Array Formulas
- Although array formulas use less memory, if you use too many large arrays in one model it can slow down your calculation.
- You cannot use column references (such as “A:A” or “D:D”) in your array formulas.
- They are difficult to audit for many Excel users and require specialist skills (both an advantage and a disadvantage!).
Simple Array Formula Example
The best way of explaining the concept of an array formula is to see one in action. Below is a very simple example of an array formula.
1. Create two simple blocks of data (arrays) as below:
2. Highlight the block of cells C1:C5 and type (or highlight) =A1:A5*B1:B5 in the formula bar:
3. Don’t press Enter yet! Now, hold down the Control and Shift keys whilst hitting Enter at the same time (CTRL+SHIFT+ENTER).
4. The formula will appear in the whole array block. Note that the curly brackets have appeared around the formula.
5. Note that you now cannot make any changes to the array block.
Nb: Array formulas are sometimes referred to as “CSE formulas,” because you press CTRL+SHIFT+ENTER to enter them.
