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.
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 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.
- 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
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
- Previous Salary
- Current Salary
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.
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.
4. Press CTRL + SHIFT + ENTER to apply curly braces
Here’s how the formula should look like in the formula bar.
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!
|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|