Dynamic arrays in Excel: FILTER, SORT, UNIQUE and SEQUENCE

For most of Excel’s history, one formula produced one answer in one cell. If you wanted a list of every customer in the North, sorted by spend, you filtered by hand, copied the result somewhere else, and did it again next week. Dynamic array functions changed that. One formula can now return a whole list that resizes itself when the data changes.

This guide covers the four functions most analysts reach for first, FILTER, SORT, UNIQUE and SEQUENCE, how spilling works, which versions of Excel support them, and the mistakes that produce the #SPILL! and #CALC! errors.

How spilling works

Microsoft’s page on dynamic array formulas and spilled array behaviour explains the core idea. When a formula returns several values, Excel places them in the neighbouring cells automatically. That output area is the spill range. Select any cell inside it and a border shows its extent.

Only the top-left cell holds the formula. The other cells show it greyed out in the formula bar, and to change the result you edit the top-left cell. If anything sits in the way, such as a typed value or another formula, Excel cannot spill and shows #SPILL!. Clear the blocking cells and the result appears.

To refer to a whole spill range, add a hash after the top-left cell. Microsoft’s page on the spilled range operator gives the example of =SEQUENCE(10) in A2 and =SUM(A2#) elsewhere. If the sequence grows to 20 rows, the SUM follows without any edit.

Two limits are worth knowing. Spilled formulas are not supported inside Excel tables, so place them in the grid beside a table and refer to the table with structured references. And links to dynamic arrays in another workbook only work while both workbooks are open; otherwise refreshed formulas return #REF!.

Version availability

This is the first thing to check before you build a shared file around these functions. Microsoft’s pages for FILTER, SORT, UNIQUE and SEQUENCE list them as available in Excel for Microsoft 365 (Windows and Mac), Excel 2024, Excel 2021, and the Excel apps for iPad, iPhone and Android. Excel 2019 and Excel 2016 do not appear on those lists, so colleagues on those versions will not be able to use the formulas. The spilled range operator page lists only Excel for Microsoft 365 on Windows and Mac.

If you send files to clients or suppliers, ask what they use. A dashboard that depends on FILTER is no use to someone on Excel 2016.

FILTER: return the rows that match

FILTER returns the rows of a range that meet a condition:

=FILTER(array, include, [if_empty])

The include argument is a true or false test with the same height (or width) as the array, such as C2:C500="North". For several conditions, multiply the tests for AND and add them for OR:

• AND: =FILTER(A2:D500,(C2:C500="North")*(D2:D500>1000),"None")
• OR: =FILTER(A2:D500,(C2:C500="North")+(C2:C500="East"),"None")

Always fill in the third argument. Without it, a filter that finds nothing returns #CALC!, because Excel does not support empty arrays. A message such as “None” or an empty string is clearer for the reader.

SORT and UNIQUE: tidy lists that stay current

SORT returns a sorted copy of a range: =SORT(array, [sort_index], [sort_order], [by_col]). By default it sorts ascending on the first column; use -1 for descending and a column number to sort on another column. Microsoft notes that SORTBY is often more flexible, because it sorts by a referenced range rather than a column number, so it survives columns being added or removed.

UNIQUE returns the distinct values in a range: =UNIQUE(array, [by_col], [exactly_once]). Set the third argument to TRUE and it returns only the values that appear once, which is a quick way to find one-off customers or products sold a single time. Wrapping one in the other is common: =SORT(UNIQUE(B2:B500)) gives an alphabetical list of every region or salesperson, and it updates when a new one appears in the data. That list makes a good source for a dropdown or the row labels of a summary table.

SEQUENCE: generate numbers and dates

SEQUENCE creates a list of numbers: =SEQUENCE(rows, [columns], [start], [step]). Any optional argument you leave out defaults to 1. =SEQUENCE(12) gives 1 to 12. Microsoft’s examples include a list of codes starting at 1001 and going up in steps of 1000, and a row of month names built by combining SEQUENCE with DATE and TEXT.

In practice, SEQUENCE is useful for building timelines. =DATE(2026,SEQUENCE(12),1) returns the first day of each month in 2026, which you can use as headers for a monthly forecast or as criteria for SUMIFS.

A worked example (illustration only)

A sales manager has an Excel table called Orders with columns Rep, Region, Date and Value. She wants a live summary sheet that shows each rep’s total and a list of large orders for the current region.

1. In A2: =SORT(UNIQUE(Orders[Rep])) spills an alphabetical list of reps.
2. In B2: =SUMIFS(Orders[Value],Orders[Rep],A2#) spills a total beside each rep, because A2# passes the whole list.
3. In E1 she types a region. In E3: =SORT(FILTER(Orders,(Orders[Region]=E1)*(Orders[Value]>=5000),"No large orders"),4,-1) lists that region’s orders of 5,000 or more, largest first.

When a new rep is hired and their orders appear in the table, the rep list, the totals and the filtered list all update on their own. Nothing needs to be copied down or extended.

Common mistakes and when not to use dynamic arrays

• Leaving data or notes below a formula, which causes #SPILL! the first time the list grows.
• Omitting if_empty in FILTER, so an empty result shows #CALC!.
• Referring to a spill with a fixed range such as A2:A20 instead of A2#.
• Putting a spilling formula inside an Excel table.
• Sharing the file with people on Excel 2019 or earlier.

Dynamic arrays suit live summaries, filtered views and lists that feed other formulas. They are less suitable when the output must be edited by hand, because you cannot type into a spill range. They also add calculation load on very large sheets, where Power Query or a PivotTable may do the job with less strain. And if the audience is on an older version, stick with SUMIFS, PivotTables and filters that everyone can open.