Day 15: Array Formulas & Dynamic Arrays

An array formula performs multiple calculations and returns multiple results (or a single result from multiple operations) in one formula. In older Excel (pre-2019), array formulas require pressing Ctrl+Shift+Enter to confirm — they appear with curly braces {} around them. In Excel 365, most array operations are handled automatically by dynamic array functions. Example: =SUM(IF(Region="North",Revenue,0)) as an array formula sums revenue only for North — equivalent to SUMIF but more flexible.

FILTER (Excel 365) extracts rows from a range that meet a condition and spills the results into adjacent cells automatically. =FILTER(A2:D100, B2:B100="North") returns all rows where column B is "North". Multiple conditions: =FILTER(A2:D100, (B2:B100="North")*(C2:C100="Electronics")). If no rows match, returns an error — add a default: =FILTER(range, condition, "No data found"). FILTER replaces complex Advanced Filter operations and is dynamic — updates automatically when source data changes.

UNIQUE (Excel 365) returns a list of unique values from a range, removing duplicates. =UNIQUE(A2:A100) returns each distinct value once. =UNIQUE(A2:A100,,TRUE) returns only values that appear exactly once (not values with duplicates). Commonly paired with other functions: =SORT(UNIQUE(A2:A100)) returns a sorted unique list. =COUNTA(UNIQUE(A2:A100)) counts distinct values. Replaces complex Remove Duplicates + manual unique count operations.

SORT(array, [sort_index], [sort_order]) returns a sorted version of a range as a spill. =SORT(A2:B100, 2, -1) sorts by column 2 descending. SORTBY sorts by a column outside the range: =SORTBY(A2:C100, D2:D100, -1) sorts the A:C range by values in column D (descending). Neither modifies the source data — the result spills into a new area. Useful for creating dynamic ranked lists that update as data changes.

When a dynamic array formula spills results into multiple cells, you can reference the entire spill range using the # sign after the first cell. If =UNIQUE(A2:A100) is in cell F2 and spills into F2:F15, you can reference all results as F2#. This means formulas that consume the output (like =COUNTA(F2#)) automatically adjust as the spill range grows or shrinks with the data. This is how you build chained dynamic array formulas.