An array is a row or column of values, or a combination of them. Array formulas can be very useful in many applications where normal Excel formulas don't work. An array formula is a formula that can perform multiple calculations on one or more items in an array.
Array formulas can return either multiple results, or a single result. We can use array formulas to perform complex tasks, such as:
- create quickly sample datasets
- count some values contained in a range of cells
-
sum numbers that meet certain conditions, such as the
lowest values in a range
- sum every Nth value in a range of cells
- find specific values in a cell or a range of cells
- etc.
The following example shows you how the single-cell array formula can find the last filled cell in a given row and show its value. Here is the formula (all entered in a single cell):