Guidelines and examples of array formulas Tutorial
Applies to: Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Excel for iPad Excel for iPhone
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.
The following examples show you how to create multi-cell and single-cell array formulas. Where possible, we’ve included examples with some of the dynamic array functions, as well as existing array formulas entered as both dynamic and legacy arrays.
Send download link to: