Why does `{"Header"; ARRAYFORMULA(range)}` return `#VALUE!` instead of a column?

The most common cause is a column count mismatch between the header row and the data array. If your header has 4 strings separated by commas but your ARRAYFORMULA returns 3 columns, Sheets throws `#VALUE!`. Count the commas in your header row (n commas = n+1 headers) and verify it matches the column count in your inner array. A second cause is locale: in European-locale files, the semicolon is a column separator, not a row separator, so the formula is being parsed differently than intended.

Do I need ARRAYFORMULA if I'm referencing a fixed range?

No. `={"Revenue"; 'P&L'!C2:C24}` works without ARRAYFORMULA when the range is explicitly bounded. ARRAYFORMULA is required when you use open-ended ranges like `C2:C`, or when your inner expression includes arithmetic across ranges (e.g., `C2:C - D2:D`) that needs to expand row by row. In a live financial model where rows are added regularly, open-ended ranges with ARRAYFORMULA are safer than fixed bounds you have to update manually.

How do I stop the array from filling thousands of blank rows below my data?

Wrap the inner expression in an IF that checks a column you know will be empty when the data ends: ``` ={"Revenue"; ARRAYFORMULA(IF('P&L'!A2:A="","", 'P&L'!C2:C))} ``` When `'P&L'!A2:A` is blank, the formula returns an empty string instead of propagating downward. Choose a column that's reliably populated for every data row - typically the date or period column, or the primary label column. Avoid checking the value column itself, since legitimate zeros would prematurely truncate the array.

Is this pattern compatible with QUERY?

Yes, and this is one of its strongest use cases. QUERY treats the first row of a source range as column headers. When your source is a dynamic array built with `{"Header"; ARRAYFORMULA(...)}`, QUERY can reference it by column label: ``` =QUERY({'Summary'!A1:E}, "SELECT Col1, Col2 WHERE Col3 > 0 ORDER BY Col3 DESC", 1) ``` The `1` at the end tells QUERY to treat the first row as a header. The array literal ensures that header exists as a string, not a formula result or blank cell. This is the standard setup for a contribution margin by SKU analysis where you want to filter and sort dynamically without rebuilding the source range.

What is the difference between `{"H1","H2"; data}` and `{"H1"; "H2"; data}`?

`{"H1","H2"; data}` creates a 2-column header row followed by the data rows - this is what you want for a multi-column table. `{"H1"; "H2"; data}` creates 3 separate rows stacked vertically: "H1" in row 1, "H2" in row 2, then data starting in row 3. In a financial model, the first form is almost always correct for labeled data tables. The second is occasionally useful if you want a title row above a header row, but that's a niche case. [Try ModelMonkey free for 14 days](/install) - it works in both Google Sheets and Excel.

Google Sheets Array Literal Header Row Formula Guide

Google's official documentation covers array literals at a surface level but never explicitly documents this pattern. What the Sheets help docs state, as of June 2026, is the general rule: in US-locale spreadsheets, semicolons separate rows in an array literal and commas separate columns. The header trick is a direct consequence of that - the string before the semicolon becomes row 1, and the ARRAYFORMULA output stacks below it.

How the Array Literal Syntax Works

In Google Sheets, {A; B} concatenates A and B vertically into a single column. {A, B} stacks them side by side into a row. The header pattern exploits the vertical case:

={"Revenue"; ARRAYFORMULA('P&L'!C2:C)}

Row 1 gets the string "Revenue". Rows 2 through N come from column C of the P&L tab. The ARRAYFORMULA wrapper is what allows the open-ended range (C2:C) to expand dynamically - without it, an open range inside an array literal throws #VALUE!.

You can technically skip ARRAYFORMULA with fixed ranges:

-- Works for a known range, but breaks if rows are added
={"Revenue"; 'P&L'!C2:C24}

In practice, finance models grow. Use ARRAYFORMULA and wrap the inner expression with an IF to terminate cleanly:

={"Revenue"; ARRAYFORMULA(IF('P&L'!A2:A="","", 'P&L'!C2:C))}

Without the IF, the formula extends to approximately row 1,000,000 (Google Sheets supports up to 10 million cells per spreadsheet), filling every blank row with empty strings. That's what breaks your SUMIFS, blows up chart ranges, and causes pivot tables to show phantom rows at the bottom of quarterly board packs.

Multi-Column Header Syntax

For multiple columns, the header row is comma-separated strings before the semicolon, and the data side is a matching array:

={"SKU","Revenue ($M)","COGS ($M)","Gross Profit","Margin %";
  ARRAYFORMULA(IF('P&L'!A2:A="","",{
    'P&L'!A2:A,
    'P&L'!C2:C,
    'P&L'!D2:D,
    'P&L'!C2:C - 'P&L'!D2:D,
    TEXT(('P&L'!C2:C - 'P&L'!D2:D) / 'P&L'!C2:C, "0.0%")
  }))}

