What does B2:2 mean in Google Sheets?

`B2:2` is an open-ended row range. It selects from cell B2 rightward through every column in row 2 - the row equivalent of `B2:B` (which extends downward through all rows in column B). You'd use `B2:2` in a MATCH formula scanning a period header row when the number of columns isn't fixed, rather than hardcoding an end column like `B2:Z2`. If you later add columns AA, AB, and beyond, the open-ended form picks them up automatically.

What's the difference between B:B and B2:B in Google Sheets?

`B:B` selects the entire column including row 1. `B2:B` starts at row 2. In a SUMIFS criteria range where row 1 is a header label, both usually produce the same numeric result since the header won't match your criteria string. The difference matters in ARRAYFORMULA and other array contexts: `B:B` tells Sheets to evaluate all 10 million potential rows, which slows recalculation noticeably in a model with multiple array formulas. `B2:B` limits the scope to populated data.

How do I reference another tab in Google Sheets using A1 notation?

Use `'Tab Name'!Range` - for example, `'P&L'!C2:D50`. Single quotes around the tab name are required when the name contains spaces, hyphens, or special characters. For a tab named with no special characters (like `Assumptions`), the quotes are optional. According to Google's Sheets documentation, the exclamation mark is the required separator between sheet name and range address in all cross-sheet references.

When should I use absolute references in cross-tab formulas?

Lock any reference that shouldn't move when you copy the formula. Dragging a formula across 12 period columns means the column should shift - but the row of your input line item should stay fixed (`C$4`). Any assumption cell you're pointing to from another tab should be fully locked (`Assumptions!$B$3`). The most common failure mode is a cross-sheet reference that drifts vertically when the formula is copied down, producing wrong numbers with no error flag.

Does A1 notation work the same in Google Sheets and Excel?

Mostly, with two practical differences. First, Excel requires single quotes around tab names in cross-sheet references only when the name contains spaces or special characters (same as Sheets), but external workbook references add a different syntax with brackets and file paths. Second, Excel supports R1C1 notation as an alternative addressing system (row number, column number) configurable in Options - Google Sheets has no R1C1 mode in formulas, though the Sheets API v4 uses `rowIndex`/`columnIndex` integers in some endpoints. For day-to-day formula writing, A1 notation transfers between the two without changes.

Google Sheets A1 Notation: FP&A Reference Guide (2026)

What trips up even experienced modelers is the full set of patterns - especially open-ended ranges like B2:B and the less-obvious B2:2 - and how absolute versus relative references interact when formulas get copied across tabs in a 3-statement model.

The Complete A1 Notation Pattern Set

Form	Example	What it references
Single cell	`D7`	One cell
Rectangular range	`C4:F28`	Block from top-left to bottom-right
Full column	`C:C` or `C:F`	Every row in those columns
Full row	`4:4` or `4:7`	Every column in those rows
Open-ended column	`C4:C`	Column C from row 4 down to last row
Open-ended row	`C4:4`	Row 4 from column C rightward to last column
Cross-sheet	`'P&L'!C4:F28`	Range on a different tab

Most of these are obvious in isolation. In practice, choosing the wrong form is how a model that looks clean breaks when someone inserts a row or extends the period columns past column Z.

Absolute vs. Relative: Where Models Actually Break

The $ sign controls whether a reference shifts when you copy a formula. Four combinations:

C4 - column and row both move with the copy
$C4 - column C is locked; row shifts
C$4 - row 4 is locked; column shifts
$C$4 - fully locked, never moves

In a multi-period model where you're dragging a formula across 12 quarterly columns, the standard pattern is:

='P&L'!C$4 * Assumptions!$C$3

C$4 lets the column shift (C to D to E as you drag right) while row 4 stays put. Assumptions!$C$3 is fully locked because the assumption cell never moves.

The failure mode: copying a formula vertically and forgetting the $ on a cross-sheet row reference. The formula silently shifts from 'Assumptions'!$B$3 to 'Assumptions'!$B$4. No error, just a wrong number. Board pack already printed.

Cross-Sheet References in Multi-Tab Models

The syntax is 'Sheet Name'!Range. Single quotes are required when the tab name contains spaces, hyphens, or parentheses. According to Google's Sheets documentation, the exclamation mark separates the sheet name from the range, and omitting quotes on a multi-word name throws a parse error.

