Carry Out Multiple Calculations with Excel Array Formulas

Man working in modern office
10'000 Hours / Getty Images

An Excel array formula is a formula that carries out calculations on the values in one or more arrays rather than a single data value. In spreadsheet programs, an array is a range or series of related data values that are normally stored in adjacent cells in a worksheet. This guide explains how to use array formulas in all modern versions of Excel.

This tutorial is compatible with most versions of Excel including 2007, 2010, 2013, 2016, and Office 365.

What Are Array Formulas?

Array formulas are similar to regular formulas:

  • They begin with an equal sign ( = )
  • Use the same syntax as regular formulas
  • Use the same mathematical operators
  • Follow the same order of operation

In Excel, array formulas are surrounded by curly braces { } — these braces cannot just be typed in; they must be added to a formula by pressing the Ctrl, Shift, and Enter keys after typing the formula into a cell or cells. For this reason, an array formula is sometimes referred to as a CSE formula in Excel.

Keyboard with Ctrl, Shift, and Enter keys

An exception to this rule is when curly braces are used to enter an array as an argument for a function that normally contains just a single value or cell reference. For example, in the tutorial below that uses VLOOKUP and the CHOOSE function to create a left lookup formula, an array is created for the CHOOSE function's Index_num argument by typing the braces around the entered array.

Steps to Creating an Array Formula

  1. Enter the formula.
  2. Hold down the Ctrl and Shift keys on the keyboard.
  3. Press and release the Enter key to create the array formula.
  4. Release the Ctrl and Shift keys.

If done correctly, the formula will be surrounded by curly braces, and each cell holding the formula will contain a different result.

Editing an Array Formula

Any time an array formula is edited, the curly braces disappear from around the array formula. To get them back, the array formula must be entered by pressing the Ctrl, Shift, and Enter keys again just as when the array formula was first created.

Types of Array Formulas

There are two main types of array formulas:

  • Single cell array formulas which carry out multiple calculations in a single worksheet cell.
  • Multi-cell array formulas which are located in more than one worksheet cell.
Calculus on blackboard
Math in Excel commonly uses array formulas. Image Source / Getty Images

Single Cell Array Formulas

This type of array formula uses a function, such as SUM, AVERAGE, or COUNT, to combine the output of a multi-cell array formula into a single value in a single cell. An example of a single cell array formula would be:

{=SUM (A1:A2 * B1:B2)}

This formula adds together the product of A1*B1 and A2*B2, and then it returns a single result in a single cell in the worksheet. Another way of writing the above formula would be:

= (A1 * B1) + (A2 * B2)

Multi-Cell Array Formulas

As their name suggests, these array formulas are located in multiple worksheet cells, and they return an array as an answer. In other words, the same formula is located in two or more cells, and it returns different answers in each cell.

Each copy, or instance, of the array formula, performs the same calculation in each cell it inhabits, but each instance of the formula uses different data in its calculations. Therefore, each instance produces different results. An example of a multiple cell array formula would look like:

{=A1:A2 * B1:B2}

If the above example is located in cells C1 and C2 in a worksheet, then the results would be as follows:

  • The data in A1 is multiplied by the data in B1, and the results are stored in cell C1.
  • The data in A2 is multiplied by the data in B2, and the results are stored in cell C2.
Screenshot of Excel showing a formula before becoming an array

Using Arrays to Transpose Rows and Columns

The TRANSPOSE function is used to copy data from a row into a column or vice versa. This function is one of the few in Excel that must always be used as an array formula.

Array Formulas and Excel Functions

Many of Excel's built-in functions, such as SUM, AVERAGE, and COUNT, can also be used in an array formula. There are also a few functions, such as the TRANSPOSE function, that must always be entered as an array in order for it to work properly. The usefulness of many functions such as INDEX and MATCH or MAX and IF can be extended by using them together in an array formula.

Examples of Array Formulas in Excel:

of 08

Create a Simple Single Cell Array Formula

Screenshot of Excel showing how to create a simple single cell array formula

Single cell array formulas normally first carry out a ​multi-cell calculation and then use a function such as AVERAGE or SUM to combine the output of the array into a single result.

of 08

Ignore Error Values when Finding Data

Screenshot of Excel showing how to ignore error values

This array formula uses the AVERAGE, IF, and ISNUMBER functions to find the average value for the existing data while ignoring error values such as #DIV/0! and #NAME?

of 08

Count Cells of Data

Screenshot of Excel showing how to count cells

Use the SUM and IF functions in an array formula to count cells of data that meet one of several conditions; this technique differs from using Excel's COUNTIFS function, which requires all set conditions to be met before the cell is counted.

of 08

Find the Largest Positive or Negative Number

Screenshot of Excel showing how to find the largest number

This example combines the MAX function and IF function in an array formula that will find the largest or maximum value for a range of data when specific criteria are met.

of 08

Find the Smallest Positive or Negative Number

Screenshot of Excel showing how to find the smallest number

Similar to the example above, this one combines the MIN function and IF function in an array formula in order to find the smallest or minimum value for a range of data when specific criteria are met.

of 08

Find the Middle or Median Value

Screenshot of Excel showing the middle value

The MEDIAN function in Excel finds the middle value for a list of data. By combining it with the IF function in an array formula, the middle value for different groups of related data can be found.

of 08

Make a Lookup Formula with Multiple Criteria

Screenshot of Excel showing how to make a lookup formula

This array formula involves nesting the MATCH and INDEX functions to find specific information in a database.

of 08

Make a Left Lookup Formula

Screenshot of Excel showing how to make a left lookup formula

The VLOOKUP function normally only searches for data located in columns to the right, but by combining it with the CHOOSE function, you can create a left lookup formula that will search columns of data to the left of the Lookup_value argument.