How to write QUERY and ARRAYFORMULA formulas in Google Sheets with Gemini
Gemini in Google Sheets can generate a formula from a plain-English request, place it in a cell and attempt a repair when the result is an error. Google's help center describes the entry points: the Ask Gemini button at the top right of a spreadsheet opens the side panel, and typing an equals sign in any cell followed by Ctrl + Alt + g on Windows and ChromeOS, or ⌘ + Ctrl + g on a Mac, starts formula generation directly in the grid.
QUERY and ARRAYFORMULA are the two Sheets functions where that help saves the most time and where a wrong answer is hardest to spot. QUERY runs a statement written in the Google Visualization API Query Language against a range, so a single misplaced clause or a date compared as text produces a table that looks plausible and is wrong. ARRAYFORMULA fills a whole column from one cell, so a missing guard for blank rows quietly writes values far below the data.
The method below uses a small illustrative sales sheet, the prompts an analyst would give Gemini, the formulas that produce the correct answer, and the exact figures those formulas must return, computed independently in Python. The documentation cited was checked on November 15, 2026.
··········
GEMINI IN GOOGLE SHEETS: PLANS, ENTRY POINTS AND FORMULA FEATURES.
The current help page for Gemini in Sheets states that the feature requires an eligible Google Workspace or Google AI plan, and that it is also offered through the Workspace Experiments trusted tester program. When Google announced more conversational formula generation on September 24, 2025, it listed Business Standard and Plus, Enterprise Standard and Plus, Google AI Pro for Education, and the consumer Google AI Pro and Ultra plans, plus customers who still hold the discontinued Gemini Business and Gemini Enterprise add-ons. That update added step-by-step explanations of complex formulas, explanations of why a formula failed with a corrected version in a follow-up, and multiple alternative formulas for the same task. Google also notes that users need smart features and personalization turned on, a setting the Workspace administrator controls in the Admin console.
........
Item | What Google documents |
Plan | An eligible Google Workspace or Google AI plan |
Side panel | Ask Gemini button, top right of the spreadsheet |
Formula shortcut | Type = in a cell, then Ctrl + Alt + g (⌘ + Ctrl + g on Mac) |
Insert result | Select the target cell, click Insert; Retry for another version |
Error repair | Hover over the error cell and click Fix |
Admin setting | Smart features and personalization turned on |
Chat history | Lost on reload, on reopening the file or when offline |
........
Two documented limits shape how formula work should be organized. Conversation history in the side panel disappears when the browser reloads or the spreadsheet is closed, so a formula that is only described in the chat and never inserted is lost. Conversations in Sheets are also kept out of Gemini Activity, and Google warns that content Gemini adds to a file shared outside the domain may be visible to people outside it. For broader analysis tasks beyond single formulas, the site's review of Gemini for data analysis and chart generation in Google Sheets covers the side panel's other functions.
··········
QUERY AND ARRAYFORMULA SYNTAX THAT GEMINI MUST RESPECT.
The QUERY help page gives the signature QUERY(data, query, [headers]). The query argument must be enclosed in quotation marks or be a reference to a cell that contains the text, and the optional headers argument sets the number of header rows; when it is omitted or set to -1, Sheets guesses. Each column of the data range can hold only boolean, numeric (including date and time) or string values, and when a column mixes types the majority type wins while the minority values are treated as nulls.
The Query Language reference adds the rules that most often break generated formulas. Clauses must appear in a fixed order: select, where, group by, pivot, order by, limit, offset, label, format, options. A date literal is the keyword date followed by a string in yyyy-MM-dd format, such as date '2026-10-01'. Every column in a select clause that uses group by must either appear in the group by clause or be wrapped in an aggregation function, and the supported aggregations are avg, count, max, min and sum. The scalar function month() is zero-based, returning 0 for January and 11 for December.
ARRAYFORMULA(array_formula) takes a range, an expression over same-sized ranges, or a function that returns more than one cell, and displays the results across multiple rows or columns. Google's page adds that pressing Ctrl+Shift+Enter while editing a formula inserts the ARRAYFORMULA wrapper automatically, that many array formulas expand into neighboring cells without the wrapper, and that array formulas cannot be exported.
··········
WORKED EXAMPLE: AN ILLUSTRATIVE SALES SHEET FOR GEMINI PROMPTS.
The sheet below is illustrative data, not real figures. It occupies A1:E13 with headers in row 1 and twelve orders in rows 2 to 13, one of them dated September 29 and two dated in November, so a correct October filter has something to exclude on both sides.
........
Order date | Region | Rep | Units | Unit price |
2026-09-29 | East | Lopez | 12 | 45.00 |
2026-10-02 | West | Chen | 8 | 120.00 |
2026-10-03 | East | Patel | 20 | 45.00 |
2026-10-07 | Central | Brooks | 5 | 310.00 |
2026-10-09 | West | Chen | 15 | 45.00 |
2026-10-14 | East | Lopez | 3 | 310.00 |
2026-10-18 | Central | Brooks | 10 | 120.00 |
2026-10-22 | West | Nguyen | 6 | 310.00 |
2026-10-27 | East | Patel | 9 | 120.00 |
2026-10-31 | Central | Diaz | 14 | 45.00 |
2026-11-03 | West | Nguyen | 4 | 310.00 |
2026-11-05 | East | Lopez | 7 | 120.00 |
........
The first task is a revenue column that fills itself as new orders are added. A prompt that names the cells and the blank-row behavior gives Gemini what it needs to produce one formula instead of a formula to copy down.
In F2, write one ARRAYFORMULA that multiplies Units (column D) by Unit price
(column E) for every row from row 2 down. Leave the cell empty when column D
is empty. Column F has the header "Revenue" in F1.The formula that meets that request is the following, typed in F2 with the header Revenue entered by hand in F1.
=ARRAYFORMULA(IF(D2:D="", "", D2:D*E2:E))The second task is a summary table of October revenue by region, largest first. The prompt states the date window as a range of dates, which leads toward date literals rather than a month number.
In H1, write a QUERY over A1:F13 (1 header row) that returns each Region and
the sum of Revenue for orders dated from October 1, 2026 up to but not
including November 1, 2026, sorted by revenue from highest to lowest.
Label the sum column "October revenue".=QUERY(A1:F13, "select B, sum(F) where A >= date '2026-10-01' and A < date '2026-11-01' group by B order by sum(F) desc label sum(F) 'October revenue'", 1)A third formula ranks the three reps with the most October revenue, using the same filter with limit, which the clause order places after order by and before label.
=QUERY(A1:F13, "select C, sum(F) where A >= date '2026-10-01' and A < date '2026-11-01' group by C order by sum(F) desc limit 3 label sum(F) 'Revenue'", 1)··········
THE CORRECT RESULTS TO CHECK GEMINI'S FORMULAS AGAINST.
The figures below were computed in Python with pandas from the twelve rows above, and the regional total was cross-checked with an equivalent SQL statement in SQLite. The revenue column must add up to 12,405.00 across all twelve rows. The October window contains nine orders worth 9,785.00 in total.
........
Region | October revenue |
West | 3,495.00 |
Central | 3,380.00 |
East | 2,910.00 |
........
The top-three query must return Brooks with 2,750.00, Patel with 1,980.00 and Nguyen with 1,860.00. Chen, at 1,635.00, is the fourth rep and must not appear.
These numbers also identify the common failure modes. A query that drops the where clause entirely returns West 4,735.00, East 4,290.00 and Central 3,380.00, which changes the order of the regions as well as the amounts. A query that filters with where month(A) = 10, the intuitive way to ask for October, selects November because month() counts from zero, and returns only West 1,240.00 and East 840.00. A query that compares column A with the bare string '2026-10-01' instead of date '2026-10-01' sets a date column against a text value, which departs from the date literal syntax the Query Language reference requires. Each of these errors produces a tidy two-column table, which is why the expected totals have to be known before the formula is accepted.
........
Formula variant | Rows used | Result |
Correct date-literal filter | 9 | West 3,495 / Central 3,380 / East 2,910 |
No where clause | 12 | West 4,735 / East 4,290 / Central 3,380 |
where month(A) = 10 | 2 | West 1,240 / East 840 |
........
··········
REVIEWING GEMINI'S QUERY AND ARRAYFORMULA OUTPUT BEFORE USE.
Gemini's help page states that its output may be inaccurate, and a wrong Sheets formula often shows no visible symptom. A short review routine catches most of them. Read the query string clause by clause against the documented order, confirm that every date comparison uses the date keyword, and check that the select list contains only grouped columns and aggregates. Compare one known total, such as the 9,785.00 October figure here, with a plain SUMIFS or a filtered sum before relying on the grouped table.
For ARRAYFORMULA, the blank-row guard is the first thing to inspect. Without IF(D2:D="", "", …), the multiplication returns 0 for every empty row to the bottom of the sheet, which inflates COUNT results and makes the column look populated. Open ranges such as D2:D also mean that a stray value typed far below the data becomes part of the calculation. When the QUERY reads a column produced by ARRAYFORMULA, the empty strings returned for blank rows are a minority type in a numeric column, so the documented majority rule treats them as nulls and the sums stay correct; a column where text outnumbers numbers would behave differently.
The Fix button and the follow-up correction feature help with formulas that return an error, but a formula that returns a wrong number raises no error to fix. Retry can generate an alternative version, and Google's September 2025 update describes multiple options for the same task, so comparing two generated formulas against the expected result is a quick way to expose a filter mistake. Pivot tables built by Gemini need the same discipline, described in the site's guide on building pivot tables with Gemini in Google Sheets and checking totals.
··········
WHEN A QUERY FORMULA IS THE WRONG TOOL IN GOOGLE SHEETS.
QUERY works on ranges inside the spreadsheet. For data stored in BigQuery, Google documents a separate path in which Gemini runs read-only queries against BigQuery from Sheets, which requires BigQuery access and a Google Cloud billing project; the site covered the general availability of conversational analytics in Connected Sheets earlier this month. For row-by-row text tasks such as classification or summarization, the AI function places a generative result in each cell, as explained in the site's article on the AI function in Google Sheets. Formulas remain the better choice when the result must be deterministic and recalculate as data changes.
··········
GEMINI FORMULAS IN GOOGLE SHEETS: WHAT TO CONFIRM IN YOUR ACCOUNT.
Google documents the essentials: formula generation from the Ask Gemini panel or the in-cell shortcut, insertion with Insert and Retry, error repair with Fix, explanations and multiple formula options since September 2025, and a side-panel history that does not survive a reload. Eligibility depends on the Workspace or Google AI plan and on the administrator enabling smart features and personalization, so access can differ between two users of the same organization. Google does not publish a data size limit for Gemini in Sheets on its help page.
The QUERY and ARRAYFORMULA rules come from Google's own function documentation and do not change with the plan: fixed clause order, date literals in yyyy-MM-dd format, zero-based month(), grouped columns or aggregates only, and majority typing per column. With the expected figures computed in advance, a generated formula can be accepted or rejected in seconds, and in this example the correct October table reads West 3,495.00, Central 3,380.00 and East 2,910.00.
··········
FOLLOW US FOR MORE.
·····
DATA STUDIOS
·····
datastudios.org
