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.


  • 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

  • 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.


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:

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 Learning Platforms!

Learning PlatformWhat’s Good About the Platform?
1CourseraCertificates are offered by popular learning institutes and companies like Google & IBM
2DataCampComes with an integrated coding platform, great for beginners!
3PluralsightStrong focus on data skills, taught by industry experts
4StratascratchLearn faster by doing real interview coding practices for data science
5UdacityHigh-quality, comprehensive courses

My Recommended Online Courses + Books!

TopicOnline CoursesBooks
1Data AnalyticsGoogle Data Analytics Professional Certificate
2Data ScienceIBM Data Science Professional Certificate
3ExcelExcel Skills for Business Specialization
4PythonPython for Everybody SpecializationPython for Data Analysis
5SQLIntroduction to SQLSQL: The Ultimate Beginners Guide: Learn SQL Today
6TableauData Visualization with TableauPractical Tableau
7Power BIGetting Started with Power BI DesktopBeginning Microsoft Power BI
8R ProgrammingData Science: Foundations using R SpecializationLearning R
9Data VisualizationBig Book of Dashboards

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

More Articles For You

Similar Posts