Can QUERY reference data from another Google Sheets file?

No. QUERY only works within the same spreadsheet file. To query data from a separate file, first pull it in with `=IMPORTRANGE("spreadsheet_url", "Sheet1!A:H")`, then wrap that with QUERY. The trade-off is that IMPORTRANGE requires a connection approval per user and refreshes on a ~30-minute cycle, not instantly — which matters for live dashboards.

Why does my QUERY formula return the wrong data after I added a column?

QUERY references columns by letter position (A, B, C...) relative to the entire sheet, not relative to your data range. If you insert a column to the left of your data, all column references in your query string shift by one. The formula doesn't error — it quietly queries a different column. The fix is either to audit column letters after any structural change, or to always reference your data range tightly (e.g., `'P&L'!B:H` instead of `'P&L'!A:Z`) so you know exactly what each letter maps to.

How do I use a cell reference inside a QUERY WHERE clause?

Concatenate the cell reference into the query string using `&`. For text values: `"WHERE B = '" & Assumptions!$B$5 & "'"`. For dates: `"WHERE C >= date '" & TEXT(Assumptions!$B$3, "yyyy-MM-dd") & "'"`. For numbers: `"WHERE D > " & Assumptions!$B$6`. The date format is strict — must be `yyyy-MM-dd` inside `date '...'`.

What's the row limit for QUERY before performance degrades?

Google Sheets caps at 10 million cells per spreadsheet, but QUERY performance degrades well before that. In practice, QUERY on ranges up to about 30,000-50,000 rows returns in under 3 seconds for most aggregation queries. Above 80,000 rows, expect 8-15 seconds per formula recalculation, which makes the model painful to work in. For large GL extracts, pre-aggregate with SUMIFS into a helper tab and QUERY against that instead.

Can QUERY replace pivot tables for FP&A work?

For dynamic reporting where you need a filtered table that updates based on dropdown inputs, QUERY often beats pivot tables — it's formula-driven, it links into the rest of your model, and it doesn't require a manual refresh. Where pivot tables win: drag-and-drop exploration, percent-of-total calculations, and multiple levels of subtotaling. QUERY with `GROUP BY` handles one level of aggregation; pivot tables handle nested subtotals without custom formulas. For a board pack with a fixed layout, QUERY. For ad-hoc exploration, pivot tables. --- The column-letter fragility is the real gotcha — models that use QUERY heavily need a discipline around never inserting columns into ranges that formulas reference. That's a structural decision worth making before you're 40 tabs deep. [Try ModelMonkey free for 14 days](/install) — it works in both Google Sheets and Excel.

Google Spreadsheet QUERY Function: FP&A Guide

Here's the full picture: what it does well, where it breaks, and when SUMIFS still wins.

What QUERY Actually Does

The syntax is:

=QUERY(data, query_string, [headers])

data — the range, including headers (e.g., 'P&L'!A:H)
query_string — a SQL-like string using Google Visualization Query Language
headers — number of header rows; almost always 1

A basic cross-tab pull looks like this:

=QUERY('GL Actuals'!A:G, "SELECT A, C, SUM(G) WHERE B = 'North America' GROUP BY A, C LABEL SUM(G) 'Revenue'", 1)

That replaces 6 separate SUMIFS — one for each department — and returns a live table instead of a single cell.

QUERY supports SELECT, WHERE, GROUP BY, ORDER BY, LIMIT, OFFSET, and LABEL. It doesn't support JOINs (that's DuckDB territory, not native Sheets), subqueries, or window functions. Think of it as a filtered pivot table you can drive from a formula.

Multi-Tab Finance Formulas

The most common FP&A use is pulling filtered cuts from a master actuals tab into summary tabs. Here's how that works in practice.

Quarterly P&L by business unit, dates from Assumptions tab:

=QUERY('GL Actuals'!A:H,
  "SELECT C, SUM(H)
   WHERE A = 'Revenue'
   AND B = 'EMEA'
   AND D >= date '" & TEXT(Assumptions!$B$3,"yyyy-MM-dd") & "'
   AND D <= date '" & TEXT(Assumptions!$B$4,"yyyy-MM-dd") & "'
   GROUP BY C
   ORDER BY SUM(H) DESC
   LABEL SUM(H) 'Actuals ($)'",1)

Note the date formatting. QUERY requires dates in yyyy-MM-dd inside a date '...' literal — the TEXT() wrapper is how you dynamically inject a date from your Assumptions tab. Miss that format and you get #VALUE! with no useful error message.

Contribution margin by SKU across a 400-row product table:

=QUERY('SKU Data'!A:F,
  "SELECT A, B, SUM(D)-SUM(E) 
   WHERE C = '" & Dashboard!$B$2 & "'
   GROUP BY A, B
   ORDER BY SUM(D)-SUM(E) DESC
   LABEL SUM(D)-SUM(E) 'Contribution Margin'",1)

The Dashboard!$B$2 reference injects a dropdown value — change the region or product category in the dropdown and the whole table refreshes. No pivot table refresh required, no manual filter adjustment.

Department expense rollup for a board pack:

=QUERY('Actuals'!A:G,
  "SELECT B, SUM(G)
   WHERE A >= date '2025-01-01'
   AND A <= date '2025-12-31'
   AND C <> 'Intercompany'
   GROUP BY B
   ORDER BY B
   LABEL B 'Department', SUM(G) 'FY2025 Opex'",1)

That's your annual opex table with intercompany eliminations, three lines of query string.

QUERY vs. SUMIFS: When to Use Which

This is where analysts get it wrong. QUERY isn't strictly better — it's better for a specific shape of problem.

Situation	Use
Single aggregated value in a cell	SUMIFS
Table output with N rows (unknown at build time)	QUERY
Multiple conditions on the same column	QUERY (cleaner)
Referencing external files	SUMIFS (QUERY can't cross files)
Speed on 100k+ rows	SUMIFS
Dynamic column selection	QUERY
Contribution margin by 400 SKUs	QUERY
Total Q3 marketing spend in one cell	SUMIFS

According to Google's Sheets API documentation, QUERY processes data server-side using the Visualization API query engine — which means it's genuinely executing a query, not just iterating cells. That makes it fast on medium-sized datasets (under ~50,000 rows) and noticeably slow on large ones.

From testing in April 2026: QUERY on a 20,000-row actuals tab returns in under 2 seconds. At 80,000 rows, the same query slows to 9-12 seconds on a standard Sheets file. SUMIFS stays under 1 second at both sizes. If your GL export is large, SUMIFS wins on performance even if QUERY wins on readability.

Where QUERY Breaks Down

No cross-file references. The data range must live in the same spreadsheet. If your actuals tab is in a separate file — common in team environments where the GL export lives in its own doc — you need IMPORTRANGE first, then QUERY on top of that. Which introduces refresh latency and the occasional "Loading..." state right before your board pack is due.

Positional column references. QUERY refers to columns by letter (A, B, C) or by position (Col1, Col2) — not by header name. Add a column to the left of your data range and every QUERY formula that touches that tab breaks silently (it doesn't error out; it just queries the wrong column). This is the single biggest maintenance risk in QUERY-heavy models.

Date literals are unforgiving. The format must be date 'yyyy-MM-dd'. Not date 'MM/dd/yyyy', not a serial number, not a Sheets date value. If your Assumptions tab stores dates as formatted display strings rather than real date values, TEXT(cell, "yyyy-MM-dd") saves you. If they're stored as text strings already, you need DATEVALUE first.

No structured table references. You can't reference a named range column like Revenue[Amount] inside a QUERY string. It's range letters only.

Empty column headers cause errors. QUERY interprets the first row of your range as headers (when headers=1). If any column in your range has a blank header cell, the formula breaks. Clean your header row before writing QUERY formulas against it.

The Non-Obvious Trick: Dynamic Column Selection

Most QUERY guides show static column letters. For a quarterly board pack where you want to show only certain columns based on a cell value, you can build the query string dynamically:

=QUERY('P&L'!A:J,
  "SELECT A, B, " & IF(Dashboard!$C$1="Quarterly","C,D,E,F","C") & ", J
   WHERE H = 'Operating Expenses'
   ORDER BY A",1)

That toggles between showing monthly columns (C through F) and a single YTD column based on a dropdown. It's the kind of thing that looks like magic to whoever inherits the model and has no idea it's just string concatenation.

The approach scales. You can construct the entire query string from cell values, named ranges, and IF logic — which means a single QUERY formula can power a board pack that adapts to 12 different filter combinations without any changes to the formula itself.

Pairing QUERY with AI Analysis

One practical limitation: once QUERY returns a table, any follow-on analysis still requires additional formulas. You can't ask QUERY to flag outliers, explain a variance, or suggest what changed.

ModelMonkey sits inside the Sheets sidebar and can read the output of a QUERY table, then answer questions against it — "what's driving the EMEA variance in row 14?" or "which SKUs have contribution margin below 20%?" — without you exporting to a separate tool. It's the step after QUERY, not a replacement for it.