**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:**

**My Recommended Learning Platforms!**

Learning Platform | What’s Good About the Platform? | |
---|---|---|

1 | Coursera | Certificates are offered by popular learning institutes and companies like Google & IBM |

2 | DataCamp | Comes with an integrated coding platform, great for beginners! |

3 | Pluralsight | Strong focus on data skills, taught by industry experts |

4 | Stratascratch | Learn faster by doing real interview coding practices for data science |

5 | Udacity | High-quality, comprehensive courses |

**My Recommended Online Courses + Books!**

Topic | Online Courses | Books | |
---|---|---|---|

1 | Data Analytics | Google Data Analytics Professional Certificate | – |

2 | Data Science | IBM Data Science Professional Certificate | – |

3 | Excel | Excel Skills for Business Specialization | – |

4 | Python | Python for Everybody Specialization | Python for Data Analysis |

5 | SQL | Introduction to SQL | SQL: The Ultimate Beginners Guide: Learn SQL Today |

6 | Tableau | Data Visualization with Tableau | Practical Tableau |

7 | Power BI | Getting Started with Power BI Desktop | Beginning Microsoft Power BI |

8 | R Programming | Data Science: Foundations using R Specialization | Learning R |

9 | Data Visualization | – | Big Book of Dashboards |