Why does referencing a cell inside a merged range return 0 instead of the value?

You're referencing a non-anchor cell. Excel stores the merged cell's value only in the top-left cell of the merge. All other cells in the merge return empty string in text contexts and 0 in numeric ones - no error, just silence. Click the cell that visually shows the value and check the Name Box at the top-left of Excel. That address is the anchor. Reference that cell directly.

What's the difference between Merge & Center and Center Across Selection?

Merge & Center physically combines cells into a single unit, destroying the individual cell addresses. Center Across Selection leaves every cell intact but centers the display text across the selected range - visually identical in almost every case. For financial models, always use Center Across Selection. It won't block spill formulas, won't corrupt VLOOKUP or INDEX/MATCH results, and won't cause the silent-zero reference problem. Find it under Format Cells (Ctrl+1) → Alignment → Horizontal → Center Across Selection.

Can I use INDIRECT to work around merged cell reference problems?

No. INDIRECT evaluates the cell reference you pass it literally. If you point it at a non-anchor cell inside a merged range, you still get 0 or empty - INDIRECT doesn't see the visual merge, it evaluates the cell at that address. The fix is always to identify the anchor cell and reference it directly.

My FILTER formula worked until my dataset grew - now it shows #SPILL!?

The output range expanded into a merged cell further down the sheet. FILTER returns a variable number of rows depending on how many records match your criteria, so the spill zone grows as data changes. Audit the column below your formula for merged cells and unmerge them. If the output length is genuinely unpredictable, wrap with INDEX and SEQUENCE to cap it at a fixed row count: `=INDEX(FILTER(...), SEQUENCE(100, 6))` limits output to 100 rows regardless of what FILTER returns.

Does Google Sheets have the same merged cell conflict with array formulas?

