Lifting

The term "lifting" refers to an array calculation behavior in Excel formulas. When you give a range or array to a function not programmed to accept arrays natively, Excel will "lift" the function and call it multiple times, one time for each value in the array. The result is an array with the same dimensions as the input array.

Lifting is a built-in behavior that happens automatically. Lifting can occur when an array is fed into a function argument that doesn't accept an array (but does accept single values), or when a range is fed into a function argument that doesn't accept a range or an array.

Note: in Excel 2021 or later, you will see lifting happen in real-time as multiple results spill onto the worksheet. In earlier versions of Excel, lifting still occurs, but only one result is displayed in the cell that contains the formula.

Example

The example shown illustrates what happens if you call the LEN function on the range C5:C7, which contains three values. LEN isn't programmed to handle arrays natively, so LEN is run three times, once for each value in an operation like this:

=LEN(C5:C7)
=LEN({"dog";"kitten";"fish"})
={3;6;4}

Notice the result is a vertical array with three values, just like the source range.

Some Excel functions, like EOMONTH, resist lifting and spilling when provided a range — they won't automatically spill results without extra help. For more details and a list of functions that have this problem, see: Why some functions won't spill.

Dealing with multiple results

When lifting occurs in a formula, there will be multiple results, and these need to be catered for.

In the example above, because LEN returns three separate values in an array, we need handle the output with a function that can work with arrays. One option is to calculate a total character count in the range C5:C7 using SUMPRODUCT: