How to Use Gemini to Write and Fix Google Sheets Formulas

New to the AI that comes built into Google's apps? Our free short course, AI in the Tools You Already Have, shows where Gemini sits in Gmail, Docs and Sheets, and every lesson is readable with no account: start learning.

Which plans include Gemini in Google Sheets

Gemini in Sheets is a paid feature. Google's help page for it says you need an eligible Google Workspace or Google AI plan, and the dividing line falls in a different place for work and personal accounts. The US prices below come from our Gemini in Google Workspace page, which read them from Google's help centre on 23 September 2026.

Workspace comes with a 14-day free trial, enough time to run the steps below on a copy of a real sheet. Business Starter is the plan to double-check: Google's pricing table leaves Sheets out of it, while Google's help-centre edition comparison suggests Business Starter has some Gemini access in Docs, Sheets and Meet, so confirm the Ask Gemini button appears before you buy seats for a team.

On a personal account, our Google Gemini page lists Gemini in Gmail and Vids for Google AI Plus, and Gemini in Gmail and Docs for Google AI Pro. Google's own AI plans page names Docs and Sheets among the apps Gemini helps with, without tying Sheets to one tier. Gemini in Google apps on these plans is limited to people aged 18 and over and to select languages.

Three ways to ask Gemini for a formula

The first two write a new formula and the third repairs one. Either way, Gemini works inside the file rather than in a separate chat window. Google's help page lists summarising the spreadsheet and analysing its data among the things Gemini in Sheets can do, so you do not have to copy rows out to explain your layout. Naming the ranges in your request still gets a better first answer.

Step 1: Pick a test range you can check by hand

Take a small invoice tab. Row 1 holds the headers. Column A has the invoice number, B the client, C the due date, D the amount, and E says Yes or No for paid. The records run from row 2 to row 7. The job is one figure in H2: the total owed on unpaid invoices that are already past their due date.

Work out that figure yourself before you ask Gemini anything. Suppose rows 3 and 6 are unpaid and overdue, row 5 is unpaid but not due until next month, and the other three are paid. The right total is D3 plus D6, and row 5 must stay out of it. Note that expectation somewhere; every version of the formula has to match it.

Step 2: Write the prompt with the sheet's own references

Google's help page says Gemini takes prompts written with real sheet references or with plain names for cells. Real references leave it less to guess. A request that suits the invoice tab:

Write a formula for H2. Invoices are in rows 2 to 7. Column C has the due date, D the amount, and E is Yes or No for paid. Add the amounts in D where E is No and the date in C is earlier than today. Explain what each range does.

A correct answer looks like this:

=SUMIFS(D2:D7,E2:E7,"No",C2:C7,"<"&TODAY())

SUMIFS adds up the amount range only on rows where every condition holds: E reads No, and the date in C falls before today. All three ranges cover the same six rows, which keeps each amount lined up with its own status and due date. If Gemini hands back ranges of different lengths, or a column you never mentioned, reply with the correction and let it rewrite the formula.

Two details make the request sharper. Say whether you want one total in a single cell or a result on every row, because those are different formulas. And if the tab holds more than one table, name the one you mean, such as "the invoice table in A1:E7". Google's help page recommends pointing Gemini at a specific table when a sheet contains several.

Step 3: Insert the formula into the selected cell

Click H2 first, then click Insert under Gemini's answer, and the formula goes into that cell. If the suggestion misses what you asked for, Retry produces another version. If you close the panel before inserting, the chat is not gone: Google now keeps side-panel history, and you reopen past chats from Ask Gemini > More options > History, for as long as your admin's retention setting allows.

Step 4: Check the answer against the rows you counted

Sheets accepting a formula only proves the syntax is valid; it says nothing about whether the formula answers your question. Type =D3+D6 in a spare cell and compare it with H2. Then click H2 and read the formula bar: every range should begin on row 2 and end on row 7.

TODAY() makes testing slippery, because the total shifts as the days pass and an invoice that was not due yesterday can be overdue now. While you test, put a fixed date in a spare cell such as J1 and ask Gemini for the same formula compared against J1 instead of today. Switch back to TODAY() once the check passes.

