Tutorials›Excel›Dynamic Arrays & Spill Functions
🟢 Free Demo
Excel TutorialTopic 21 of 31

Dynamic Arrays & Spill Functions

Use FILTER, UNIQUE, SORT, SEQUENCE and XLOOKUP to write one formula that returns multiple results automatically.

✅ What You Will Learn

›What a spill range is and how dynamic arrays work
›How to filter a table with FILTER() using one or more conditions
›How to extract unique values from a list with UNIQUE()
›How to sort data dynamically with SORT() and SORTBY()
›How to generate number sequences with SEQUENCE()

Dynamic array functions, introduced in Microsoft 365 and Excel 2021, fundamentally changed how Excel handles formulas. A single formula can now return a range of results that automatically "spills" into adjacent cells — no Ctrl+Shift+Enter, no helper columns, no copying down.

Before dynamic arrays, extracting unique values from a list required complex formulas or a Pivot Table. Now =UNIQUE(A2:A100) does it in one keystroke. Filtering a table for rows matching a condition used to require Advanced Filter or SUMPRODUCT tricks — now =FILTER(A2:D100, C2:C100="North") returns the matching rows instantly.

The # (hash) operator references a spill range. If UNIQUE() in B2 spills to B2:B8, you can reference the entire output as B2#. This makes chaining functions together elegant and powerful.

📋 Sales data — input for dynamic array formulas

Order IDRegionProductAmount (₹)Status
O-101NorthLaptop45000Delivered
O-102SouthMobile12000Delivered
O-103NorthTablet28000Pending
O-104EastLaptop52000Delivered
O-105SouthLaptop49000Cancelled
O-106NorthMobile15000Delivered

Syntax

EXCEL SYNTAX
=FILTER(array, include, [if_empty])
  include: a TRUE/FALSE array matching array rows
  =FILTER(A2:D100, C2:C100="North")         → rows where Region=North
  =FILTER(A2:D100, (C2:C100="North")*(D2:D100>10000))  → two conditions

=UNIQUE(array, [by_col], [exactly_once])
  =UNIQUE(B2:B100)       → deduplicated list

=SORT(array, [sort_index], [sort_order], [by_col])
  =SORT(A2:B50, 2, -1)   → sort by column 2, descending

=SORTBY(array, by_array1, sort_order1, ...)
  =SORTBY(A2:C50, C2:C50, -1)   → sort by column C descending

=SEQUENCE(rows, [cols], [start], [step])
  =SEQUENCE(10)          → 1 to 10 vertically
  =SEQUENCE(1, 12, 1)    → 1 to 12 horizontally (months)

Examples

Example 1FILTER — extract all delivered North orders in one formula
=FILTER(A2:E7, (B2:B7="North")*(E2:E7="Delivered"), "No results")
OUTPUT
O-101 | North | Laptop | 45000 | Delivered
O-106 | North | Mobile | 15000 | Delivered
💡

Multiplying two conditions (*) works like AND — both must be true. Use + for OR logic. The third argument "No results" displays when nothing matches.

Example 2UNIQUE + SORT — sorted list of distinct regions
=SORT(UNIQUE(B2:B7))
OUTPUT
East
North
South
💡

Functions chain naturally. UNIQUE removes duplicates, SORT orders the result alphabetically. The output spills automatically — no need to select a range first.

Example 3SEQUENCE — generate a numbered list or date series
=SEQUENCE(12, 1, DATE(2026,1,1), 30)   → 12 dates 30 days apart
=SEQUENCE(5, 4)                         → 5×4 grid of numbers 1–20
OUTPUT
44927, 44957, 44987 ...   (serial numbers for Jan 1, Jan 31, Mar 2 ...)
💡

Format the SEQUENCE output as dates (Ctrl+1 → Date). Combine with TEXT(SEQUENCE(...),"MMM-YY") for month labels in dashboard headers.

📌 Key Points to Remember

  • ✓Dynamic array functions require Microsoft 365 or Excel 2021 — not available in Excel 2016/2019
  • ✓Spill range: if a formula result occupies B2:B10, reference it all as B2#
  • ✓If anything blocks the spill range (non-empty cell), Excel shows a #SPILL! error — clear the blocking cells
  • ✓Combine FILTER + SORT + UNIQUE to replace complex Pivot Table setups with a single formula chain
  • ✓XLOOKUP (covered in VLOOKUP & XLOOKUP topic) is also a dynamic array function in 365

🏢 Real-World Application

E-commerce analysts use FILTER to instantly extract open orders, high-value transactions, or cancelled items from a master order table — without filtering the sheet and without Pivot Tables. HR teams use UNIQUE to generate headcount lists by department dynamically. These formulas update automatically when new rows are added to the source table.

⚠️ Common Mistakes to Avoid

WRONGUsing dynamic array functions in Excel 2016 — #NAME? error
FIXCheck your Excel version: File → Account → About Excel. Dynamic arrays require Excel 365 or Excel 2021. For older versions, use SUMPRODUCT or helper column approaches instead.
WRONGGetting #SPILL! error when pasting a dynamic array formula
FIXAnother cell is blocking the spill range. Find and clear it by clicking the formula cell and looking at the blue spill border to see where it wants to expand.
WRONGHardcoding the output range instead of using the spill reference (#)
FIXReference spill output as B2# not B2:B20. If the list grows beyond 20 rows, B2:B20 misses the extras. B2# always refers to the entire spill output.
✏️Test Yourself

What does =UNIQUE({"Delhi","Mumbai","Delhi","Noida","Mumbai"}) return?

❓ Frequently Asked Questions

Can I use FILTER inside a Pivot Table?

No — Pivot Tables have their own filtering system. Use FILTER on regular table data outside Pivot Tables. For interactive filtering with Slicers, Pivot Tables are still the better choice.

How do I count the results of a FILTER formula?

Wrap in ROWS: =ROWS(FILTER(A2:A100, B2:B100="North")). This counts how many rows matched the filter condition.

Does SORT work on text alphabetically?

Yes — SORT(range, 1, 1) sorts column 1 ascending (A→Z for text, smallest→largest for numbers). Use -1 for descending.

✏️ Practice Exercise

Build a live order dashboard using dynamic arrays. Source data: 30 order rows with Region, Category, Amount, Status. Use: (1) FILTER to show only "Pending" orders above ₹20,000, (2) UNIQUE+SORT to list distinct regions, (3) SEQUENCE+TEXT to generate a 6-month header row (Jan-2026 through Jun-2026), (4) SORTBY to rank the data by Amount descending.

← PreviousExcel ShortcutsNext →Statistical Functions
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.