Robust Spreadsheets: Tables, Named Ranges, Dynamic Arrays | Excel & Power BI S1 Ep6

📂 Download the AtlasParts dataset, the Episode 6 starter / completed workbooks, and the robust-monthly-close template: https://github.com/GoCelesteAI/excel-powerbi-for-finance Episode 6 of *Excel & Power BI for Finance* — the Season 1 finale. Across the first five episodes we built the AtlasParts close: pulled from the ERP, read the GL, lookups, pivots, then Power Query for the cleanup. Every episode added something the workbook could *do*. This one is about how that workbook *survives*. The story this episode opens with is real. A senior analyst's monthly close model worked perfectly for three years. She left, her replacement opened it, and the formulas started returning #REF!. Two weeks of reverse engineering followed. The model worked for one person. It didn't survive the hand-off. Three tools fix the hand-off problem. **Tables** (the Ctrl+T kind) replace bare cell ranges with named, auto-expanding objects whose formulas read like English. **Named ranges** turn magic numbers and hard-coded dates into labelled, single-source-of-truth cells. **Dynamic arrays** — FILTER, SORT, UNIQUE, SEQUENCE — replace dragged-down formulas with output that auto-resizes as data changes. Together they turn a personal artifact into a hand-offable asset. What You'll Learn: - The five failure modes of brittle spreadsheets — hard-coded ranges, magic numbers, fragile sheet refs, manually-extended outputs, and untraceable dependencies - Tables (Ctrl+T) — convert a range to a Table, name it, use structured references like tblGL[Amount], the auto-expand behaviour, the Total Row, and how Tables integrate with pivots and Power Query - Named ranges — Define Name, scope (workbook vs sheet), naming constants like VATRate, parameters like ReportingPeriod, and the LET function for inline named values in modern formulas - Dynamic arrays — FILTER, SORT, UNIQUE, SEQUENCE, SORTBY, the spill range, and the # operator for referring to a spill range from another formula - A robust monthly close model — Tables for source data, named ranges for parameters, dynamic-array reports — that any new analyst can pick up on day one - Three takeaways: every range should be a Table, every magic number should be a named range, every multi-row output should be a dynamic-array formula Timestamps: 0:00 - Intro — the workbook that didn't survive the hand-off 0:26 - What's in this episode 1:02 - Five failure modes of brittle spreadsheets 2:23 - Pillar 1 · Tables (Ctrl+T) 4:13 - Pillar 2 · Named ranges 5:53 - Pillar 3 · Dynamic arrays 7:43 - A robust monthly close model 8:47 - Recap — three takeaways 9:28 - Up next · Season 2 · Power BI for Finance Key Takeaways: 1. Every data range you reference should be a Table. Bare ranges miss new rows, break on column inserts, and require manual maintenance. Tables grow with the data, integrate with pivots and Power Query, and turn formulas into structured references that read like English. Convert on contact — Ctrl+T. 2. Every magic number is a named range waiting to happen. If a formula has a literal number, a hard-coded date, or a hard-coded text constant — name it. The name documents the intent. The cell is the single source of truth. Use LET for one-off intermediate values. 3. Every multi-row output should be a dynamic-array formula, not a dragged formula. FILTER, SORT, UNIQUE, SEQUENCE produce output that resizes with the data. New rows extend the report, deleted rows shrink it, the count is computed automatically. 4. Workbook-scoped named ranges for parameters that drive the whole model: ReportingPeriod, BaseCurrency, VATRate. Sheet-scoped for sheet-local helpers. The named-range list IS the workbook's documentation. 5. Tables pair best with Power Query (Episode 5) — load to a Table or load from a Table, and the entire pipeline auto-refreshes when source data changes. Next: *Season 2 begins — Power BI for Finance*. Same AtlasParts close, but on a star-schema data model with DAX formulas, time intelligence, and a real dashboard layer for the data Excel can't comfortably hold. Taught by CelesteAI. #exceltutorial #excelforfinance #financeprofessionals #exceltips #structuredreferences #namedranges #dynamicarrays #FILTER #SORT #UNIQUE #excelandpowerbi #financialmodeling #closeprocess #atlasparts #financeexcel #robustspreadsheets