Google Sheets Empty Array Literal {}: Valid or Not?

That matters most when you're building formulas that need to return "nothing" on a zero-match condition, which comes up constantly in multi-tab financial models: variance analysis filtered by date, FILTER-based waterfalls that might return zero SKUs in a given period, or QUERY-powered bridge tables pulling from a data tab.

Why Google Sheets Doesn't Have Empty Array Literals

Array literals in Google Sheets - the {} syntax - require at least one value. A horizontal array like {1,2,3} is valid. A vertical one like {"Revenue";"COGS";"Gross Profit"} is valid. A single-cell array like {""} is valid. An empty {} is not.

This isn't an oversight you can work around with escaping or nesting. Google's array literal parser rejects zero-element arrays at the syntax level, before any evaluation happens. According to the Google Workspace Apps Script documentation on Sheets array literals, every array literal must contain at least one value or the formula won't compile.

The distinction matters because {} is valid in Excel's LAMBDA and LET contexts, and in some scripting environments. Analysts who work across both platforms sometimes try to port patterns that rely on empty arrays and wonder why Sheets rejects them.

Where the Empty Array Literal Gap Breaks Financial Models

The failure mode shows up in 3 specific places in a typical model.

FILTER with no matches. Without a third argument, =FILTER('P&L'!C2:F500, 'P&L'!B2:B500=Assumptions!$B$3) returns #N/A when no rows match. The natural instinct is to use {} as a fallback - something like IFERROR(FILTER(...), {}). That {} kills the formula before it runs.

QUERY returning zero rows. Per Google's QUERY function documentation, when a WHERE clause matches no rows with headers=0, QUERY returns the string "did not match any row in the table" rather than an error value. IFERROR won't catch a string - it only catches error codes like #N/A and #VALUE! - so downstream formulas that reference the QUERY output range receive unexpected text instead of numbers.

Contribution margin by SKU with seasonal gaps. If you're running =FILTER('Revenue'!A2:D500, 'Revenue'!C2:C500=SKU_Assumptions!$A5) for each SKU and certain SKUs have zero transactions in a given month, you get a grid of #N/A errors across your contribution margin tab. A board pack with 40 SKUs and 12 months means up to 480 formula cells in error state simultaneously - and every formula downstream of that tab inherits the error.

In models with 5 or more unprotected FILTER calls (no IFERROR or third-argument fallbacks), a single month with no matching data cascades into broken P&L totals, busted variance columns, and a CFO who notices something is wrong at 11pm before a 9am presentation.

Working Alternatives to the Empty Array Literal {}

The fix depends on how many columns your filter returns and what you want "empty" to look like.

Option 1: FILTER's third argument. The cleanest fix for FILTER. According to the Google Sheets FILTER function documentation, the third argument accepts a value or array returned when no rows match. For a 4-column filter, provide a 4-element array:

=FILTER(
  'P&L'!B2:E500,
  'P&L'!A2:A500=Assumptions!$B$3,
  {"","","",""}
)

This preserves the column structure. Formulas referencing this output still see 4 columns - they just get empty strings instead of #N/A. That is the behavior you actually want.

Option 2: IFERROR with a typed fallback. When you cannot predict column count, or when wrapping a QUERY:

=IFERROR(
  FILTER('Revenue'!A2:D500, 'Revenue'!C2:C500=Assumptions!$A$5),
  ""
)

This collapses to a single empty cell, not a 4-column array. Downstream SUMIFS still work (they see empty, not an error). But if another formula is doing a structured reference into this range expecting 4 columns, it will break differently.

Option 3: Guard with COUNTIFS first. The verbose approach, but fully explicit:

=IF(
  COUNTIFS('P&L'!B2:B500, Assumptions!$B$3) > 0,
  FILTER('P&L'!C2:F500, 'P&L'!B2:B500=Assumptions!$B$3),
  ""
)

Evaluates the filter count before calling FILTER at all. Adds a recalculation pass but makes the logic completely transparent. Useful in models where auditability matters more than conciseness.

Option 4: IFERROR wrapping QUERY. For QUERY specifically, IFERROR catches hard errors but misses the zero-row text case. The most reliable pattern combines both:

=IFERROR(
  QUERY(
    'Revenue'!A1:D500,
    "SELECT A,B,C,D WHERE B='" & Assumptions!$B$3
    & "' AND C IS NOT NULL LABEL A 'Period', B 'Category', C 'Revenue', D 'Units'"
  ),
  ""
)

The IS NOT NULL clause reduces the chance of returning zero-row text. Not elegant, but reliable across edge cases.

Comparison: Approaches to Handling Zero Rows

A note on performance: from what I have tested on ranges of 2,000-5,000 rows, FILTER runs noticeably faster than QUERY for simple condition filtering. FILTER is a native array function; QUERY parses SQL-like syntax before execution, which adds overhead. For a quarterly board pack that recalculates on every sheet change, that difference compounds across 8+ tabs. If you're using QUERY purely to filter rows rather than aggregate them, FILTER with a typed third argument is both faster and safer.

As of June 2026, neither FILTER nor QUERY in Google Sheets supports returning a properly-typed empty array on zero rows. The {} syntax is not on Google's public roadmap for Sheets.

If you're building or troubleshooting these patterns, ModelMonkey generates FILTER calls with correctly-sized third arguments automatically, rather than attempting {} fallbacks that fail at parse time.