What does B2:2 mean in Google Sheets?

`B2:2` is an open-ended range that starts at cell B2 and extends rightward across all of row 2. The start point is B2 (column B, row 2), and the `2` endpoint means "the rest of row 2." In practice, functions like SUM and SUMPRODUCT evaluate up to the last populated cell in that row. Use it when your model adds time-period columns (quarters, months) and you don't want to manually update formula endpoints as columns are added.

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

`B2:2` opens rightward along row 2 - useful for horizontal time series where new quarterly or monthly columns are added. `B2:B` opens downward along column B - useful for vertical data lists where new rows are added (transactions, SKUs, line items). A standard three-statement model uses both: `B2:B` to capture all rows in a data column, and `B2:2` to capture all time periods in a summary row. Mixing them up produces silent errors that return a value but calculate the wrong thing.

Does using B2:2 slow down Google Sheets?

Open-ended row references like `B2:2` carry a small recalculation cost because Sheets must resolve the range boundary on each calculation pass. For financial models under 200 columns, the overhead is negligible. In larger models with 50+ SUMPRODUCT formulas using open row ranges, expect 2-3 second recalculation delays on complex sheets. For sensitivity tables or iterative calculations that recalculate frequently, use bounded ranges (e.g., `B2:BZ2`) to keep response times fast.

Why does SUMIFS return #VALUE! with an open-ended B2:2 range?

SUMIFS requires every range argument to be the same size. If your sum_range is an open-ended `B3:3` but your criteria_range is a bounded `B1:Z1`, the dimension mismatch triggers `#VALUE!`. The fix is consistency: make all ranges in the formula open-ended. For example, `=SUMIFS('Revenue'!B3:3, 'Revenue'!B1:1, ">=" & Assumptions!$C$2)` works because both the sum_range and criteria_range are open row references of matching width. This error is especially tricky because it only fires when your data exceeds the bounded endpoint - the formula can work correctly for months before breaking.

Can I use B2:2 inside ARRAYFORMULA?

Yes. `=ARRAYFORMULA('P&L'!B2:2 * (1 + Assumptions!$B$7))` applies the multiplier to every cell in row 2 from column B rightward. As you add new period columns, the ARRAYFORMULA expands automatically - no copying formulas across columns. This works well for applying a single growth rate or cost adjustment across an entire horizontal time series. The formula stays in one cell, which makes auditing the model easier and prevents the formula-drift issues that come from copying across 18+ monthly columns by hand.

Google Sheets Open-Ended Range B2:2 Explained (2026)

For FP&A work, the choice between them comes down to one question: does your data grow by adding rows, or by adding columns? Vertical lists (transactions, SKUs, deal rows) call for B2:B. Horizontal time series (quarterly periods, monthly actuals stacking right) call for B2:2. Getting them confused creates silent errors that surface in board packs at the worst possible moment.

The Four Variants, Side by Side

Syntax	What it covers	Best use case
`B:B`	All of column B, every row	Quick reads in small models
`B2:B`	Column B from row 2 downward	Vertical lists, skip-header references
`2:2`	All of row 2, every column	Full-row scans
`B2:2`	Row 2 from column B rightward	Horizontal time series, growing periods

As of May 2026, Google Sheets supports up to 10 million cells per spreadsheet. An open-ended reference like B:B technically spans the full column extent - up to 18,278 columns wide for row references, or however many rows your sheet contains for column references. In practice, functions like SUMIFS and COUNTIFS stop evaluating at the last populated cell, but the range definition still gets parsed on every recalculation.

Where B2:2 Shows Up in Real Models

The clearest FP&A use case: a P&L where fiscal quarters stack horizontally. Row 1 holds period labels (Q1 FY26, Q2 FY26, and so on), row 2 holds revenue. Your Q1 revenue is $4.2M in B2, Q2 is $4.8M in C2, and the CFO wants Q3 added before the board pack goes out. A bounded =SUM('P&L'!B2:C2) misses Q3 entirely until someone manually updates the endpoint. With B2:2, the formula is self-maintaining:

=SUM('P&L'!B2:2)

Every new period column lands inside the range automatically.

For cross-tab work where you need to filter by date, SUMPRODUCT with open row ranges handles it cleanly:

