An array formula is a formula that can perform multiple calculations on one or more items in an array. You can think of an array as a row or column of values, or a combination of rows and columns of values. Array formulas can return either multiple results, or a single result.

Beginning with the September 2018 update for Microsoft 365, any formula that can return multiple results will automatically spill them either down, or across into neighboring cells. This change in behavior is also accompanied by several new dynamic array functions. Dynamic array formulas, whether they’re using existing functions or the dynamic array functions, only need to be input into a single cell, then confirmed by pressing Enter. Earlier, legacy array formulas require first selecting the entire output range, then confirming the formula with Ctrl+Shift+Enter. They’re commonly referred to as CSE formulas.

You can use array formulas to perform complex tasks, such as:

• Quickly create sample datasets.
• Count the number of characters contained in a range of cells.
• Sum only numbers that meet certain conditions, such as the lowest values in a range, or numbers that fall between an upper and lower boundary.
• Sum every Nth value in a range of values.