Dynamic Array Formulas: Excel's Most Powerful Modern Feature

The Spill Concept

Old Excel: One formula, one result.
New Excel: One formula, many results that “spill” down or across.

Enter =A1:A10*2 in a cell, and Excel fills 10 cells with the results. The spill range shows a blue border.

FILTER: Dynamic Filtering

=FILTER(array, include, [if_empty])

Example: =FILTER(A2:D100, C2:C100="Sales")

Returns all rows where column C equals “Sales.” Results spill automatically. When source data changes, filtered results update.

SORT: Dynamic Sorting

=SORT(array, [sort_index], [sort_order])

Example: =SORT(A2:D100, 3, -1)

Returns all data sorted by column 3 descending. Unlike Excel's Sort feature, this doesn't modify source data — it creates a sorted view.

UNIQUE: Extract Distinct Values

=UNIQUE(array)

Returns each unique value once. Perfect for creating dropdown lists or understanding what categories exist in your data.

SORTBY: Multi-Column Sorting

=SORTBY(array, by_array1, [sort_order1], ...)

Sort by any column without including it in output. Sort by multiple columns with different orders.

Combining Dynamic Arrays

The real power comes from nesting:

=SORT(FILTER(A2:D100, B2:B100="Active"), 4, -1)

Filter to active records, then sort by column 4 descending. One formula replaces what used to require helper columns and multiple steps.

Official Resources

• FILTER function — Microsoft's FILTER guide
• Dynamic arrays — understanding spill behavior