This is a contribution margin table by SKU - 5 columns, inline calculation, terminates when the SKU column runs out. The header count must match the column count in the data array exactly, or Sheets returns #VALUE!. For a quarterly board pack where you're building a horizontal summary across 5 forecast periods:

={"Period","Revenue","EBITDA","EBITDA Margin";
  ARRAYFORMULA(IF('Assumptions'!B3:F3="","",{
    'Assumptions'!B3:F3,
    'P&L'!B5:F5,
    'P&L'!B15:F15,
    TEXT('P&L'!B15:F15 / 'P&L'!B5:F5, "0.0%")
  }))}

A model with $18.4M projected revenue at a 38.5% EBITDA margin in Year 1 would render "38.5%" automatically as that margin compresses or expands in the assumptions.

The Locale Problem Nobody Warns You About

This is the silent model-breaker. In ~40 countries that use a comma as the decimal separator (Germany, France, the Netherlands, Brazil when working in PT-BR Sheets), Google Sheets flips the array literal separators:

US locale: semicolon = new row, comma = new column
European locale: semicolon = new column, backslash \ = new row

So {"Header"; ARRAYFORMULA(...)} in a European-locale file puts "Header" and the ARRAYFORMULA output side by side on the same row, not stacked. Your 5-column contribution margin table becomes a 10-column mess.

The locale is set at the file level under File > Settings > Locale, not the user's system locale. When you share a model with a bank syndicate or PE sponsor in Frankfurt and they open it in their Sheets environment, the locale may differ from yours. According to Google's Sheets documentation, the separator behavior is tied to the spreadsheet's locale setting, not the viewer's regional settings - but this gets complicated when files are duplicated or imported.

Practical fix: add a cell note documenting the expected locale, or test with a throwaway file before distributing a model with array literal headers.

VSTACK: The 2022 Alternative

Google released VSTACK in late 2022. The functional equivalent of the header pattern is:

=VSTACK({"SKU","Revenue ($M)","Margin %"},
  ARRAYFORMULA(IF('P&L'!A2:A="","",{
    'P&L'!A2:A,
    'P&L'!C2:C,
    TEXT(('P&L'!C2:C-'P&L'!D2:D)/'P&L'!C2:C,"0.0%")
  }))
)

VSTACK is locale-safe and handles mixed-width arrays more gracefully in edge cases. The trade-off is portability: VSTACK requires a Sheets version that supports it (unavailable before 2022), and while Excel 365 has its own VSTACK, the behavior differs enough that a model designed around it won't translate cleanly if you're maintaining parallel Excel versions for bank presentations.

	`{"header"; ARRAYFORMULA}`	VSTACK
Locale-safe	No	Yes
Sheets version required	Any	2022+
Blank row suppression	Required	Often still needed
Readable syntax	Moderately	Cleaner
Excel compatibility	No	Different behavior

For models that will never leave Google Sheets and your Sheets file is in US locale, either works. For anything shared internationally or maintained in both Sheets and Excel, VSTACK is lower-risk.

Where the ARRAYFORMULA Scope Actually Matters

A frequent mistake: wrapping the outer array literal in ARRAYFORMULA instead of the inner range expression.

-- Wrong: ARRAYFORMULA is on the outside, doing nothing useful
=ARRAYFORMULA({"Revenue"; 'P&L'!C2:C})

-- Correct: ARRAYFORMULA expands the range inside the array
={"Revenue"; ARRAYFORMULA('P&L'!C2:C)}

-- Correct: ARRAYFORMULA expands a calculation
={"Gross Profit ($M)"; ARRAYFORMULA('P&L'!C2:C - 'P&L'!D2:D)}

The ARRAYFORMULA needs to sit around the expression that requires expansion - the subtraction across two open ranges, the IF condition, the TEXT formatting. Moving it to the outside doesn't break the formula, but it also doesn't fix the expansion problem if your inner expression contains arithmetic on ranges.

When to Use This Pattern

Reach for it when you need a single-formula data block with a header - typically for:

Summary tabs pulling labeled columns from a P&L, balance sheet, or cash flow
QUERY source ranges where the first row must be a header string (not a cell reference)
Waterfall or bridge chart data tables where each row feeds a labeled chart series
Sensitivity output tables combining scenario names with NPV or IRR calculations from a bank syndicate DCF

Skip it when your header and data are intentionally separated across rows in a structured model layout. Most multi-tab financial models keep headers in their own row for clarity, and the array literal approach is more useful in summary/output contexts than in the core model mechanics.

If you're generating these output tables programmatically - say, pulling from a live data source into a labeled Sheets table - ModelMonkey handles the header-and-data construction for you, including the IF termination logic that most hand-built formulas miss.