Google Sheets uses ARRAYFORMULA rather than native spill, but the interaction with merged cells is similar - ARRAYFORMULA output can be blocked or corrupted by merged cells in the target range. The same separation-of-layers principle applies: run array formulas in an unmerged calculation zone, reference the results in a formatted output section. The specific error messages differ (Google Sheets won't show #SPILL!), but the structural failure mode is the same.

Excel Merged Cells and Spill Formulas: The Real Fix

The same problem surfaces in reverse: reference a non-anchor cell inside a merged range and you silently get 0 or empty string instead of the value you expected. That's the error that survives QA, prints in the board pack, and gets noticed by your CFO.

As of May 2026, this behavior hasn't changed across any Excel 365 or Excel 2021 build. It's not a bug that's getting patched.

Why Merged Cell References Return Zero

When you merge A1:C1, the value lives only in A1 - the top-left anchor cell. B1 and C1 still exist as distinct cells, but they return empty string in text contexts and 0 in numeric ones. Excel hides the emptiness visually but doesn't propagate the value.

// Merged cell A1:C1 contains "FY2025 Revenue"
=A1   // Returns "FY2025 Revenue" - correct
=B1   // Returns "" or 0 - silent failure, not an error

This kills any formula that navigates by column position into a merged header row. If your P&L has quarterly headers merged across 3 columns each ("Q1 2025" spanning C3:E3), cross-tab references hit the anchor or the ghost cells depending on which column they land on:

// SUMIFS pulling quarterly actuals from a formatted P&L
=SUMIFS('P&L'!C:C, 'P&L'!B:B, ">=" & Assumptions!$B$3, 'P&L'!B:B, "<=" & Assumptions!$B$4)
// Returns correct value if C is the merge anchor

=SUMIFS('P&L'!D:D, 'P&L'!B:B, ">=" & Assumptions!$B$3, 'P&L'!B:B, "<=" & Assumptions!$B$4)
// Returns 0 if D is inside the C3:E3 merge - silent, not flagged as an error

SUMIFS returns 0, not #VALUE! or #REF!. It looks like a legitimate zero in your model.

Why Merged Cells Trigger #SPILL!

Excel's dynamic array functions calculate their full output range at formula entry, then write the results into that projected block of cells. Microsoft's documentation is explicit: one of the named #SPILL! causes is "The spill range has merged cells." A single merged cell anywhere in the projected output range fails the entire formula.

This shows up in 3 specific FP&A patterns:

Dynamic period headers with SEQUENCE:

=SEQUENCE(1, 8, DATE(2024,1,1), 30)

If the output range C1:J1 has merged cells left over from a previous template version, this fails. The formula is fine. The range isn't.

Multi-column XLOOKUP into a formatted output section:

=XLOOKUP(Assumptions!$B$3, 'Revenue'!$A:$A, 'Revenue'!$C:$H)

This returns a 6-column array. If any cell in the destination range is merged, #SPILL!.

FILTER with variable-length output:

=FILTER('Transactions'!$A:$F, 'Transactions'!$E:$E="Enterprise", "No data")

The result set grows and shrinks as data changes. One merged cell 40 rows down - maybe a section break from a formatted template - stops the whole formula the moment the output reaches it.

Fix 1: Replace Merge & Center with Center Across Selection

This is the right fix for any model you own. Center Across Selection looks identical to Merge & Center - same visual centering across columns - but doesn't actually merge anything. Each cell keeps its individual identity. Spill formulas pass through without issue, cross-tab references work correctly, and INDEX/MATCH finds values in every cell.

To apply it: select the header cells, open Format Cells (Ctrl+1), go to the Alignment tab, and change the Horizontal dropdown from "Center" to "Center Across Selection."

The limitation: Center Across Selection only works horizontally. Vertical label spanning (a category header merged across rows) requires a different approach, covered below.

For any model you're building now, never use Merge & Center for column headers. The visual result is indistinguishable and you avoid every problem in this article.

Fix 2: Reference the Anchor Cell When You Can't Unmerge

If you're pulling data from a source you don't control - a file from the bank syndicate, a corporate template, a system export - unmerging may not be an option.

The rule: always reference the top-left cell of a merged range. If "Q1 2025" spans C3:E3, reference C3. If you're not sure where the anchor is, click into the cell that displays the value and check the Name Box (the address field at the top-left of Excel). That address is your anchor.

For vertically merged label columns - a P&L with "Operating Expenses" merged across rows 22 through 28 - VLOOKUP and INDEX/MATCH see only row 22 as having a value. Rows 23-28 look empty to a formula even though they display the label visually. If you need to classify rows against those merged categories, the cells aren't actually populated and matching fails.

The workaround is a helper column:

// Helper column D, with merged labels in column C
// D2: =IF(C2<>"", C2, D1)
// Fill this down through the dataset

This propagates the visible label down through the apparent merge, giving you matchable values in each row. Reference column D in your lookups instead of column C.

Fix 3: Audit and Clear the Spill Zone

When you're dropping a dynamic formula into an inherited template, audit the output range before entering the formula. Select the projected spill area, run Go To Special (F5 → Special), and check for merged cells. Merged cells behave differently in range selection - they don't highlight the same way as truly blank cells.

Unmerge the output area, apply Center Across Selection where you need visual formatting, then enter the formula.

One scenario where this gets complicated: FILTER with variable output length. If the result set grows over time - more transactions, more segments - the spill range expands into cells you didn't audit. Either keep a long unmerged buffer below the formula, or wrap with INDEX to cap the output at a fixed row count:

=INDEX(FILTER('Transactions'!$A:$F, 'Transactions'!$E:$E="Enterprise"), SEQUENCE(50,6))

This returns a fixed 50-row by 6-column array regardless of how many rows FILTER returns. The spill range is predictable and you can clear it once.

The Architecture Problem Behind This

The root cause is that Excel uses the same grid for presentation (where merged cells look clean) and calculation (where they cause structural failures). Models built for board packs or investor review often have merged headers, colored bands, and formatted sections that print well but become fragile the moment someone builds formulas on top of them.

The cleaner approach: separate your calculation layer from your presentation layer. Run SUMIFS, FILTER, and XLOOKUP formulas in a clean, unmerged data section. Reference those results in a formatted output section where merged headers and visual styling live. The dynamic formulas never touch the formatted zone.

// Data section - clean, unmerged, no formatting
// C5: EBITDA pull from P&L
=SUMIFS('P&L'!$C:$C, 'P&L'!$B:$B, "EBITDA")
// C6: Apply multiple from Assumptions tab
=C5 * Assumptions!$B$8   // 14.2x entry multiple → $47.4M implied EV

// Presentation section - merged headers, color-coded, board-ready
// J14 (inside merged cell J14:L14): just reference the clean cell
='Data'!C5

The presentation section can be as formatted as the CFO wants. The calculation layer stays clean. When someone asks for a sensitivity on the EBITDA multiple or a new segment cut, the calculation layer handles it without touching the formatted output.

ModelMonkey can scan your active sheet and flag merged cells that will conflict with spill formulas before you spend time debugging - particularly useful when you inherit a model that's been through 3 analysts and has formatting decisions nobody remembers making. Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.