Google Sheets Open-Ended Column Range B5:B Explained

This isn't obscure syntax. It's the standard way to write formulas that survive data growth without ever needing to be updated.

What B5:B Actually Resolves To

Google Sheets caps a spreadsheet at 10 million cells total. Practically, a single column of data can run to around 1,000,000 rows before you hit sheet limits. When you write B5:B, Sheets scans from row 5 to the last available row. For calculation purposes, it treats the range as if you'd written the full bounded reference yourself.

The open-ended syntax B5:B is the column analogue of the open-ended row range A2:2 (covered in this article on row ranges). One expands vertically, the other horizontally. Both let ranges grow without formula maintenance.

B5:B vs B:B - The Distinction That Actually Matters

Using the full column reference B:B looks convenient but creates two real problems in production models.

First, it includes your header rows. A SUMIFS using B:B as both the criteria range and sum range will try to evaluate your column label ("Revenue", "Date", whatever's in B1:B4) as a number or match value. This usually fails silently - you get a zero where you expect a sum, and spend 20 minutes debugging a formula that looks correct.

Second, it creates circular reference risk. If your formula lives anywhere in column B - a total row, a check figure - B:B includes that cell. B5:B sidesteps this by starting below your headers and formula rows.

The typical model layout that makes B5:B the right default:

With this structure, B5:B targets exactly your data. B:B targets everything.

SUMIFS Across Tabs With Open-Ended Ranges

The most common pattern in a multi-tab FP&A model: a summary tab pulling from a transaction tab, where new rows keep getting added. Here's what that looks like:

=SUMIFS(
  'P&L'!D5:D,
  'P&L'!B5:B, ">=" & Assumptions!$C$3,
  'P&L'!B5:B, "<=" & Assumptions!$C$4,
  'P&L'!C5:C, Returns!$B12
)

This sums column D (revenue amounts) from the P&L tab where the date in column B falls within the period defined by C3:C4 on the Assumptions tab, filtered to the entity in Returns!B12. All three range references use D5:D, B5:B, and C5:C - open-ended, same start row, same effective length.

This is required. SUMIFS demands that all range arguments be identical in dimension. If you mix B5:B with D5:D1000, Sheets throws an error because the ranges don't match. Keep the start rows consistent and let them all be open-ended.

Another real example from a contribution margin model pulling SKU-level data:

=SUMIFS(
  'Transactions'!E5:E,
  'Transactions'!C5:C, SKU_Analysis!$A14,
  'Transactions'!D5:D, "Wholesale"
)

Column E is margin dollars, C is SKU code, D is channel. Every time a new transaction row gets added to the Transactions tab, this formula picks it up automatically. No range expansion needed, no quarterly maintenance task.

ARRAYFORMULA With Open-Ended Ranges

ARRAYFORMULA and B5:B combine well. The pattern for computing a running total or cumulative figure down a column:

=ARRAYFORMULA(
  IF(LEN('Cash Flow'!B5:B)=0, "",
    SUMIFS('Cash Flow'!C5:C,
           ROW('Cash Flow'!C5:C), "<=" & ROW('Cash Flow'!C5:C)))
)

The IF(LEN(...)=0, "") wrapper suppresses output for empty rows - essential when you're using open-ended ranges that extend far beyond your actual data. Without it, you get a column of zeros (or errors) filling thousands of rows below your last entry.

One sharp edge: when an ARRAYFORMULA using B5:B sits in column B itself, you've created a circular reference. Sheets will catch it and throw an error. Put your ARRAYFORMULA output in a different column, or anchor it somewhere above row 5.

Performance: Does B5:B Slow Things Down?

Shorter answer than you might expect. B5:B in a SUMIFS or ARRAYFORMULA does not meaningfully degrade performance compared to a bounded range like B5:B2000, as long as the column has a reasonable amount of data.

Where performance degrades is with volatile functions (TODAY(), NOW(), RAND()) inside formulas that reference large open-ended ranges - those recalculate on every sheet change. The B5:B syntax itself isn't the issue; the combination of volatility and large scan range is.

According to Google's Sheets documentation, SUMIFS with large column references is internally optimized when the data is contiguous. For a model with 5,000 transaction rows, the difference between B5:B5000 and B5:B is negligible. At 100,000+ rows, you might start to see recalculation lag, but at that scale you're running up against the 10 million cell cap anyway.

If your model is noticeably slow and you're using B5:B everywhere, the fix is usually to bound the ranges to a realistic maximum (say, B5:B50000) rather than either the full open-ended or a too-tight bound.

Open-Ended Multi-Column Ranges

Less commonly used but valid: B5:D means columns B through D, all starting at row 5. This works as a source range for SUMIF-style functions but requires that your sum range and criteria range still align.

More practically useful is the mixed form for reading a data block of unknown depth:

=QUERY('Revenue'!B5:G, "SELECT B, SUM(D) GROUP BY B LABEL SUM(D) ''", 0)

Here B5:G reads all columns from B to G, rows 5 to the bottom. QUERY handles the open end gracefully. As of June 2026, QUERY with open-ended ranges performs well for datasets under 50,000 rows.

When Named Ranges Are the Better Call

If you're referencing the same open-ended column range in 30+ formulas across 8 tabs, define it as a named range. RevenueData pointing to 'P&L'!D5:D is easier to audit than hunting every instance of that reference when the P&L tab gets restructured.

The /blog/named-ranges-google-sheets article covers that tradeoff in depth. The short version: use open-ended literal references for one-off formulas, switch to named ranges when the reference appears in 5 or more places or crosses more than 2 tabs.

ModelMonkey and Range Expansion

One place B5:B range issues surface unexpectedly is when AI tools write formulas into your sheet. ModelMonkey's write tool uses explicit bounded ranges by default (it can see your actual data and sets a sensible end row), but when you ask it to build out a full-column SUMIFS structure or expand a formula to handle future data, it writes open-ended ranges correctly. Worth knowing if you're using it to scaffold multi-tab models.

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