=SUMPRODUCT(
  ('P&L'!B1:1 >= Assumptions!$B$3) *
  ('P&L'!B1:1 <= Assumptions!$B$4) *
  ('P&L'!B2:2)
)

This pulls revenue from all quarters falling between the start and end dates in your Assumptions tab - no hardcoded column endpoints, no formula maintenance every quarter.

The SUMIFS Mismatch Problem

SUMIFS requires all range arguments to be the same dimensions. Open row ranges trip analysts up here constantly.

This works:

=SUMIFS('Revenue'!B3:3, 'Revenue'!B1:1, ">=" & Assumptions!$C$2)

This throws #VALUE!:

=SUMIFS('Revenue'!B3:3, 'Revenue'!B$1:Z$1, ">=" & Assumptions!$C$2)

The second criteria range is bounded at column Z while the sum range is open. Sheets can't reconcile the sizes. Fix it by making all ranges consistent - either all open-ended or all bounded. The error doesn't always fire immediately (if your data currently fits within column Z), which is what makes it dangerous: the formula works fine until you add period 27.

Performance: What It Actually Costs

According to Google's Apps Script documentation, whole-row and whole-column references "are evaluated against the entire sheet extent," meaning Sheets resolves the range boundary on each recalculation. For most financial models - a standard 8-tab quarterly board pack with 12-24 months of data - the overhead is undetectable.

At 500+ columns across many linked tabs, or when you're stacking 50+ SUMPRODUCT formulas with open row ranges, you'll see 2-3 second refresh delays. The point where it matters is sensitivity tables and iterative models, where Sheets is recalculating hundreds of cells per keystroke. For those, bound your ranges explicitly (B2:AZ2). For summary formulas that recalculate once when you open the file, open-ended is fine.

Which to Use When: The Model Architecture Question

Most multi-tab models need both variants. A three-statement model built for a bank syndicate DCF typically has:

Line items in rows, periods in columns → B2:B for referencing all rows in a given column (e.g., all expense line items), B2:2 for referencing all periods in a given row
=SUMIFS('P&L'!C:C, 'P&L'!B:B, ">=" & Assumptions!$B$3) uses open columns for vertical data
=SUM('Cash Flow'!B7:7) uses an open row for horizontal period totals

The trap is mixing them up. If your Revenue tab has 38.5% gross margin in row 4 across 12 quarterly columns, and you accidentally reference B4:B instead of B4:4, you'll get a column of vertical data instead of a horizontal sum. Both formulas evaluate without error. One gives you the right number.

ARRAYFORMULA with Open Row Ranges

ARRAYFORMULA works with B2:2 the same way it works with B2:B - it processes every cell in the range, just in the horizontal direction instead of vertical. Useful for applying a single growth assumption across an entire time series without copying formulas across columns:

=ARRAYFORMULA('P&L'!B2:2 * (1 + Assumptions!$B$7))

Where Assumptions!$B$7 holds your revenue growth rate (say, 12.5%). The formula sits in one cell on your Adjustments tab and produces values across the entire row as you add new periods. No copy-paste, no formula drift across columns.

This pattern shows up often in runway sensitivity models - where new hire pace assumptions feed a headcount row that multiplies across 18+ months of projected spend.

The Part Most Documentation Skips

Most Sheets guides cover B:B and B2:B at length, then stop. The horizontal equivalent, B2:2, gets almost no coverage despite being the more natural syntax for financial models where time runs left to right. The related issue of reversed and unbounded column ranges covers a different but adjacent problem.

The original insight: models that grow in both dimensions - new SKUs added as rows, new periods added as columns - need both B2:B and B2:2 working together, and the architecture decision about which dimension holds what data should happen before you build the model. Retrofitting direction changes into a 10-tab model after the fact is miserable.

If you're building a contribution margin model that shows SKU-level performance across 8 quarters, decide upfront: SKUs as rows (open columns for SKU data, open rows for period data) or periods as rows (the opposite). Either works. Mixing the logic halfway through creates formulas that require comments explaining which direction they're reading.

ModelMonkey constructs range references in the same way when it reads your model - if you ask it to "sum the revenue row across all periods," it uses an open row reference automatically rather than guessing at a column endpoint. That matters when you're mid-build and the final column count isn't fixed yet.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.