This is a rundown of the non-obvious behaviors: the ones that don't show up in the tooltip.
How Cross-Tab Reference Shifting Actually Works
You know the basics. Relative references shift as you drag; absolute references ($) stay fixed. What catches people in real models is that this rule applies to the source tab too — not just the cell you're filling into.
Take this formula in your Returns Analysis tab, row 5:
='P&L'!C5 * Assumptions!$B$3
Drag it down to row 40. You get 'P&L'!C6, 'P&L'!C7, and so on — the row reference on the P&L tab shifts in sync with your destination row. That's usually what you want.
But if your P&L and Returns Analysis tabs don't share the same row layout — say, P&L has a blank row at row 12 for visual spacing — you've now wired up mismatched rows. Period 8 in your Returns Analysis is pulling Period 9 revenue from P&L. The formula looks right. The number is plausible. It fails the cross-tab tie-out by $340K and nobody catches it until the board pack is already out.
The fix is explicit anchoring on the row you don't want to shift:
='P&L'!$C5 * Assumptions!$B$3
Column locked, row relative. Drag down and the row shifts; drag across and the column stays on C.
The Double-Click Shortcut and Its Blank-Row Trap
Double-clicking the fill handle fills down to the last row of the adjacent column — specifically, it stops at the last contiguous row in the column immediately to the left (or right, if the left is empty). In a 60-period model where column B has your date headers through row 64, double-click gets you there without dragging. Fast.
The problem: if your adjacent column has a single blank cell anywhere in the range, double-click stops at the blank. Board pack templates with blank rows for visual spacing are a common source of this. You double-click, get formulas through row 22, and the remaining 42 periods are empty. No error, no warning.
Check adjacent columns for blanks before trusting double-click fill. If your template has deliberate blank rows, drag manually or select the target range first and use Ctrl+D.
Ctrl+D vs Fill Handle: Filtered Range Behavior
These aren't interchangeable on filtered data.
The fill handle fills all cells in the dragged range, including rows hidden by a filter or group. Ctrl+D fills only the selected visible cells.
In a model where you're applying a discretionary % override to 8 out of 30 cost centers — with the other 22 filtered out — dragging the fill handle over the visible range propagates the override to all 30 rows. Ctrl+D on the same selection hits only the 8 you can see.
For any formula fill on a filtered range, Ctrl+D is the right tool. For contiguous, fully-visible ranges, fill handle is fine.
Google Sheets vs Excel: Where They Diverge
Both apps share the core mechanic, but as of May 2026 there are meaningful differences in behavior worth knowing.
Series detection. Excel is more aggressive. Type "Q1 FY25" in one cell and "Q2 FY25" in the next, select both, drag — Excel correctly fills Q3 FY25, Q4 FY25, and so on. Google Sheets does this for standard date formats but handles fewer custom patterns. If your model uses non-standard period labels, test before committing.
Dynamic arrays vs fill handle. Excel 365 introduced spill ranges — a formula like =SEQUENCE(60) spills results into 60 cells automatically, no fill handle needed. Google Sheets has ARRAYFORMULA for similar behavior. In both cases, once you're using dynamic/array output, fill handle on that column can break the spill range by placing static values on top of it. According to Microsoft's documentation, "if a spill range is blocked, Excel will display a #SPILL! error" — easy to miss in a model where that column is a few tabs away from your primary view.
Excel 2019 and earlier. If you're working in an enterprise environment that hasn't rolled to 365 (more common than people admit), dynamic arrays don't exist. You're relying on fill handle or Ctrl+D for everything. In that case, the anchoring discipline covered above is more important, not less.
A SUMIFS Example Worth Auditing
A formula you'd actually use in a quarterly board pack:
=SUMIFS('P&L'!$C:$C, 'P&L'!$B:$B, ">=" & Assumptions!$B$3, 'P&L'!$A:$A, 'Returns'!A5)
The 'Returns'!A5 reference is intentionally relative — it shifts as you drag down, picking up each entity or period. The P&L column references and the Assumptions cell are fully anchored. This is correct, but only if you've consciously decided each anchor before dragging.
Fill handle this without auditing $ placement first, and you may be shifting the Assumptions!$B$3 reference (if you forgot a $) across 24 periods, each pulling a different discount rate assumption. The enterprise value goes from $47.3M to $51.8M with no formula error, just wrong inputs.
Auditing Fill-Handle Errors After the Fact
Fill-handle corruption is hard to catch in review because every cell in the filled range looks structurally correct. The formula bar shows you one cell at a time.
In Excel: Ctrl+` (backtick) toggles formula view. Scan the row references on your cross-tab formulas as you move down the filled range. Look for shifts in source-tab row references that shouldn't be shifting.
In Google Sheets, there's no direct formula view toggle. Use a FORMULATEXT() audit column:
=FORMULATEXT(C5)
This renders each formula as text in a parallel column, letting you scan for unexpected reference shifts without clicking cell by cell.
For both apps: a SUMCHECK row that independently validates each column total is the fastest way to catch fill-handle corruption before it reaches review. If your DCF column sums don't reconcile to your cross-tab rollup, a reference shift is the first thing to check.
When to Skip Fill Handle Entirely
3 cases where fill handle is the wrong tool:
Long stable series. For a 60-period fill that won't change, select the range, enter the formula in the top cell, hit Ctrl+D. More deliberate, no chance of dragging one row too many.
Structured tables in Google Sheets. Tables created via Insert → Table auto-propagate calculated column formulas as rows are added. The fill handle can override this behavior and create reference inconsistencies in the structured column. Leave auto-fill columns alone.
Any formula with conditional locking. If your formula has a mix of locked and relative references across multiple tabs, spell out every $ before you drag. Fill handle will faithfully propagate whatever you gave it — including the wrong anchors.
Where ModelMonkey Fits
Fill handle is fine for simple cases. Where it breaks down is propagating formulas across a new tab structure, maintaining consistent anchoring across 12 linked sheets, or filling a template that references dynamically named ranges — cases where a single wrong $ creates a model that looks right and isn't.
ModelMonkey handles that kind of propagation through natural language: describe what the formula should do, where it should land, and what it should reference. It writes and places it without the quiet reference-shift errors that fill handle produces on cross-tab formulas.
Try ModelMonkey free for 14 days — it works in both Google Sheets and Excel.