For SUMIFS across tabs, every range argument that comes from the same sheet needs the same prefix:

=SUMIFS(
  'P&L'!D2:D,
  'P&L'!B2:B,
  Assumptions!$B$3,
  'P&L'!C2:C,
  ">=" & Assumptions!$C$4
)

This pulls revenue from the P&L tab where the category matches an assumption and the date is on or after a quarter start. A scalar from Assumptions is fine mixed in as a criteria value - the rules only apply to sum_range and criteria_range arguments, which must come from consistent sheets. Cross-pollinating criteria ranges (pulling one from P&L, another from a different tab) is the bug that passes IFERROR and returns a silently wrong number.

Open-Ended Ranges: B2:B and B2:2

C2:C is the right choice when your data grows row-by-row. Row 1 holds headers (period labels, line-item names), row 2 is where data starts. C:C includes row 1; C2:C skips it.

In most SUMIFS this doesn't change the result because a header like "Q1 2025" won't match your criteria string. The performance difference is real, though. Google Sheets caps at 10 million cells per spreadsheet - and when you use C:C in an ARRAYFORMULA, Sheets tries to evaluate all 10 million potential rows. A model with 20 ARRAYFORMULA calls on full-column ranges runs meaningfully slower than the same model written with C2:C. The difference is often 3-5 seconds per recalculation on a mid-size 3-statement model.

C4:4 is the row equivalent of C4:C. It selects from column C rightward through every column in row 4. You see it in MATCH formulas that scan a header row for a period label:

=MATCH(Assumptions!$D$2, 'Cash Flow'!C3:3, 0)

This finds which column in the Cash Flow header row (row 3, starting at column C) matches the target period. If you hardcode C3:Z3 and later add columns past Z, the MATCH misses them silently. The open-ended C3:3 adjusts automatically as the model grows.

INDIRECT: When A1 Notation Gets Dynamic

INDIRECT evaluates a text string as a cell reference:

=INDIRECT("'" & Assumptions!$A$2 & "'!C2:C")

If Assumptions!$A$2 contains P&L, this resolves to 'P&L'!C2:C. Some scenario models use this to let users flip between base-case, downside, and upside tabs by typing a name into a single assumption cell.

The cost: INDIRECT is volatile. Google's documentation notes that volatile functions recalculate on every sheet change, including edits that have nothing to do with the referenced cell. In a model with 30+ INDIRECT calls, this adds 2-4 seconds to every keystroke. Named ranges handle most of what INDIRECT is used for and recalculate only when their source data changes.

A Real-World Multi-Tab Formula

Here's what A1 notation looks like when it's actually doing work - a FCFF build pulling from three tabs in a bank syndicate DCF model:

=SUMIFS(
  'P&L'!C2:C,
  'P&L'!A2:A,
  ">=" & 'Assumptions'!$B$3,
  'P&L'!A2:A,
  "<=" & 'Assumptions'!$B$4
)
- SUMIFS(
  'CapEx'!D2:D,
  'CapEx'!A2:A,
  ">=" & 'Assumptions'!$B$3,
  'CapEx'!A2:A,
  "<=" & 'Assumptions'!$B$4
)
- 'Working Capital'!$C$14

Three tabs, two date-bound SUMIFS, one fixed scalar. Every cross-sheet reference is explicit. The date bounds in Assumptions are fully locked ($B$3, $B$4) so they don't move when the formula is copied down for each projection year. The C2:C and D2:D ranges start at row 2 to skip headers.

Original Insight: The Row-Extent Problem

Most guides cover B:B vs B2:B. Almost none cover the equivalent row pattern, and that gap causes real model errors.

When a team extends a model from 8 quarterly columns to 20 (say, a 5-year weekly cash flow), any formula that hardcoded a column endpoint like B3:J3 now stops short. The MATCH returns the wrong column index. The lookup pulls the wrong period. The model is wrong from column K onward.

The fix - B3:3 instead of B3:J3 - is a one-character change. Teams that know the pattern write models that self-extend; teams that don't spend 45 minutes debugging a mis-indexed IRR when the model gets handed to them mid-deal.

As of June 2026, Google Sheets still doesn't warn you when a hardcoded range endpoint cuts off your data. It returns whatever the range contains and treats truncation as correct behavior.