Expert guide

Excel FILTER, SORT, UNIQUE and Dynamic Arrays

Live lists and mini-reports from one formula, without copying, pasting or refreshing

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: dynamic arrays let one formula return a whole list that spills into the cells below. Use =FILTER(data, condition, "None") for live subsets, SORT/SORTBY to order them, UNIQUE for distinct lists, the E2# reference to point at a spilled result, and VSTACK, TAKE and GROUPBY for stacking and summarising. Leave space for spills and keep them outside Excel Tables. Available in Microsoft 365 and Excel 2021+. Our Excel consultants use them to replace fragile copy-paste reports.

A lot of manual Excel work is making lists: overdue invoices for the collections call, this week's jobs for one technician, distinct customers who bought a product, the top ten sellers this month. Before dynamic arrays these needed filters, copy and paste, or complicated formulas. Now each is a single formula that updates the moment the data changes.

FILTER: Live Subsets

Overdue invoices:
=FILTER(tblInvoices, (tblInvoices[DueDate] < TODAY()) * (tblInvoices[Balance] > 0),
        "Nothing overdue")

One technician's jobs this week (name in B1):
=FILTER(tblJobs[[JobNo]:[Address]],
        (tblJobs[Tech] = B1) *
        (tblJobs[Date] >= TODAY() - WEEKDAY(TODAY(), 2) + 1) *
        (tblJobs[Date] <  TODAY() - WEEKDAY(TODAY(), 2) + 8),
        "No jobs")

OR conditions use +:
=FILTER(tblOrders, (tblOrders[Region] = "West") + (tblOrders[Region] = "North"))

Multiply conditions for AND, add them for OR. tblJobs[[JobNo]:[Address]] returns only the columns between those two, so the output is report-ready.

SORT and SORTBY

Overdue invoices, largest balance first (balance is the 5th column):
=SORT(FILTER(tblInvoices, tblInvoices[Balance] > 0), 5, -1)

Sort by a column you are not returning:
=SORTBY(tblCustomers[Name], tblCustomers[Revenue], -1)

Top 10:
=TAKE(SORT(FILTER(tblSales[[Product]:[Revenue]], tblSales[Month] = H1), 2, -1), 10)

UNIQUE: Distinct Lists

Distinct customers who bought product P-220:
=SORT(UNIQUE(FILTER(tblOrders[Customer], tblOrders[Product] = "P-220")))

Count of distinct customers:
=COUNTA(UNIQUE(tblOrders[Customer]))

Customers who appear only once (one-time buyers):
=UNIQUE(tblOrders[Customer], , TRUE)

The Spill Reference (#)

If E2 contains =SORT(UNIQUE(tblOrders[Region])), then E2# means "the whole spilled list". Build a summary next to it that grows automatically:

F2:  =SUMIFS(tblOrders[Amount], tblOrders[Region], E2#)
G2:  =COUNTIFS(tblOrders[Region], E2#)

Add a new region to the data and the list, totals and counts all extend by one row. Use =E2# as a data validation source to get a drop-down list that maintains itself.

Newer Functions Worth Knowing

FunctionDoesExample use
VSTACK / HSTACKStack arrays vertically or horizontallyCombine three branch tables into one list
TAKE / DROPKeep or remove first/last rows or columnsTop 10, or remove a header row
CHOOSECOLS / CHOOSEROWSPick specific columns or rowsReorder output columns
TOCOL / TOROWFlatten a range to one column or rowTurn a grid of names into a list
GROUPBY / PIVOTBYFormula-based grouping and pivotingSales by region and month without a PivotTable
SEQUENCEGenerate a series of numbers or datesCalendar of the next 30 days
Combine three branch tables and sort by date:
=SORT(VSTACK(tblNorth, tblSouth, tblWest), 1, 1)

Sales by region (Microsoft 365):
=GROUPBY(tblOrders[Region], tblOrders[Amount], SUM)

Errors and How to Avoid Them

ErrorCauseFix
#SPILL!Something is in the spill range, or merged cellsClear the area below/right; unmerge
#SPILL! inside a TableSpilling formulas cannot live inside Excel TablesPut the formula outside the Table
#CALC!FILTER found nothing and no if_emptyAdd the third argument
#NAME?Excel version lacks the functionUse Microsoft 365 / 2021+, or older formulas
Results look wrong after sorting the sourceHard-coded row references elsewhereUse Table references and #

Designing Sheets for Spills

  • Put each spilling formula at the top of an empty area with nothing below it.

  • Keep source data in Excel Tables so new rows are included automatically.

  • Format the spill area with conditional formatting rather than fixed cell formatting, so formatting follows the data.

  • Label the output clearly; users cannot edit individual spilled cells and may be confused.

Dynamic arrays pair naturally with XLOOKUP and LAMBDA; see XLOOKUP vs INDEX MATCH and the LAMBDA guide. For dashboards that combine these with charts and slicers, see Excel dashboards and BI reporting.

A Complete Example: Self-Updating Collections Sheet

Here is how a finance team replaced a weekly copy-and-paste routine with four formulas on one sheet. The source is an Excel Table named tblAR with Customer, InvoiceNo, DueDate, Balance and Owner columns.

B1:  owner dropdown (data validation on =SORT(UNIQUE(tblAR[Owner])))

A4:  =SORT(FILTER(tblAR[[Customer]:[Balance]],
            (tblAR[Owner] = B1) * (tblAR[Balance] > 0) * (tblAR[DueDate] < TODAY()),
            "Nothing overdue"), 4, -1)

G4:  =SORT(UNIQUE(CHOOSECOLS(A4#, 1)))                     distinct customers
H4:  =SUMIFS(tblAR[Balance], tblAR[Customer], G4#,
             tblAR[Owner], B1, tblAR[DueDate], "<" & TODAY())  overdue per customer
H1:  =SUM(CHOOSECOLS(A4#, 4))                              total overdue for this owner

Each collector picks their name and sees their overdue invoices, largest first, a per-customer summary and a total, all updating as payments are posted. Nobody copies anything, and the sheet never goes stale.

FAQ

Dynamic arrays FAQ

FILTER, SORT, UNIQUE and spill errors

Dynamic array formulas return several values that automatically spill into neighbouring cells. Functions such as FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, VSTACK and TAKE produce live lists and tables from one formula, which update when the source data changes.

Microsoft 365, Excel 2021 and later, and Excel for the web. Excel 2019 and earlier show #NAME? for these functions. Newer functions such as VSTACK, HSTACK, TAKE, DROP, CHOOSECOLS, GROUPBY and PIVOTBY need Microsoft 365 or recent versions.

Use the spill range operator: if a formula in E2 spills, E2# refers to the whole result, however many rows it has. For example, =COUNTA(E2#) counts the items in a UNIQUE list, and charts or data validation can point at E2#.

FILTER returns #CALC! when no rows match and you did not provide the if_empty argument. Add a third argument such as "No matches" to show a friendly message instead.

For many simple summaries, yes: UNIQUE plus SUMIFS, or GROUPBY and PIVOTBY in Microsoft 365, give formula-driven summaries that update instantly without refreshing. PivotTables remain better for interactive exploration with slicers and drill-down.