Excel & Power BI for Finance: Robust Spreadsheets: Tables, Named Ranges, Dynamic Arrays

Download the AtlasParts dataset and the Episode 6 starter / completed workbooks — follow along in your own copy of Excel: github.com/GoCelesteAI/excel-powerbi-for-finance.

There’s a finance team I worked with whose monthly close model lived on the senior analyst’s laptop. She’d built it three years earlier. It pulled from six source files, ran twenty SUMIFS, fed a P&L, and produced a board pack. It worked. Every month, on time, no errors.

She left for a new job. Her replacement opened the workbook and the formulas started returning #REF!. Half the totals showed wrong numbers. Some sheets had hard-coded ranges that needed extending. Others had numbers typed directly into formulas — a 0.21 here, a 0.07 there — that were the VAT rate and the freight rate, except neither was labelled. The replacement spent two weeks reverse-engineering it.

The model worked for one person. It didn’t survive the hand-off.

This episode is about the spreadsheet discipline that makes a workbook survive — robust to new data, robust to new readers, robust to next quarter when the rules change. Three tools do most of the work: Tables (the Ctrl+T kind), named ranges, and dynamic arrays. Each one solves a specific kind of brittleness. Together they’re the difference between a workbook that’s a personal artifact and a workbook that’s a hand-offable asset.

What “robust” actually means

Before the techniques, let’s name the failure modes.

Hard-coded ranges that don’t grow. =SUM(Revenue!B2:B100) worked when the sheet had 95 rows. Next month it has 1500. The formula returns the sum of the first 99 rows and silently misses the rest. Nobody notices because the number still looks plausible.

Magic numbers in formulas. =Sales!E5*0.21 is the VAT calculation, but the 0.21 lives only in that one cell. When VAT changes to 22%, you have to find every formula that references it. You’ll miss some.

Brittle sheet references. Someone inserts a column in a source sheet and =VLOOKUP(A2, Customers!A:F, 4, FALSE) now returns the wrong column. The lookup still runs without error. The numbers are just wrong.

Output that requires manual extension. A SUMIFS that breaks revenue into eleven products. Marketing adds a twelfth product. The report still shows eleven. Nobody knows the twelfth exists in the data.

Dependencies that aren’t traceable. Three sheets feed the P&L. A formula on the P&L sheet references Adjustments!H47. What is H47? Why is it there? The original author knew. The reader doesn’t.

Tables, named ranges, and dynamic arrays each fix one of these. Used together, they fix all of them.

Pillar 1: Tables (Ctrl+T)

A Table in Excel — the formal kind, not just “a range of cells with data in it” — is a structured object that knows where its boundaries are and grows when you add rows.

Select your data, press Ctrl+T, confirm the prompt about whether the table has headers, and Excel converts the range to a Table. The visual difference: banded rows, filter chevrons in the headers, and a Table Design ribbon tab when the Table is selected. The structural difference is much bigger.

The Table has a name. Excel auto-names it Table1, Table2, etc, but you should rename it on the spot under Table Design → Table Name to something meaningful: tblGL, tblCustomers, tblProducts. Now this Table is a first-class object you can refer to from anywhere in the workbook.

Formulas use structured references instead of cell ranges. Instead of =SUM(B2:B1500), you write =SUM(tblGL[Amount]). The reference reads like English — sum the Amount column of tblGL. The formula doesn’t care how many rows are in the Table. When new rows arrive, the reference still resolves to the entire column.

The Table auto-expands when you add data. Type a value in the row immediately below the Table and Excel extends the Table to include it. Any formula referencing the Table now sees the new row. No manual B2:B1500 → B2:B1501 editing.

Aggregations get a built-in Total Row. Table Design → Total Row adds a row at the bottom with a per-column dropdown for SUM, COUNT, AVERAGE, etc. The Total Row is part of the Table — when the Table grows, the Total moves down to stay at the bottom.

