Excel Dynamic Arrays in Financial Models: Safely Retiring OFFSET, INDIRECT and CSE Formulas

Scenario: Monthly Forecast Model With Classic Formula Debt

You join a finance team maintaining a monthly forecast model for the next 36 months:

• Sheets
  • Drivers – assumptions (growth %, margins, capex, etc.) by month
  • Scenarios – Base / Downside / Upside driver sets
  • P&L, BalanceSheet, CashFlow – monthly statements
• Structure
  • Months in columns (e.g. column F onward)
  • Lines in rows (revenue, COGS, opex, etc.)

The pain points:

1. Scenario switching via INDIRECT
   In Drivers, each scenario is a named range (BaseDrivers, DownsideDrivers, etc.). A cell ScenSel holds the active scenario name. Formulas like:

   =INDIRECT(ScenSel & "_Revenue")
   

   pull the active scenario's revenue drivers.

2. Rolling ranges via OFFSET
   In P&L, revenue forecast uses a rolling sum of the last 3 months:

   =SUM(OFFSET(F7,0,MONTH(TODAY())-1,1,3))
   

   This is volatile and fragile when columns are inserted.

3. Array‑entered formulas (CSE)
   In several areas, matrix calculations are done with Ctrl+Shift+Enter, e.g. allocating overhead by driver proportions.

You want to:

• Keep the monthly column layout and general logic
• Remove volatility and reference fragility
• Make ranges explicit and traceable

Dynamic arrays (Microsoft 365 and Excel 2021+ with the modern calculation engine) give you the tools, but you need patterns that are safe in financial models.

Ground Rules: What Dynamic Arrays Actually Do

Before refactoring, align on behaviour.

Where Dynamic Arrays Work

Dynamic arrays are available in:

• Microsoft 365 Excel (Windows, Mac, web)
• Excel 2021 and later (perpetual license) on desktop

Key behaviours:

• A single formula can spill results into adjacent cells.
• Spill ranges adjust automatically when source data size changes.
• Many functions (e.g. FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP, XMATCH) are designed to return dynamic arrays.

Spill Mechanics

Important rules for financial models:

• A formula that returns multiple values spills down and/or right into empty cells.
• If any spill target cell is non‑empty, Excel shows #SPILL! and does not partially overwrite.
• Spill ranges are referenced with #, e.g. A1# refers to the entire spill range starting at A1.

This makes spill ranges predictable, but you must manage layout to avoid accidental overlaps.

Pattern 1: Replace INDIRECT Scenario Switching With Explicit Indexing

INDIRECT is volatile and breaks structured references, table resizing and refactoring. In our scenario, it's used to switch between scenario‑specific named ranges.

Problem Pattern

Example from Drivers:

=INDIRECT(ScenSel & "_Revenue")

Issues:

• Volatile: recalculates whenever anything changes.
• Text‑based: references are not updated by insert/delete operations.
• Opaque: trace precedents shows a text string, not the actual range.

Safer Structure for Scenarios

Instead of one named range per scenario, model scenarios in a single table.

1. Create a table tblDrivers on Drivers sheet with columns:

   • Scenario (text: "Base", "Downside", "Upside")
   • LineItem (e.g. "Revenue", "COGS")
   • Month (actual date or period code)
   • Value (numeric driver)

2. Put the active scenario in a cell ScenSel (data validation list: Base / Downside / Upside).

3. Use FILTER to spill the active scenario drivers:

=FILTER(tblDrivers[Value], tblDrivers[Scenario]=ScenSel)

This spills a vector of values for the selected scenario. To align with the monthly columns in P&L, filter by month as well.

Assume months in P&L are actual dates in row 5 (F5:AK5). In Drivers, add a Month column storing those same dates. Then:

=FILTER(tblDrivers[Value], (tblDrivers[Scenario]=ScenSel) * (tblDrivers[Month]=F$5))

This returns the driver for the active scenario and specific month. Copy across months; no INDIRECT, no volatility.

Pattern: Scenario Matrix + XLOOKUP

If you prefer a matrix layout:

• Rows: line items
• Columns: scenarios

Create a table tblScenarioDrivers:

In Drivers, with ScenSel holding the scenario name, use:

=XLOOKUP(
    "Revenue",
    tblScenarioDrivers[LineItem],
    INDEX(tblScenarioDrivers[[Base]:[Upside]], , XMATCH(ScenSel, tblScenarioDrivers[[#Headers],[Base]:[Upside]]))
)

Explanation:

• XMATCH finds the column position of the active scenario within the scenario columns.
• INDEX(tblScenarioDrivers[[Base]:[Upside]], , column_number) returns the entire scenario column.
• XLOOKUP pulls the row for "Revenue" from that column.

This is non‑volatile, fully traceable, and works cleanly with structured references.

Pattern 2: Replace OFFSET Rolling Windows With INDEX + SEQUENCE

OFFSET is another volatile function commonly used for rolling ranges (e.g. trailing 3 months). Dynamic arrays let you build those windows explicitly.

Problem Pattern

In P&L, a trailing 3‑month average revenue per month:

=AVERAGE(OFFSET(F7,0,MONTH(TODAY())-1,1,3))

Issues:

• Volatile due to OFFSET and TODAY.
• Assumes month columns align with calendar months.
• Hard to audit the exact range.

Safer Pattern With INDEX + SEQUENCE

Assume revenue row is 7, months are in columns F to AK, and you want trailing 3 months for each column.

In the first month where you have at least 3 months of history (e.g. column H for the 3rd month), use a dynamic array pattern:

1. Build relative column offsets with SEQUENCE.

   For a trailing 3‑month window ending at the current column:

   =SEQUENCE(3,1,-2,1)
   

   This returns {-2;-1;0} as a vertical array.

2. Use INDEX to pick the corresponding cells from the revenue row.

   In cell H8 (trailing 3‑month average for revenue row 7):

   =LET(
       RevenueRow, $F$7:$AK$7,
       Offsets, SEQUENCE(3,1,-2,1),
       CurrentPos, MATCH(H$5, $F$5:$AK$5, 0),
       Window, INDEX(RevenueRow, Offsets + CurrentPos),
       AVERAGE(Window)
   )
   

   Explanation:

   • RevenueRow is the full row of monthly revenue.
   • MATCH(H$5, $F$5:$AK$5, 0) returns the position of the current month within the header row.
   • Offsets + CurrentPos gives the positions of the 3‑month window.
   • INDEX(RevenueRow, positions) returns a 3‑element array.
   • AVERAGE(Window) computes the trailing average.

This formula is non‑volatile (no OFFSET, no TODAY) and explicit about the window. It also adapts if you insert months, because MATCH and structured ranges update.

Rolling Windows for Entire Rows With Spill

If you want the trailing 3‑month average for all P&L lines at once in a helper area, you can spill vertically and horizontally.

Assume P&L data is a table tblPL with:

• LineItem column
• Month columns (dates) F:AK

In a separate area, put:

=LET(
    Data, tblPL[[F]:[AK]],
    WindowSize, 3,
    EndPos, COLUMNS(Data),
    Offsets, SEQUENCE(WindowSize,1,-WindowSize+1,1),
    WindowIndices, EndPos + Offsets,
    RowIndex, SEQUENCE(ROWS(Data)),
    Window, INDEX(Data, RowIndex, TRANSPOSE(WindowIndices)),
    AVERAGE(Window)
)

This pattern:

• Uses SEQUENCE to construct column positions for the trailing window.
• Uses two‑dimensional INDEX to pull the window for all rows.
• Spills the averages for every line item.

Adjust EndPos if you want the window ending at a specific month rather than the last column.

Pattern 3: Replace CSE Matrix Calculations With Native Dynamic Arrays

Array‑entered formulas (Ctrl+Shift+Enter) are fragile because:

• Many users don't realise a range is controlled by one formula.
• Editing a single cell can silently break the array.
• Ranges don't resize automatically when source data changes.

Dynamic arrays remove the need for CSE in most cases.

Problem Pattern: Overhead Allocation

In P&L, overhead is allocated to business units based on driver proportions.

• OverheadTotal in cell B10
• Driver weights by unit in range B20:D20 (three units)
• Overhead allocation row B30:D30 with a CSE formula:

=B10 * (B20:D20 / SUM(B20:D20))

Array‑entered across B30:D30.

Dynamic Array Replacement

Move the formula to a single cell and let it spill.

In cell B30:

=LET(
    TotalOverhead, B10,
    Weights, B20:D20,
    Normalized, Weights / SUM(Weights),
    TotalOverhead * Normalized
)

This spills across B30:D30 automatically. If you add a fourth unit in column E:

• Extend Weights to B20:E20
• The spill range expands to B30:E30 without editing multiple cells.

Pattern: Matrix Multiplication With MMULT

For more complex allocations, you can combine dynamic arrays with MMULT.

Assume:

• Overhead categories in rows B10:B12 (3 categories)
• Business units in columns C9:E9 (3 units)
• Driver matrix (3x3) in C20:E22

You want a 3x3 allocation matrix: overhead categories × units.

In cell C30:

=LET(
    Overhead, B10:B12,
    Drivers, C20:E22,
    Ones, SEQUENCE(ROWS(Drivers),1,1,0),
    TotalDriversByUnit, MMULT(TRANSPOSE(Drivers), Ones),
    NormalizedDrivers, Drivers / TotalDriversByUnit,
    Allocation, MMULT(TRANSPOSE(Overhead), NormalizedDrivers),
    Allocation
)

Notes:

• SEQUENCE(ROWS(Drivers),1,1,0) creates a column vector of ones with the same row count as Drivers.
• MMULT(TRANSPOSE(Drivers), Ones) returns total driver per unit (1x3 vector).
• Drivers / TotalDriversByUnit normalises drivers per unit.
• MMULT(TRANSPOSE(Overhead), NormalizedDrivers) returns the 3x3 allocation matrix.

This spills from C30, no CSE required. Be mindful of matrix dimensions; MMULT requires compatible sizes.

Pattern 4: Making Dynamic Arrays Safe in Column‑Based Financial Models

Dynamic arrays can create layout conflicts if you don't plan for spill ranges. In a column‑based financial model, you want dynamic arrays to be explicit and contained.

Use Helper Blocks for Spills

Rather than spilling directly into your primary statement area:

• Create helper blocks on the same sheet or a dedicated calc sheet.
• Let dynamic arrays spill there.
• Reference the spill ranges in your main P&L/BS/CF via # or INDEX.

Example: scenario‑specific revenue drivers.

On Drivers, in a helper area starting at G10:

=FILTER(tblDrivers, tblDrivers[Scenario]=ScenSel)

This spills the active scenario rows. In P&L, reference:

=XLOOKUP(
    F$5,
    INDEX(Drivers!G10#, , MATCH("Month", Drivers!G9#, 0)),
    INDEX(Drivers!G10#, , MATCH("Value", Drivers!G9#, 0))
)

(Adjust header row reference and column labels to your actual layout or use structured references.)

This keeps spills away from your statement layout while still leveraging dynamic behaviour.

Guard Against #SPILL! in Shared Models

In team environments, someone will eventually type into a spill target.

Mitigation patterns:

• Put spill ranges in protected areas (sheet protection with unlocked input cells elsewhere).
• Use named ranges for spill outputs and reference those names in downstream formulas rather than the raw cell addresses.

Example:

• Name the cell with the FILTER formula ActiveScenarioDrivers.
• Use ActiveScenarioDrivers# in downstream formulas.

If #SPILL! appears, it's easier to trace back to the named spill source.

Avoid Volatile Functions With Dynamic Arrays

Dynamic arrays don't automatically remove volatility. The functions themselves matter.

For financial models:

• Prefer XLOOKUP, FILTER, INDEX, XMATCH, SEQUENCE, CHOOSECOLS, CHOSEROWS.
• Avoid OFFSET, INDIRECT, CELL, INFO, NOW, TODAY in core calc paths unless you genuinely need them.

If you need rolling windows by period, use explicit period indices or dates rather than MONTH(TODAY()) so recalculation isn't tied to system date.

Pattern 5: Transition Strategy in a Live Model

You rarely get to rebuild from scratch. A safe transition plan matters more than using every new function.

1. Inventory Volatile and CSE Areas

Use:

• Formulas tab → Error Checking → Circular References (to ensure none first).
• Formulas → Show Formulas to visually scan for OFFSET, INDIRECT.
• Find & Select → Find for INDIRECT(, OFFSET(, { (array formulas show braces in the formula bar).

List:

• Location (sheet, address)
• Purpose (scenario switch, rolling window, allocation)
• Dependencies (named ranges, tables)

2. Convert One Pattern at a Time

Start with:

1. Scenario switching (INDIRECT)
   • Move to tables + FILTER/XLOOKUP.
2. Rolling windows (OFFSET)
   • Move to INDEX + SEQUENCE.
3. CSE ranges
   • Move to spill formulas using LET for clarity.

Validate outputs against the old formulas before deleting them.

3. Document Spill Ranges

For each dynamic array block, add a short note next to it:

• What it does
• Which sheet/range consumes it

In shared models this prevents “helpful” edits that break spills.

One Practical Takeaway

Pick one live model and replace a single INDIRECT‑based scenario switch with a FILTER/XLOOKUP table pattern. Once you see that dynamic arrays can make the logic more explicit and less volatile without changing your sheet layout, the rest of the OFFSET and CSE clean‑up becomes a straightforward refactor instead of a risky rebuild.