What is Array Formula

An array formula is a powerful tool in Excel that allows you to perform multiple calculations on one or more sets of values simultaneously. An array can be a single row or column of values, or a combination of multiple rows and columns. Array formulas can produce either multiple results or a single result, depending on how they are used.

Let's consider this example, imaging we have a table with a count and value for something. If we want to find out total for each row of this. Usual approach would look like this:

Start by entering a formula in the first cell that multiplies the count by a value. This can be done easily. Use the autofill feature to apply the formula to the rest of the cells. While this method works, it is not very convenient.

Instead of this we could use the array formula. For this we have to write formula for only one cell and then it will display result in multiple cells.

Note

The formula is only in one cell but the result is display in multiple ones.

But be careful because you might get a #SPILL! error. It shows up if something blocks the range of results. For example there are might be some values in the cells below the array formula.

Once you remove the blocking line, the results will reappear. This makes array formulas excellent for data integrity because you can't accidentally overwrite a part of a multi-cell array formula, even if you press Delete. Excel will not alter the array’s output.