Excel Spill Range Operator (#): What N2# Means

The # was introduced with dynamic arrays in Excel build 1809, released January 2020 with Microsoft 365. It's part of the same feature set as XLOOKUP, FILTER, UNIQUE, and SORT.

What Generates a Spill Range

Any formula that returns more than one value creates a spill range. The relevant ones in FP&A models:

• FILTER - returns a subset of rows matching criteria
• UNIQUE - returns deduplicated values (useful for building category lists)
• SEQUENCE - returns a list of numbers or dates
• SORT / SORTBY - returns a reordered array
• XLOOKUP when the return range is multi-row

When any of these land in a cell, Excel writes the results into adjacent cells automatically. The originating cell (N2) becomes the anchor. The operator N2# resolves to the full spilled range, whatever size that happens to be at calculation time.

How N2# Resolves at Runtime

According to Microsoft's official dynamic array documentation, N2# "refers to the entire spilled range… and is dynamic in nature." Concretely: if N2 contains =FILTER('P&L'!$A:$A, 'P&L'!$B:$B="Actuals") and that returns 8 quarters, N2# resolves to N2:N9. If next quarter you add a 9th period, the FILTER grows and N2# automatically resolves to N2:N10.

That automatic resize is the whole point. A SUMIFS formula like:

=SUMIFS('P&L'!$E:$E, 'P&L'!$A:$A, Returns!N2#)

...stays correct whether you're running a 6-quarter or 12-quarter model without touching the formula. In a bank syndicate DCF with 8+ projection years and multiple scenario tabs, this removes an entire class of maintenance errors.

3 Failure Modes Worth Knowing

1. Non-array anchor. If N2 holds a regular value or a single-cell formula, N2# resolves to just N2. No error - it silently returns a 1-cell range. This is the dangerous one: your SUMIFS looks fine, passes audit, and is quietly wrong. Always verify the anchor has a blue spill border in Excel before trusting downstream # references.

2. Blocked spill. If another value sits anywhere in the spill zone, the originating formula returns #SPILL! and N2# inherits that error. The fix is clearing the spill zone. The full #SPILL! debugging guide covers the non-obvious cases.

3. Cross-workbook references. As of Excel version 2404, Microsoft states that "References to spill ranges in closed workbooks are not supported." If your model pulls N2# from an external file, that workbook must be open or the reference fails. For multi-file board pack setups, consolidate the spill formulas into the same workbook or use Power Query to import the data before referencing it.

Spill References vs. OFFSET() and Named Ranges

Before dynamic arrays, the standard pattern for a dynamic range was OFFSET combined with COUNTA:

=OFFSET('Returns'!$N$2, 0, 0, COUNTA('Returns'!$N:$N)-1, 1)

It works. It's also volatile - Excel recalculates it on every change anywhere in the workbook, not just when the underlying data changes. On models with 40,000 cells and 12 OFFSET-based named ranges, that compounds into 8-12 seconds of recalculation time per edit. Charles Williams at FastExcel has benchmarked OFFSET and INDIRECT at 3-8x slower than equivalent non-volatile formulas on large models.

The # operator is non-volatile. It recalculates only when the anchor formula recalculates. Swapping OFFSET-based dynamic ranges for # references typically cuts recalculation time by 50-60% on models of that size.

Named ranges solve the naming problem but not the dynamic sizing problem - you still have to manually update the range bounds when data grows. Spill references handle both.

Replicating the # Pattern in Google Sheets

Google Sheets has no # operator. Typing N2# gives you a parse error. But the underlying need - consuming a FILTER or UNIQUE output without hardcoding a range - is entirely solvable. The structure is different.

The key shift: in Excel, you store the FILTER result in one cell and reference that cell's spill with # from anywhere. In Sheets, you embed the FILTER directly inside the consuming formula, or use BYROW to iterate over it row by row.

Pattern 1: BYROW + LAMBDA (closest structural equivalent)

This is the right pattern when you need per-row results - the same output structure you'd get from using N2# as criteria in an Excel XLOOKUP or SUMIFS.

Scenario: your Returns tab needs contribution margin by period, where the period list comes from filtering Assumptions for actuals rows only.

=BYROW(
  FILTER(Assumptions!$A$3:$A$50, Assumptions!$B$3:$B$50="Actuals"),
  LAMBDA(period,
    SUMIFS('P&L'!$E:$E, 'P&L'!$A:$A, period, 'P&L'!$C:$C, "Gross Margin")
  )
)

BYROW feeds each row from the FILTER into the LAMBDA and runs SUMIFS for that period. Result: a column of margin figures, one per actuals period, auto-sized. Add a 9th period to Assumptions and the output grows to 9 rows without touching the formula.

BYROW + LAMBDA shipped in Google Sheets in October 2022. Any model built in the last 3 years has access to it.

Pattern 2: SUMPRODUCT + SUMIF (when you want a single total)

If you need the total across all periods the FILTER returns - equivalent to =SUM(SUMIFS(..., N2#)) in Excel:

=SUMPRODUCT(
  SUMIF(
    'P&L'!$A:$A,
    FILTER(Assumptions!$A$3:$A$50, Assumptions!$B$3:$B$50="Actuals"),
    'P&L'!$E:$E
  )
)

SUMIF with an array of criteria returns an array of sums - one per criterion. SUMPRODUCT collapses that to a single number. For a DCF model pulling $4.2M in revenue across 8 actuals periods, this returns the correct total without specifying which rows to sum. This pattern predates BYROW by years and works across all Sheets versions.

Pattern 3: OFFSET + COUNTA (when you need an actual range reference)

Some contexts require a real range reference rather than an inline array - chart data sources are the main case. Here OFFSET with COUNTA is the only option:

=OFFSET(Returns!$N$2, 0, 0, COUNTA(FILTER(Assumptions!$A$3:$A$50, Assumptions!$B$3:$B$50="Actuals")), 1)

Yes, it's volatile, with the same recalculation penalty as any OFFSET. Use it only where an inline array won't work. For everything computational, BYROW or SUMPRODUCT + SUMIF is faster.

Comparison: Excel # vs. Google Sheets equivalents

The Sheets approach produces longer formulas because you're embedding what Excel stores separately. Some analysts prefer that - all the logic is visible in one place rather than split across a spill anchor cell and downstream # references.

Building Models That Use Spill References Well

A few patterns that hold up in production:

Anchor in a dedicated column. Keep FILTER, UNIQUE, and SEQUENCE formulas in a single lookup column. Reference those with # elsewhere. Predictable anchor locations prevent accidental blocking and make audits faster.

Check anchor type before signing off. When reviewing someone else's model, any # reference is worth confirming. Select the anchor cell and look for the blue spill border. No border means the formula returns a scalar and N2# is silently pointing at one cell instead of the full range.

Watch for zero-gap data assumptions. A FILTER-produced period list used as SUMIFS criteria works correctly. A SEQUENCE-produced date list with missing dates in the source returns zeros for the gaps, not errors - which passes a spot check and fails a thorough audit.

If you're building in Google Sheets and want help wiring BYROW + LAMBDA or SUMPRODUCT + SUMIF patterns across a multi-tab model without explaining the formula logic from scratch each time, ModelMonkey understands this kind of cross-tab structure and can generate or debug these patterns in plain language.