Spill functions can fill neighbouring cells with their results, to create dynamic ranges. These examples show how to use Excel's new functions, such as FILTER and SORT. Also, see how older functions, and custom VBA functions, can "spill" too. Thanks to Dermot Balson, who created these examples.
NOTE: Spill functions (dynamic arrays) are available in Microsoft 365 plans.
Normal formulas can only fill the cell they are in, but "spill formulas" can fill neighbouring cells.
The examples below show how to use Excel's new functions, such as FILTER and SORT, and you'll see how older functions, and custom VBA functions, can "spill" too.
You can get the sample file, with all of these examples, and several additional examples, in the download section.
This is the test data that is used in some of the spill formulas. In the sample file, the ID heading is in cell C4, and the last note is in cell F11.
|10||10.1||10.2||Duplicate ID & data|
Before we look at Excel's new spill functions, let's see what happens with a "normal" worksheet formula.
In this example, there is a formula in cell C19, which refers to some of the sample data:
The formula results spill into the adjacent rows and columns, as far as needed, to show all 9 cells that the formula refers to
Here's another simple formula, using the OFFSET function, entered in cell C19:
This formula returns 3 rows and 3 columns, offset from cell C4, using the number of rows that is typed in cell I17.
The UNIQUE function makes a list of unique items. You can use multiple columns as criteria, eg unique rows where col A and col C are the same)
In the first example in this screenshot, this formula returns a single column of unique IDs from column C.
The second example returns unique combinations of ID and Data1 values, from columns C and D:
In the first example in the next screenshot, this formula returns unique combinations of ID and Data2 values, from columns C and E. Because the columns are not adjacent, the CHOOSE function is used, with an array of numbers.
The second example returns a list of IDs that are used only once in column C:
Use the SORT function to create a sorted copy of a range. The sort is based on one of the columns in the results.
In the first example, the optional column number argument is omitted, so the list is sorted by the first column in the results.
In the second example, the list is sorted by the second column, Data1.
In the SORT function, you can sort on two or more columns at once, and use the Sort Order argument, to sort in ascending or descending order. To do that, use arrays within the formula.
In this example, the list is sorted by ID, in ascending order (1), and then by Data1, in descending order (-1)
Use the SORTBY function to sort a range, based on values in another list. This is useful if you don't need the "sort" columns in the results.
In this screenshot, only the Data2 values (from column E) are in the formula results. The SORTBY function sorted the sample data by Data1 (column C, ascending ) and then by ID (column D, descending).
Use the FILTER function to select a set of rows that match a TRUE/FALSE test. This is essentially an array formula which doesn't need Ctrl Shift Enter pressed,
Note: The TRUE/FALSE test must have the same number of rows as the range
With this simple FILTER formula, the results show all rows where the value in the Data1 column is greater than100
With the FILTER function, you can have multiple tests, by multiplying them together. Multiplication works like AND, because Excel treats TRUE as 1 and FALSE as 0, so:
In the next example, the results show rows where: 1) the value in the Data1 column is greater than 100, AND, 2) the value in the Data2 column is greater than 110
In the FILTER function, you can also do multiple tests, for A OR B. Instead of multiplying, add the two tests together. If either test is true, the result will be true
In this formula, the results show rows where: 1) the value in the Data1 column is greater than 100, OR, 2) the value in the Data2 column is greater than 110
Note: You can also use the AND or OR functions to do multiple tests.
Use the SEQUENCE function to make a list of numbers. This function is most useful in combination with other functions. Arguments in square brackets are optional. Start and Step default to 1, if omitted.
In the first example below, this formula returns a sequence of numbers in 1 row and 5 columns.
In the second example, the result is a sequence of numbers in 2 rows and 3 columns, starting at 10, with a step of 5.
With this SEQUENCE formula, the result is a 4-week calendar, starting with the current date:
Note: The TEXT formula in the first heading cell is an old function, not one of the new "spill" functions. However, it is automatically treated as an array function, and spills across all 7 columns.
Use the RANDARRAY function to make a table of random numbers. Like SEQUENCE, this function is most useful in combination with other functions.
Arguments in square brackets are optional. Min defaults to 0, Max defaults to 1, and Integer defaults to False, if omitted.
=RANDARRAY(rows, cols, [min], [max], [integer])
In the first example below, this formula returns a table of random numbers, in 1 row and 5 columns.
In the second example, the result is a table of random numbers in 3 rows and 2 columns, with a min of -5, a max of 5, and numbers must be integers.
The spill functions can be combined with other spill functions, or with older functions, to create powerful formulas.
For example, this formula creates a list of unique values, sorted smallest to largest.
This formula puts a list of names in random order, based on the count of names. COUNTA is an old Excel function, used in combination with the new spill functions
If you need to refer to the spill range in another formula, don't use the spill range address. The results of spill formulas are in a dynamic range, so its range address could change at any time.
Instead, refer to the cell that contains the spill formula, and add the spill operator, #, at the end of that cell reference.
In the example shown below, there is a spill formula in cell C184, and the results are in 3 rows and 3 columns. In cell H184, this formula sorts the spill range results, because it refers to C184#
It seems any VBA function can return an array that can spill into neighbouring cells.
Copy the code for these functions, and paste it in regular code modules in your workbook. This video shows the steps.
This simple custom VBA function, SimpleArray, creates a little table and returns on the worksheet.
Here is the function being used on the worksheet, to create an array of 3 rows and 5 columns
And here is the code for the SimpleArray custom VBA function:
Function SimpleArray(x, y) Dim i, j, n ReDim A(x, y) For i = 1 To x For j = 1 To y n = n + 1 A(i, j) = n Next j Next i SimpleArray = A End Function
The Splitter function splits text using a delimiter, and the result is a row of cells. For example, this string of text will be split at the space characters.
=splitter("the quick brown fox"," ")
Here is the code for the Splitter custom VBA function:
Function Splitter(Txt$, Optional delim$ = ",") Splitter = Split(Txt, delim) End Function
This custom function, Ranking, returns a list of ranked numbers, and includes tie breaker numbers, unlike the worksheet RANK function.
Here is the code for the Ranking custom VBA function:
Function Ranking(rng As Range) Dim S, r, i, A r = rng SortLinkedCol r, S, 1 ReDim A(UBound(r), 1) For i = 1 To UBound(r) A(S(i), 1) = i Next i Ranking = A End Function
There are more custom functions in the sample workbook, so download the file to get the code for these functions:
--Compare 2 Lists
--Crosscheck 2 Lists
--Join cells or Lists
Spill formulas -- To see the worksheet and VBA spill formula examples, download the Spill Formula Examples workbook. The zipped file is in xlsm format, and contains macros. Thanks to Dermot Balson, who created these examples. (Microsoft 365)
Dermot Balson is a retired actuary, who spends time doing family research -- with spreadsheets, of course!
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: October 20, 2020 11:54 AM
Contextures RSS Feed