Array formulas

Array formulas are probably the weirdest thing you’ll ever see in Excel. You can recognise them because they’ve got funny squiggly brackets { } at each end of the formula. It seems to us that most people first encounter them when they see them in someone else’s model and risk breaking them. If you go into an array formula to interrogate it (e.g. by pressing “F2”) and then press “Enter” you’ll rapidly find yourself in trouble. The formula and the model will break. You’ll find that going back and manually entering those squiggly { } brackets from your keyboard will do you absolutely no good at all. For once you’ll be hoping that you didn’t save so you can close down the model and re-open with the array formula working again.

Array formulas: our top tip

Here’s our top tip for array formulas: you have to press “Ctrl” “Shift” “Enter” at the end. On an existing array formula that will get the squiggly brackets back and the formula working again. Our lesson on array formulas will give you a chance to practise that, plus find out a bit more about array formulas.

Doing two things at once in a model

Array formulas are really a buy-one-get-one-free or two-for-one special. At this point on the course we’ve already met some formulas that seem to do to things at once:

  • The day job for the Sumproduct formula is to multiply one set of numbers (e.g. £ sales amounts) by another set of numbers (e.g. % commission rates) and add up the total (£ commission due). Sumproduct multiplies and it adds up.
  • Similarly the Sumifs formula sums up data if it meets certain criteria (remember the John dope over-spend investigation where we looked at Excel’s Sumifs?). Sumifs kind-of combines an If formula with a Sum function. It’s a two-for-the-price-of-one special.

What are Array formulas all about?

Imagine an Excel world before the invention of Sumif and Sumproduct. How could you do two things at once in Excel? That’s what array formulas are there for.

Array formulas

These days we have lots of standard functions like Sumif and Suproduct that will happily do two things at once for you, so you shouldn’t need or encounter array formulas very often at all. But people can get very confused about array formulas. “Shift” “F3” for insert function won’t help you and neither will Excel’s own usually extremely comprehensive “F1” help. Like the conditional Sumproduct, array formulas are another of Excel’s best-kept secrets. The first time for most people is when they see these very weird formulas in someone else’s model and break them: we feel we need to prepare you that. Remember: “Ctrl”, “Shift”, “Enter” is what you need to do at the end.

Start the course on Array formulas

Today we think we’ve got a good example (courtesy of one of our training course delegates working in funds management who could describe his problem to us) where we use a unique array formula to short cut the calculation of investment returns across a long list of assets. If you like the idea of maybe doing two things at once in a spreadsheet, you’ll want to take a look at our free course on Excel formulas.