What is an Array Formula? (A Helpful Explanation)

Introduction

What exactly is an Array Formula?

As the name goes, it is a specific formula that is meant for arrays as opposed to a formula for single values. An array is a set of columns or rows or even a combination of both.

Now, this may sound confusing now, but it will all seem clearer after some explanation. I know it may sound complex, but in fact, it is a rather simple concept that you can learn as a beginner.

An array formula applies a formula across ranges instead of single cells. It allows useful calculation formulas to be applied across a dataset. Therefore, when specifying the arguments in a formula, a range (array constant) is referenced. Following the reference, curly braces have to be added to confirm the formula as an array instead of the single-cell manner.

Array Constant

A range of cells that is reference entirely. It is stored as memory in Excel and it is used as an argument for the function.

Curly Braces

{}

Curly braces indicate that an array formula has been applied across an array. However, you are not required to manually type them in. You can use a shortcut (Ctrl + Shift + Enter) to enter them automatically. This method is also known as the CSE method.

Uses

• Calculating sums of number for specific conditions, such as the maximum in a range, using the MAX function
• For rapidly building sample datasets
• Summing Nth values within a range

Example

Let us demonstrate this with an example of the MAX function being applied on a simple dataset. We will be looking at some data regarding the difference between the Current and Previous Salaries of some employees. Here’s how the dataset looks like.

Employee Salary Dataset

• Employee
• Previous Salary
• Current Salary
• Difference

Normal Calculations (without using Array Formulas)

In the example of a regular method of calculation, where array formula is not used, the MAX function is applied across the column Difference.

=MAX(D2:D5)

Calculations using Array Formula

When calculating the maximum by using the array formula, use the following steps:

1. Use =MAX function

2. Select C2:C5 instead of C2 (Current Salary)

3. Select B2:B5 instead of B2 (Previous Salary)

Here’s what the formula should look like for now.

=MAX(C2:C5:B2:B5)

4. Press CTRL + SHIFT + ENTER to apply curly braces

Here’s how the formula should look like in the formula bar.

{=MAX(C2:C5:B2:B5)}

And that’s how an array formula can be used, instead of the single-cell method that is more commonly used. With proper usage, functions can be applied across entire ranges without having to specify single-cells. This is potentially powerful for any Excel user looking to increase their efficiency!