The Ultimate Guide to Functions in Excel: From Basic to Advanced

The essential formulas — SUM, IF, VLOOKUP and the rest — cover daily work, and you should be fluent in them first. But Excel 365 introduced a genuinely new generation of functions that changed what a single formula can do: instead of writing one formula and copying it down a thousand rows, you write one formula that spills across as many cells as the answer needs, and recalculates the whole range live. These are the dynamic array functions, and they’re the real “advanced” layer worth learning. Note up front: they require Microsoft 365 or Excel 2021 and later.

The spill: one formula, many cells

The idea underneath everything here is “spilling.” A formula that returns multiple values writes them into the neighboring cells automatically, no copying required.

=B2:B100 * 1.1     'one formula, spills a whole column of results

Type that in one cell and Excel fills the entire range below it with each value times 1.1 — a blue border outlines the “spill range.” You write one formula; it occupies as many cells as it needs. Reference the whole spilled result elsewhere with the spill operator: =SUM(D2#) totals everything D2 spilled, however many rows that turns out to be. This is the mental shift — from “a formula per cell” to “a formula per answer.” Microsoft’s guide to dynamic arrays and spilling explains the behavior in full.

FILTER: extract matching rows, live

FILTER is the function that changes how you work. It returns just the rows meeting a condition — spilled into place, updating the instant the data changes.

=FILTER(A2:C100, B2:B100="North")

This returns every row where column B is “North,” all three columns, as a live block. Where you’d once have applied a filter, copied the visible rows, and pasted them elsewhere — redoing it whenever data changed — FILTER does it in one self-updating formula. Add a North row to the source and it appears in the result automatically. Wrap a second condition with * for AND or + for OR, and you have a query engine in a single cell. It’s the dynamic-array function you’ll reach for most.

SORT and UNIQUE: views that maintain themselves

Two more do jobs you used to do by hand, except they redo themselves whenever the data shifts.

=SORT(A2:C100, 3, -1)    'sort by the 3rd column, descending
=UNIQUE(B2:B100)         'a distinct list of column B's values

SORT returns a sorted copy that re-sorts the moment the underlying numbers change — no more re-running Data → Sort after every edit. UNIQUE returns a deduplicated list that grows and shrinks with the source, which makes it perfect for feeding a dropdown or building a summary without a pivot table. A live list of unique customers, always current, is one formula. These functions turn manual, repeat-every-time chores into set-it-once results. Point a data-validation dropdown at a UNIQUE spill range (using the # operator) and you get a dropdown whose options expand on their own as new categories appear in the data — a self-maintaining picklist that used to require a macro.

Combine them into a mini report engine

The real power shows when you nest them — each takes an array and returns one, so they stack cleanly.

=SORT(FILTER(A2:C100, C2:C100>1000), 3, -1)

Read it inside-out: FILTER pulls the rows over 1000, SORT orders that result by the third column descending. One formula, and you’ve got a live “top deals, biggest first” report that updates with the data — no pivot, no manual sort, no copy-paste. Layer UNIQUE in too — =SORT(UNIQUE(FILTER(...))) — and you’re composing genuinely sophisticated output from three readable pieces. This composability is what makes dynamic arrays feel less like formulas and more like a small query language living in a cell.

SEQUENCE and the generators

Some dynamic-array functions create data rather than transform it. SEQUENCE is the handy one.

=SEQUENCE(12)            'spills 1 through 12 down a column
=SEQUENCE(1, 7, 0, 1)   'a row: 0,1,2,3,4,5,6

SEQUENCE generates a run of numbers — useful for building date series, numbered lists, or feeding other formulas that need a counter. Paired with a date start, =start+SEQUENCE(30)-1 spills a month of consecutive dates in one cell. It’s a small function, but it removes the tedium of typing or dragging sequences, and it shines as a building block inside larger dynamic-array formulas that need a range to operate over.

LET and LAMBDA: name things, build your own

The newest additions make complex formulas readable and even let you create your own functions. LET names intermediate values so a formula stops repeating itself.

=LET(rate, 0.1, base, B2, base + base*rate)
=LAMBDA(x, x*1.1)        'a reusable function, named in Name Manager

LET assigns names (here rate and base) you can reuse within the formula — both faster, since Excel computes each once, and far more readable than repeating B2 five times. LAMBDA goes further: define your own function once, name it in the Name Manager, and call it like a built-in throughout the workbook. Together they’re how power users tame formulas that would otherwise be unmaintainable monsters — naming the parts and packaging the logic. They’re advanced, but even LET alone makes any long formula dramatically easier to read and fix later.

The #SPILL error and how to clear it

Sooner or later a dynamic-array formula will return #SPILL! instead of results. It’s not a broken formula — it’s Excel telling you something is in the way.

What it means. A spill range has to be empty for the formula to fill it. If any cell where the result would land already holds a value, Excel can’t spill and shows #SPILL! instead. The fix is almost always to clear the cells below and to the right of the formula. Click the error’s dropdown and “Select Obstructing Cells” to see exactly what’s blocking it.

The other common cause is a merged cell sitting in the spill path — dynamic arrays and merged cells don’t mix, so unmerge anything in the way. Once you understand that a spilled formula needs room to grow into, #SPILL! stops being mysterious and becomes a two-second fix: make space, and the results appear. It’s the one new error class dynamic arrays introduce, and it’s far friendlier than it first looks.

From the essentials to the modern toolkit

Tip. Don’t jump here first. Get comfortable with the essential formulas and XLOOKUP (itself a dynamic-array function), then add FILTER and UNIQUE when you catch yourself filtering and copying by hand. That’s the moment dynamic arrays click — when they replace a chore you’re tired of repeating.

Dynamic arrays are the biggest change to Excel formulas in years: one formula that spills, filters, sorts, and dedupes a whole range and keeps it live. Start with the spill concept, learn FILTER and UNIQUE for the everyday wins, nest them when you need a quick report, and reach for LET and LAMBDA when a formula grows complex enough to deserve names. You don’t need all of them at once — but each one you add removes a manual, repeat-it-every-time task from your week, which is exactly what separates the modern Excel toolkit from the classic one. Just remember they need Excel 365 or 2021, so a workbook shared with older versions will show a #SPILL or compatibility error instead.