Many common Excel functions have a great new power added to them with little fanfare. They can become dynamic array functions which improves the consistency of worksheets. It’s simple and easy to do.
When Microsoft added dynamic arrays to Excel, all the focus was on the new functions like SORT, FILTER, and SEQUENCE that “spill” their results into multiple cells.
What didn’t get enough attention is that many other functions also became dynamic with the ability to return many results from the one function.
This works in Excel 365 plus Excel 2021 and Excel 2024 which also support dynamic arrays.
As an example, let’s use the TEXT() function which reformats a value into another form.
In the olden days (pre-2021<g>), a function like Text() could only accept one input but now it can accept a range. When the function is given a range (not a single cell reference), it knows to spill the results.
First a simple range of cells like A2:A6. The formula =TEXT(A2:A6,”0.00″) in cell B2 spills results down column B, matching the values in column A.
Of course you could just copy the formula down the column, that’s the old fashioned way. But letting a function spill means there’s only one formula to change.
Functions with two ranges
Here’s another example using EXACT() and two ranges. The single formula in H2 spills down the column.
This also works for MOD(). The two ranges have to be the same size.
Reference a dynamic range
If the range you’re referencing is a dynamic range, there’s another, even better way to write the formula – using the # sign. Here’s Roundup(A10#,0) in B10 which references cell A10 to the end of the dynamic array in Column A. This works because the values in Column A are made with a dynamic array function (RandArray() in this case).
The hash symbol tells Excel to start in the cell given and continue along/down the dynamic array to its end.
If the values in Column A weren’t a dynamic array then it’ll give a #REF error.
Excel Functions that can spill
Here’s just some of the common functions that can accept a range input and spill to a dynamic array.
| Function | Example Formula | Description |
|---|---|---|
| ABS | =ABS(A1:A3) | Returns absolute value of each item in array. |
| CEILING | =CEILING(A1:A5, 5) | Rounds numbers up to the nearest multiple of 5. |
| CHAR | =CHAR(A1:A5) | Returns character for each ASCII code in the range. |
| CODE | =CODE(A1:A5) | Returns ASCII code of first character in each string. |
| CONCAT | =CONCAT(A1:A5) | Concatenates values; with array input, spills in some contexts. |
| EXACT | =EXACT(A1:A5, B1:B5) | Compares corresponding items in two arrays; spills TRUE/FALSE. |
| FLOOR | =FLOOR(A1:A5, 10) | Rounds numbers down to the nearest multiple of 10. |
| IF | =IF(A1:A3>5, “High”, “Low”) | Applies condition to array and spills results. |
| INT | =INT(A1:A5) | Rounds each number down to the nearest integer. |
| ISBLANK | =ISBLANK(A1:A5) | Checks if each cell is blank; spills array of TRUE/FALSE. |
| ISNUMBER | =ISNUMBER(A1:A5) | Checks if each item is a number; spills TRUE/FALSE values. |
| LEN | =LEN(A1:A3) | Returns length of each string in the range; spills results vertically. |
| LOWER | =LOWER(A1:A5) | Converts each item in the array to lowercase. |
| MOD | =MOD(A1:A5, 3) | Returns the modulo (remainder) of each value divided by 3. |
| N | =N(A1:A5) | Converts values to numbers (text = 0, TRUE = 1, etc.). |
| PROPER | =PROPER(A1:A5) | Capitalizes the first letter of each word in strings. |
| REPT | =REPT(“★”, A1:A5) | Repeats the star character based on array values. |
| ROUND | =ROUND(A1:A3, 2) | Rounds each number to 2 decimal places. |
| ROUNDDOWN | =ROUNDDOWN(A1:A5, 1) | Rounds each number down to 1 decimal place. |
| ROUNDUP | =ROUNDUP(A1:A5, 1) | Rounds each number up to 1 decimal place. |
| SQRT | =SQRT(A1:A3) | Returns square root of each value. |
| TEXT | =TEXT(A1:A3,”0.00″) | Formats each number in the array; spills formatted strings. |
| TEXTJOIN | =TEXTJOIN(“-“, TRUE, A1:C1) (with spill arrays in A1:C1) | Can combine spilled arrays into a string (though returns scalar). |
| TRIM | =TRIM(A1:A5) | Removes leading/trailing spaces from each string. |
| TYPE | =TYPE(A1:A5) | Returns type code for each cell (number, text, logical, etc.). |
| UPPER | =UPPER(A1:A5) | Converts each string to uppercase. |
| VALUE | =VALUE(A1:A3) | Converts text to number for each element in array. |
Exceptions
Before you get too excited, there are exceptions:
Some functions accept a range and return a single value such as SUM, AVERAGE, MIN or MAX. If you put a range into one of these functions, it takes all the given values to return a single result. In nerd speak, the functions collapse an array.
There are other functions don’t accept a range input in the way we’ve shown above. But there’s a sneaky way to do it, see Excel Spill Mystery: Why It Happens and How to Fix It
