What are Dynamic Arrays?
Introduced in Excel 365 and Excel 2021, Dynamic Arrays allow a single formula to return multiple results that automatically spill into adjacent cells. This eliminates the need for complex array formulas entered with Ctrl+Shift+Enter, and makes many previously difficult tasks straightforward.
The key functions in the Dynamic Array family are: FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY, and XLOOKUP.
FILTER: The most powerful of the set
FILTER returns only the rows from a range that meet a condition — without pivot tables, without helper columns, without VBA.
=FILTER(A2:D100, C2:C100="Closed", "No results")This returns every row where column C equals "Closed". The third argument is what to show if nothing matches.
Combining multiple conditions
=FILTER(A2:D100, (C2:C100="Closed") * (B2:B100="London"))Multiply conditions for AND logic. Use + for OR logic. This replaces dozens of manual filter steps in dashboards and reports.
SORT and SORTBY
SORT returns a sorted version of a range — dynamically, so it updates whenever the source data changes.
=SORT(A2:B50, 2, -1)This sorts the range by column 2 in descending order. SORTBY allows sorting by a column that isn't in the output range — useful for ranked dashboards and leaderboards.
UNIQUE: Instant distinct value lists
UNIQUE extracts a list of distinct values from a column — replacing complex pivot-based approaches or manual deduplication.
=UNIQUE(B2:B200)Combine with SORT for a sorted distinct list. Combine with COUNTA to count unique items dynamically:
=COUNTA(UNIQUE(B2:B200))SEQUENCE: Generating number and date series
SEQUENCE generates arrays of sequential numbers. It's particularly useful for building dynamic calendars, rolling date ranges, and numbered lists.
=SEQUENCE(12, 1, DATE(2025,1,1), 30)This produces 12 dates starting January 1 2025, each 30 days apart — a rolling monthly calendar in one formula.
Combining functions: The real power
The real leverage comes from nesting these functions together. For example — a dynamically filtered, sorted, unique list of locations with open incidents:
=SORT(UNIQUE(FILTER(C2:C200, D2:D200="Open")))This replaces what used to require a pivot table, a helper column, and a manual refresh step.
Practical tip: Use Dynamic Arrays as the data source for named ranges, which then feed into charts and dashboard elements. Everything updates automatically when the source data changes — no more manual refreshes.
What this means for organisations
Dynamic Arrays dramatically reduce the complexity of building live dashboards in Excel. Reports that previously required VBA or Power Query can often be rebuilt as a handful of formula-driven sheets that any analyst can maintain. The result: faster reporting cycles, fewer errors, and dashboards that stay current without manual intervention.
Conclusion
If your organisation is still on Excel 2019 or earlier, upgrading to Microsoft 365 unlocks this entire function family. The productivity gain is substantial — these aren't incremental improvements, they're a fundamentally different way of working with data in Excel.