Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.
What is Array Formula | Array Formulas
Excel Formulas
course content

Course Content

Excel Formulas

Excel Formulas

1. Basic Formulas
2. Array Formulas
3. Lookup and Reference

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.

What is an array formula in Excel?

Select the correct answer

Everything was clear?

Section 2. Chapter 1
We're sorry to hear that something went wrong. What happened?
some-alt