## 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!

## My Favorite Learning Resources:

Here are some of the learning resources I’ve personally found to be useful as a data analyst and I hope you find them useful too!

These may contain affiliate links and I earn a commission from them if you use them.

However, I’d honestly recommend them to my juniors, friends, or even my family!

### My Recommended Online Courses + Books!

To see all of my most up-to-date recommendations, check out this resource I’ve put together for you here.