Then try the edges. Move row 5's due date to the day before J1 and confirm the total rises by D5. Mark row 3 as paid and confirm it falls by D3. A formula that moves the right way on both edits is ready for the full tab. Extend every range to the last row of data, and end them all on the same row.

Fix a formula that shows an error

For a formula that already returns an error, hover over the cell and click Fix. Gemini reviews the formula and analyses what went wrong, and Stop in the side panel cancels it if the attempt is heading somewhere unhelpful. Read the explanation and the corrected formula before you move on; they tell you whether the fix dealt with the real cause or only hid the symptom.

Here is a typical case. A second tab looks up each client's credit limit with =VLOOKUP(B2,Clients!A2:C40,4,FALSE) and shows #REF!. The culprit is the 4. VLOOKUP counts columns from the first column of its range, and A to C is only three columns wide, so Sheets reports that the lookup evaluates to an out-of-bounds range. The repair is either to widen the range to A2:D40, if the limit sits in column D, or to change the index to 3, if it sits in column C. Only you know which column holds the limit, so correct Gemini if its fix picks the wrong one.

An error message is the easy case. The harder one is a formula that shows no error but gives the wrong total, where there is no Fix button to click. Ask in the side panel instead, and give Gemini the formula, the figure you expected and the figure you got. Two causes turn up often:

• Dates stored as text. Sheets keeps a real date as a number, so =ISNUMBER(C3) returns TRUE for one. FALSE means C3 holds text that only looks like a date, and the "<"&TODAY() condition passes over it.
• Stray spaces. A status typed as "No " with a trailing space is not the same as "No", so that invoice drops out of the total. =LEN(E3) reveals the extra character, and TRIM removes it.

Formatting traps like these also catch lookups between two tabs. Our guide to comparing two spreadsheets for missing records shows how an ID that lost its leading zero can pass for a missing row.

Convert an Excel formula to Google Sheets syntax

Common functions such as SUMIFS and VLOOKUP take their arguments in the same order in Excel and in Sheets, so a formula copied from Excel often works as it is. Three things tend to break it.

The file is still an .xlsx. Google's help page says Gemini in Sheets works best with native Sheets files, and an Excel file has to be saved as a Google Sheets file first. Use File > Save as Google Sheets, then work in the new copy.

The separator does not match the locale. Spreadsheets set up for many European locales separate arguments with semicolons, so a colleague's formula may arrive as =SUMIFS(D2:D7;E2:E7;"No"). A sheet set to the United States locale expects commas and reports a parse error. The locale is under File > Settings; check it before you retype a single formula.

It points at another workbook. An Excel link such as ='[Budget.xlsx]Q3'!B4 means nothing to Sheets. Data from another spreadsheet comes in through IMPORTRANGE, for example =IMPORTRANGE("spreadsheet URL","Q3!B4"). The first time, Sheets asks you to allow access, and the cell shows #REF! until someone does.

A prompt that handles all three:

This formula came from Excel: [paste it]. Rewrite it for this Google Sheets file, keeping the same ranges. Tell me if any function, separator or reference behaves differently in Sheets.

Put the converted formula through the same test as before: the sample rows, the figure you worked out by hand, and a look at the formula bar.

No Gemini in Sheets? Use the Gemini app as a separate chat

Without an eligible plan, the free Gemini app can still suggest a formula, running on the Flash-Lite model. It is a separate chat rather than a panel inside the file, so you describe the layout yourself, the way Step 2 does, and paste the answer into the cell. The routine matches our guide to using ChatGPT to write and fix an Excel formula, with Sheets in place of Excel, and the checks in Step 4 apply unchanged.

Keep the checking habit

Gemini can write the formula, but deciding whether it answers your question stays with you, and that gets faster with practice. Start learning with our courses, where every lesson is readable with no account, including the short course on Gemini in Google Workspace and Copilot in Microsoft 365.

Sources consulted

• Google Docs Editors Help (support.google.com)
• Google Workspace (workspace.google.com)
• Google One (one.google.com)