# Advanced Excel: Dynamic Arrays, LET, LAMBDA, and Power Query

You already trust Excel with real work. You write lookups, you build pivot tables, and you know the quiet dread of a report that has to be rebuilt by hand every month because the export changed shape again. This guide is for that moment. Modern Excel has a second layer on top of the grid: formulas that return whole tables, a way to name the pieces of a formula, and a cleanup tool that remembers every step so next month costs one click.

Everything runs on one thread: a messy real-world export that gets cleaned, reshaped, and analysed. You meet the same four sales reps and the same three regions in every phase, so each new tool fixes a problem you have already felt.

## Prerequisite

You should be comfortable with lookups, conditional sums, and pivot tables. If any of that is shaky, start with [Excel From Zero](/guides/excel-from-zero), then [Excel Formulas That Do Real Work](/guides/excel-formulas-that-do-real-work) and [Excel Pivot Tables and Charts](/guides/excel-pivot-tables-and-charts).

Version note, checked against Microsoft's documentation in October 2026: FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, XMATCH, and LET work in Excel for Microsoft 365, Excel 2024, and Excel 2021. LAMBDA, BYROW, and MAP are documented for Microsoft 365 and Excel 2024 (not 2021). Power Query ships as Get & Transform on the Data tab in the same recent versions. Each phase repeats the version note where it matters.

## How to read this

- **Fighting a `#SPILL!` or a stray `@` right now?** Jump to [Phase 1](01-dynamic-arrays-and-spilling.md).
- **Rebuilding the same cleanup every month?** Go straight to [Phase 3](03-power-query-repeatable-cleanup.md).
- **Want it to finally make sense?** Read in order. Each phase builds on the last.

## The phases

1. **[Dynamic Arrays and Spilling](01-dynamic-arrays-and-spilling.md)** - formulas that return whole tables, the spill range, `#SPILL!`, the `#` and `@` operators, and FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, and XMATCH.
2. **[LET and LAMBDA: Readable and Reusable Formulas](02-let-and-lambda.md)** - name the pieces of a formula, then package a formula as your own function.
3. **[Power Query: Repeatable Cleanup](03-power-query-repeatable-cleanup.md)** - import a messy CSV, unpivot it, merge in a lookup table, and refresh with one click.
4. **[Guardrails: Data Validation and Conditional Formatting with Formulas](04-validation-and-conditional-formatting.md)** - stop bad data at the door and make problems visible.
5. **[Knowing When to Leave Excel](05-when-to-leave-excel.md)** - the row limit, the Data Model, and when Power BI, SQL, or Python is the better tool.

> Deferred on purpose: VBA and Office Scripts, Power Pivot measures in depth (see [Power BI DAX Deep Dive](/guides/power-bi-dax-deep-dive) for the language they share), and newer array helpers that are not yet in every version.