Excel Spill Formula Examples

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 calculation examples.

NOTE: Spill functions (dynamic arrays) are available in Microsoft 365 plans.

Intro: Spill formulas

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.

Sample Spill Data

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.

ID	Data1	Data2	Note
10	10.1	10.2	Some note
12	12.1	12.2	Another note
11	11.1	11.2	Test note
13	13.1	13.2	Sample
10	100.1	100.2	Duplicate ID
12	120.1	120.2	Duplicate ID
10	10.1	10.2	Duplicate ID & data

Spill Range

Before we look at Excel's new spill functions, let's see what happens with a "normal" worksheet formula.

Simple Range Reference

In this example, there is a formula in cell C19, which refers to some of the sample data:

• =C6:E8

The formula results spill into the adjacent rows and columns, as far as needed, to show all 9 cells that the formula refers to

• If you select any cell in the formula results, a thin blue border appears around the spill range
• The formula can only be edited in cell C19, where it was entered
• If a spill cell is selected, the formula can be seen in the Formula bar, in grey font, but it cannot be edited

OFFSET Function

Here's another simple formula, using the OFFSET function, entered in cell C19:

• =OFFSET(C4,I17,0,3,3)

This formula returns 3 rows and 3 columns, offset from cell C4, using the number of rows that is typed in cell I17.

UNIQUE Function

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)

• =UNIQUE(range, [sort by col=TRUE,sort by row=FALSE], [only items occurring once=TRUE, all unique values=FALSE])

In the first example in this screenshot, this formula returns a single column of unique IDs from column C.

• =UNIQUE(C5:C11)

The second example returns unique combinations of ID and Data1 values, from columns C and D:

• =UNIQUE(C5:D11)

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.

• =UNIQUE(CHOOSE({1,2},C5:C11,E5:E11))

The second example returns a list of IDs that are used only once in column C:

• =UNIQUE(C5:C11,,TRUE)

SORT Function

Use the SORT function to create a sorted copy of a range. The sort is based on one of the columns in the results.

• =SORT(range, [col number to sort on], [sort order 1=asc -1=Desc], [sort by col=TRUE,sort by row=FALSE)

Sort by One Column

In the first example, the optional column number argument is omitted, so the list is sorted by the first column in the results.

• =SORT(C5:E11)

In the second example, the list is sorted by the second column, Data1.

• =SORT(C5:E11,2)

Sort by Two Columns

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)

• =SORT(C5:E11,{1,2},{1,-1})

SORTBY Function

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).

=SORTBY(E5:E11,C5:C11,-1,D5:D11,-1)

FILTER Function

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,

• =FILTER(range, true/false test, value to show if no results)

Note: The TRUE/FALSE test must have the same number of rows as the range

Simple Filter

With this simple FILTER formula, the results show all rows where the value in the Data1 column is greater than100

=FILTER(C5:E11,D5:D11>100,"no matches")

Multiple Filter Tests - AND

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:

• TRUE x TRUE = 1 (ie TRUE)
• any other combination = 0 (ie FALSE)

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

• =FILTER(C5:E11,(D5:D11>100) * (E5:E11>110),"no matches")

Multiple Filter Tests - OR

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

• =FILTER(C5:E11, (D5:D11>100) + (E5:E11>110), "no matches")

Note: You can also use the AND or OR functions to do multiple tests.

SEQUENCE Function

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.

• =SEQUENCE(rows, columns, [start], [step])

In the first example below, this formula returns a sequence of numbers in 1 row and 5 columns.

• =SEQUENCE(1,5)

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.

• =SEQUENCE(2,3,10,5)

With this SEQUENCE formula, the result is a 4-week calendar, starting with the current date:

=SEQUENCE(4,7,TODAY())

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.

• =TEXT(J102:P102,"DDD")

RANDARRAY Function

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.

• =RANDARRAY(1,5)

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.

• =RANDARRAY(3,2,-5,5,TRUE)

Function Combinations

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.

• =SORT(UNIQUE(C5:C11))

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

• =SORTBY(C169:C174, RANDARRAY(COUNTA(C169:C174)))

Referencing Array Results

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#

• =SORT(C184#)

Custom VBA Functions

It seems any VBA function can return an array that can spill into neighbouring cells.

• NOTE: VBA functions will not update unless one of the parameters provided to the function changes. They can be forced to update every time Excel calculates, if the line Application.Volatile is put at the top of the VBA function code

Copy the code for these functions, and paste it in regular code modules in your workbook. This video shows the steps.

Create an Array

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

• =simplearray(3,5)

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

Split String Into Cells

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

Rank

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

More Custom Functions

There are more custom functions in the sample workbook, so get the file to get the code for these functions:

--SQL Query

--Compare 2 Lists

--Crosscheck 2 Lists

--Join cells or Lists

--Regular Expression

--Formula Counter

Get the Sample Files

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)

About the Developer

Dermot Balson is a retired actuary, who spends time doing family research -- with spreadsheets, of course!

More Functions Tutorials

Functions List

FILTER Function Examples

FILTER Function Reports

FILTER Function Lookup

Dynamic Drop Down Lists

Interactive FILTER Example

Named Excel Tables