Skip to content
Reporting and Analysis

Write and Fix Spreadsheet Formulas with Gemini in Google Sheets

View source

What the source describes

Google's Docs Editors Help explains that Gemini in Google Sheets can create tables and formulas, generate data analysis and insights, build charts, and perform actions such as creating pivot tables, applying conditional formatting, sorting, filtering and find and replace. Users open the Ask Gemini side panel, describe the formula they need with references to their sheet, and insert or retry the suggestion. When a formula shows an error, users can hover over the cell and click Fix so Gemini reviews the formula and analyzes the issue.

Result or outcome

According to the source, Gemini can generate formulas from plain-language prompts that reference the sheet and can review and help fix formulas that return errors; the page notes it works best with native Google Sheets files rather than .xlsx files.

How to run it

  1. Open the client transaction file in Google Sheets (Gemini in Sheets needs a Google Workspace plan that includes Gemini), and if it is an .xlsx file, save it as a Google Sheets file first.
  2. Click Ask Gemini at the top right to open the side panel, and describe each formula using your sheet's column and cell references.
  3. Paste the prompt on this page into the side panel, then select the target cell and click Insert, or click Retry for another version.
  4. When a formula returns an error, hover over the cell and click Fix so Gemini reviews the formula and analyzes the issue.
  5. Test each formula on a few rows you can check by hand before filling it down the whole column.

Try it yourself

Copyable prompt

I am working in a spreadsheet of client transactions with columns A [Date], B [Vendor], C [Description], D [Amount], E [Account]. Write a formula for column F that returns the month and year from column A as text like Mar-2026. Write a formula for column G that returns Yes if the same vendor and amount appear more than once within [7] days, otherwise No. Then write a SUMIFS formula on a summary tab that totals column D by account in column E for a month entered in cell [B1]. For each formula, explain in one sentence what it does, and tell me how to fix it if it returns an error such as #VALUE! or #N/A.

Replace the [bracketed] placeholders. Remove client names, account numbers, and personal data before pasting into any AI tool.

What to review

Check the SUMIFS result for a sample month against the same total in the ledger, and confirm each formula's ranges cover every row of data. Test the duplicate flag on a known repeat payment and a known one-off charge. Review any fix Gemini proposes before accepting it, since a formula without an error can still return the wrong number.

Related free download: 50 AI Prompts for Bookkeepers (Prompt pack (PDF))

Source: Google Docs Editors Help

See also

Not professional advice. This is general information, not accounting, tax, legal or financial advice. Check AI output yourself and consult a licensed professional for your situation. See our Terms of Use.

This entry summarizes a public source. Features, plans, and availability change, so confirm details with the vendor. Tool names are trademarks of their respective owners.

Related workflows

  • Reporting and Analysis

    Build a Budget vs Actual Report in Google Sheets with Gemini

    Google's help page for Gemini in Google Sheets says Gemini can create tables and formulas, generate data analysis and insights, build charts, and perform actions such as conditional formatting, pivot tables, filters and number formats, each shown on a preview card you apply or undo. It works best with native Google Sheets files, so Google says to save an Excel file as Google Sheets first. Inserted charts go on a new tab with their data and do not change when the original data changes, and the conversation history is lost when you reload or reopen the file. The feature requires an eligible Google Workspace or Google AI plan.

    Google Sheets, Google Gemini

    Google Docs Editors Help (Collaborate with Gemini in Google Sheets)
  • Reporting and Analysis

    Draft a Client Financial Health Review from an Income Statement

    CPA Practice Advisor walks through pasting an anonymized landscaping company's income statement into ChatGPT and Gemini Advanced and asking how financially healthy the business is and what to recommend. Both tools identified the net loss, flagged salaries as a large share of expenses, and suggested cost and revenue actions. The author stresses anonymizing data, getting client permission, having an expert check every answer, and reviewing terms of service.

    Works in ChatGPT, Claude, Grok, Gemini

    CPA Practice Advisor
  • Reporting and Analysis

    Benchmark a Client P&L Against Industry Norms by NAICS

    This TechNews.Tax newsletter post describes how a tax firm reviews Profit and Loss statements from clients who do their own books, using an AI prompt that compares the P&L to typical expense categories and percentages for the client's NAICS code. The prompt asks for a NAICS summary, a category and percentage comparison, a judgment on whether expenses are in line (within a 10% variance), and a risk explanation for outliers. The author uses the OpenAI API but notes the same process works in ChatGPT, Gemini, Copilot or similar tools.

    Works in ChatGPT, Claude, Grok, Gemini

    TechNews.Tax (newsletter)
  • Reporting and Analysis

    Write Bookkeeping Excel Formulas with ChatGPT

    OpenAI's help article on data analysis says ChatGPT can analyze uploaded spreadsheets, including .xls, .xlsx and .csv files, answer questions about the data and create tables or charts. It recommends descriptive column headers in the first row, one row per record, and no empty rows or columns splitting the data. For some tasks ChatGPT writes and runs Python code, and OpenAI says to review the generated code, outputs and assumptions before relying on the result. The article also notes that the number and complexity of formulas in a spreadsheet can affect processing.

    ChatGPT, Microsoft Excel

    OpenAI Help Center (Data analysis with ChatGPT)

If you want somewhere to check the numbers from this report or analysis, the Small Business Bookkeeping Spreadsheet Bundle is on our sister store.