Tables integrate with everything else. Pivot tables built on a Table auto-refresh against the Table’s current data. Power Query (Episode 5) can load to a Table or load from a Table. Charts pointed at a Table auto-extend. The whole “I added rows and now I need to update everything” pain disappears.

For the AtlasParts GL, the upgrade is one keystroke. Select the GL data → Ctrl+T → name it tblGL. Every existing =SUMIFS(B2:B1500, ...) becomes =SUMIFS(tblGL[Amount], ...). Next month’s load adds 800 rows. Every report still works.

Pillar 2: Named ranges

A named range is a label for a cell, range, or formula. You define it once and reference the name from anywhere in the workbook.

Two ways to create one. Quickest: select the cell or range, type a name into the Name Box (left of the formula bar), press Enter. More structured: Formulas → Define Name, where you can set the name, scope (workbook-wide or just one sheet), and the reference formula.

Three things you’d actually name in a finance workbook:

Constants. A cell holding 0.21 named VATRate. Now every formula uses =Sales*VATRate instead of =Sales*0.21. When VAT changes you update one cell; every formula that mentions VATRate updates. The intent of the formula is now readable — Sales*VATRate is self-documenting in a way Sales*0.21 is not.

Parameters that drive the workbook. A ReportingPeriod cell on a control sheet holding the date 2025-04-30. Every aggregation that filters to this period references ReportingPeriod. To run last month’s close, you change one cell. Every report rebuilds.

Named formulas. Formulas → Define Name → Name: NetSales, Refers to: =tblOrders[Subtotal]-tblOrders[Discount]. Now =SUM(NetSales) works anywhere. The formula is defined once, named once, used everywhere — a function without writing VBA.

Scope matters. A workbook-scoped name is visible everywhere. A sheet-scoped name only resolves on the sheet it’s defined on. Use sheet-scoped names for sheet-local helpers (a calculation column’s intermediate value). Use workbook scope for parameters that should be the same across all sheets — like VATRate and ReportingPeriod.

For modern formulas, the LET function lets you name values inline without creating a workbook-level named range. =LET(rate, 0.21, sales, B2, sales*rate) reads top-to-bottom: rate is 0.21, sales is B2, the result is sales*rate. Use LET for one-off calculations that need readable intermediate names but don’t deserve a workbook-wide name.

The named-range upgrade for AtlasParts: a Parameters sheet with three labelled cells — ReportingPeriod, BaseCurrency, VATRate — each given a workbook-scope name. Every aggregation references the name. The workbook is now driven by three parameters that any new analyst can read and change without touching a single formula.

Pillar 3: Dynamic arrays

Dynamic arrays are the modern Excel formula model. A formula returns multiple values that spill into adjacent cells without you having to drag the formula down or use Ctrl+Shift+Enter array entry.

The headline functions:

FILTER(array, condition) — return the rows of array where condition is true. =FILTER(tblGL, tblGL[Account]=4000) returns every GL line for account 4000, spilling down into a block of cells. No manual count of rows required. The result grows and shrinks as the source data changes.

SORT(array, [sort_index], [sort_order]) — sort an array. =SORT(tblCustomers, 5, -1) sorts the customers Table by the fifth column descending. Pair with FILTER to get filtered-and-sorted output in one formula.

UNIQUE(array) — distinct values. =UNIQUE(tblGL[CustomerId]) returns every customer who appears in the GL, once each. Powerful when paired with COUNTIF for distinct counts.

SEQUENCE(rows, [columns], [start], [step]) — generate a list of numbers. =SEQUENCE(12, 1, 1, 1) produces 1 through 12 in a column — useful for month indexes, period numbers, scenario IDs.

SORTBY(array, by_array1, [order1], ...) — sort by one or more parallel arrays. Useful when the sort key isn’t in the data itself.

RANDARRAY, XMATCH, TOCOL, TEXTSPLIT — supporting cast for dynamic-array workflows.

