Dynamic Arrays

💡 Key concepts

• Spill behavior: One formula returns many cells. #SPILL! means something is blocking the output range.
• Spill reference: =COUNTA(E2#) refers to the whole spilled range starting at E2.
• Core functions:
  • FILTER(array, include, [if_empty]), with multiple criteria using * (AND) and + (OR)
  • SORT, SORTBY (sort by a column you don't display)
  • UNIQUE (distinct lists for dropdowns)
  • SEQUENCE (build month lists: =EOMONTH(start, SEQUENCE(12,1,0)))
  • CHOOSECOLS, CHOOSEROWS, TAKE, DROP (shape outputs)
  • VSTACK, HSTACK (append tables from several sheets)
  • GROUPBY, PIVOTBY (formula-based pivots in current Microsoft 365. Check availability in your tenant.)
• Dynamic arrays vs. Tables: Spilled formulas can't live inside a Table. Put them on a report sheet that references Tables.

🛠 Hands-on exercise: Live "Watch List"

Data: milestones (with Status and SlipDays from 1.1), risks (RiskID, ProgramID, Title, Likelihood, Consequence, Status, MitigationDueDate).

1. =SORT(FILTER(Milestones[[ProgramID]:[SlipDays]], (Milestones[Status]="Late")+(Milestones[Status]="At Risk"), "None"), 0, -1). Adjust the sort column index to SlipDays.
2. Make a dropdown of portfolios with =UNIQUE(Programs[Portfolio]) and a data-validation list pointing to =H2#.
3. Filter the watch list by the selected portfolio (add a criterion combined with *).
4. Risk exposure: =TAKE(SORTBY(FILTER(Risks[[RiskID]:[Title]], Risks[Status]<>"Closed"), FILTER(Risks[Likelihood]*Risks[Consequence], Risks[Status]<>"Closed"), -1), 10) gives the top 10 open risks by score.
5. Use SEQUENCE and EOMONTH to build a 12-month FY2026 header (Oct 2025–Sep 2026).

📦 Mini-project: One-Formula Portfolio Summary

On a blank sheet, use GROUPBY (or UNIQUE + SUMIFS if GROUPBY isn't available) to summarize budget_execution by Program × Appropriation for FY2026: PlannedObligation, ActualObligation, and obligation rate %. Add a portfolio dropdown that filters everything. No PivotTables allowed.