Quick, clean, and to the point

Array operation

Array operation examples

The distinctive feature of an array formula is that it includes an array operation. Prior to Excel 365, many formulas with array operations had to be entered with control + shift + enter to work properly. In Excel 365, arrays are native, and this is not necessary.

An array operation is some kind of operation that touches or manipulates the values in an array directly. This can be a logical comparison, a math operation, concatenation, or even feeding an array into another function. Array formulas can include one or more array operations. The final result of an array formula can be a single value or multiple values.

In the example shown above, the following formulas appear, each representing a different type of array operation:

=B5:B13>6 // logical comparison
=B5:B13+1 // math operation
=B5:B13&"x" // concatenation
=INT(B5:B13) // another function

For example, the formula in D5 is:


This is an array operation below compares values in the range B5:B13 to 6 with the greater than operator (>):


This is a logical expression with an array that comes from the range B5:B13. Expanded, we have:


Because there are 9 cells in B5:B13, the result is an array with 9 TRUE or FALSE values: