Select Page

# Why You Should Get Cozy with Array Formulas in Excel

25 August 2023

There comes a time in every Excel aficionado’s life when basic formulas and functions just don’t cut it anymore. Picture this: you have thousands of cells filled with data, and you need to do something with them—quickly and efficiently. The answer? Array formulas. Unlocking their potential could be a real game-changer for you, and here’s why.

## What Are Array Formulas, Anyway?

Let’s start with the basics. An array formula in Excel is a special kind of formula that performs operations on multiple values rather than just one. The end result can either be a single value or, interestingly, an entire set of values. For instance, you can use `SUM(LEN(range))` to sum up the number of characters in a specified cell range.

In versions of Excel older than 365, many array formulas had to be entered in a special way, using the Control + Shift + Enter key combo (affectionately abbreviated as “CSE”). Upon doing so, Excel would automatically wrap the formula in curly braces `{}`, signaling that you’ve entered the realm of array formulas. But a word of caution! The curly braces can’t be manually entered—they must be “bestowed” upon you by Excel.

## So What’s an Array, Really?

An array is a collection of more than one item. In Excel, arrays are represented within curly brackets, e.g., {1;2;3} or {“red”, “blue”, “green”}. This is a pretty intuitive way to represent cell ranges on a spreadsheet: vertical ranges are presented as arrays using semicolons, and horizontal ranges are represented as arrays using commas.

Array formulas might seem daunting, but in practice, they’re fairly straightforward. Take the `OR(A1:A5="a")` function, for example. This formula compares each cell in the range A1:A5 to the string “a.” Because the comparison occurs on multiple values, the `OR` function gets a bunch of results, like `OR({FALSE;FALSE;FALSE;TRUE;FALSE})`. If even one value in the resulting array is `TRUE`, the `OR` function says, “Yep, that’s a TRUE!”

Another example is `SUM(LEN(B2:B11))`, which returns the total character count in the range B2:B11. Here, the `LEN` function is fed multiple values and spits out an array, which the `SUM` function then happily adds up.

## The Rule Breakers

Not all array formulas require the “CSE” treatment. Some, like `SUMPRODUCT`, are programmed to handle array operations natively.

## Conclusion

Array formulas in Excel are a potent tool for advanced operations on large data sets. They can dramatically improve your workflow and make you see Excel in a completely new light. So, if you haven’t dived into the world of array formulas yet, it’s about time you did.

That’s all on this topic. Analyze in peace!

Share it on Social Media 📱
>>> You can share it on LinkedIn and show that you learn something new every day.
>>> You can throw it on Facebook – and perhaps help a friend of yours who is looking for this.
>>> And remember to bookmark this page, you never know if it won’t come handy in in the future.

You prefer to watch 📺 – no problem
>>> Subscribe and watch my English channel on YouTube.

Wanna keep readin’? Here’s some juicy stuff:

Prefer to read in Polish? Here you go!

Przeczytaj coś jeszcze:

## Perfect job for introverts or do data analysts need other people

Ja Ci ją z przyjemnością wyślę. Za darmo. Bez spamu.