Excel for actuaries · Intermediate · ⏱ 15 min
Dynamic arrays: FILTER, UNIQUE, SEQUENCE & SUMPRODUCT
The four array functions that replace half your manual work: filtering claim lists, deduplicating policies, generating schedules, and conditional math without helper columns.
Old Excel: one formula, one cell. Dynamic arrays: one formula, a whole spilling range. This is the biggest upgrade to Excel in twenty years, and most study manuals never mention it.
FILTER: a query in one cell
=FILTER(A2:F500, C2:C500="Open", "None")Give it a claims table and a condition; it returns every open claim, live, updating as the data changes. Stack conditions with * (AND) or + (OR):
=FILTER(A2:F500, (C2:C500="Open")*(E2:E500>50000))Open claims over $50k, no pivot table, no manual sort, no stale copy-paste extract.
UNIQUE, instant deduplication
=UNIQUE(B2:B500)Every distinct policy number, once. Wrap it for a sorted report header: =SORT(UNIQUE(B2:B500)). Combined with FILTER, you’ve just built a live summary page.
SEQUENCE, schedules from thin air
=SEQUENCE(26, 1, DATE(2026,5,4), 7)Twenty-six weekly dates starting May 4th, which is an entire study calendar in one formula. Anchor it to a cell holding your exam date and every week regenerates the moment that date moves.
SUMPRODUCT: conditional math, any Excel version
The pre-dynamic classic that still runs everywhere:
=SUMPRODUCT((C2:C500="Open")*(D2:D500))Reads as: for each row, 1 if open else 0, times the amount: summed. Multi-condition sums, weighted averages, counting with criteria. All one pattern. It’s also a quiet exam skill: expected values are SUMPRODUCTs of outcomes and probabilities.
Put it together
A one-sheet claims dashboard: UNIQUE for the status list, FILTER for the drill-down, SUMPRODUCT for the totals by category. Zero helper columns, updates itself. Build it once and you’ll never hand-filter again.