The spill range. When a dynamic-array formula returns multiple values, the result spills into the cells below and to the right. The cell containing the formula is the spill anchor. The full spill range is referenced by the anchor cell with a # suffix: =B2# refers to the entire spill range starting at B2. If a formula in another cell needs to consume the FILTER result, point it at B2# and it auto-tracks the spill range size.

Dynamic arrays change how reports are built. Instead of dragging a formula down 1500 rows for every customer, write one FILTER that produces the entire customer list. When the data grows, the report auto-grows. When the data shrinks, the report shrinks. The output count is always correct because it’s computed, not maintained.

The dynamic-array upgrade for AtlasParts: a Top10Customers sheet whose entire content is two formulas. =SORT(tblCustomers, 6, -1) sorts customers by credit limit. Wrap that in =TAKE(SORT(tblCustomers, 6, -1), 10) to take just the top ten. Every month’s refresh now updates the top-ten list automatically.

A robust monthly close model

Let’s put the three pillars together. A monthly close model for AtlasParts has these moving parts:

1. Source data sheets — tblGL, tblCustomers, tblProducts, tblBudget. All Tables, named with the tbl prefix. Loaded via Power Query from CSV exports. Auto-grow on refresh.

2. Parameters sheet — five cells, each a workbook-scoped named range. ReportingPeriod (date), BaseCurrency (text), VATRate (number), MaterialityThreshold (number for variance flagging), BudgetYear (number).

3. Calculations — every formula references Tables and named ranges, not bare cell addresses. =SUMIFS(tblGL[Amount], tblGL[Account], 4000, tblGL[PostingDate], "<="&ReportingPeriod). Reads like a sentence; refreshes on a parameter change.

4. Reports — built on dynamic-array formulas. The P&L report is a FILTER + SORT against the GL Table. The variance schedule is a FILTER against accounts whose budget-vs-actual variance exceeds MaterialityThreshold. The reports auto-resize as the underlying data changes.

5. Documentation — a single README sheet listing every Table, every named range, every parameter, what each one means. The existence of the named ranges is the documentation; the README sheet just lists them with a one-line purpose.

Now hand this model to the new analyst. They open it. They see Tables with descriptive names. They see formulas that read like English. They see a Parameters sheet with five labelled cells. To run the close for a different period, they change ReportingPeriod. To accommodate a VAT rate change, they update VATRate. To handle next month’s GL export, they refresh — Power Query reloads the source CSV, the Tables grow, the formulas re-aggregate, the dynamic-array reports re-spill.

No two-week reverse engineering required.

Three takeaways

One: every range you reference should be a Table. Bare ranges are footguns. They miss new data, they break on column inserts, they require manual maintenance. Tables grow with the data, integrate with pivots and Power Query, and turn formulas into structured references that are self-documenting. Convert every data range to a Table on contact. Ctrl+T. One keystroke.

Two: every magic number is a named range waiting to happen. If a formula contains a literal number, a hard-coded date, or a hard-coded text constant, that’s a candidate for a named range. The name is the documentation. The single source of truth is the cell. Future-you and future-readers thank you.

Three: every multi-row output should be a dynamic-array formula, not a dragged formula. If you find yourself filling a formula down 1500 rows, write one FILTER or one SORT or one UNIQUE instead. The output count is then computed automatically. New data extends the report. Lost data shrinks it. Nothing manual.

These three rules, applied consistently, turn a personal spreadsheet into a hand-offable asset. They’re the discipline that makes the next analyst’s first day a productive one instead of a forensic one.

That’s the end of Season 1 — Excel for Finance. We’ve covered the ERP-to-Excel pipeline (Episode 1), GL extract literacy (Episode 2), lookups (Episode 3), pivots (Episode 4), Power Query (Episode 5), and now the spreadsheet discipline that holds it all together. Season 2 picks up where Excel runs out of room: Power BI for the same finance work — but on a star-schema data model, with DAX formulas, time intelligence, and a real dashboard layer.

See you in Season 2.