Gemini in Google Sheets Suggests the Wrong Formula? Check the Inputs Before Sharing

A formula can look tidy and still answer the wrong question.

This guide covers a common reporting failure: Gemini suggests a percentage formula, but the denominator does not match the business definition. You will use a small fictional KPI table to inspect the suggestion, check one example by hand, and decide whether the formula is safe to use in a draft report.

The evidence label is documentation-based. Google documents formula generation, the Ask Gemini side panel, and the option to inspect analysis steps in Sheets. The table and expected outputs below are illustrative. No Gemini run is claimed.

Before you start

You need:

A desktop browser with Google Sheets.
An eligible Google Workspace or Google AI plan with Gemini in Sheets available. Access varies by account and plan.
A blank practice spreadsheet. Do not use customer, employee, financial, or other sensitive data for this exercise.
A calculator or a separate cell for the manual check.

Gemini in Sheets works best with a native Google Sheets file. If your source is an .xlsx file, open it in Google Sheets and choose File > Save as Google Sheets before you continue.

If Ask Gemini is not visible, stop at the access check. This workflow does not assume that every account has the feature.

Fictional input

Create a sheet named Weekly KPIs and paste this CSV into cell A1:

week,leads,qualified_leads,opportunities,won_deals
2026-10-05,40,10,4,1
2026-10-12,50,15,5,2
2026-10-19,60,12,6,1

The reporting question is: “What percentage of leads became qualified leads each week?”

The intended definition is:

qualified_leads / leads

For the first row, the hand-checked answer is 10 / 40 = 25%.

Three steps

1. Inspect the source before asking for a formula

Check the sheet before opening Gemini:

The header row is in A1:E1.
The three data rows are in A2:E4.
leads is the denominator for the requested KPI.
qualified_leads is the numerator.
The other columns are present, but they are not part of this calculation.

Do not rename columns or delete extra columns just to make the prompt easier. The point is to see whether the suggested formula follows the stated definition.

2. Ask Gemini for a formula with a visible definition

At the top right of the sheet, click Ask Gemini. If the side panel opens, paste this prompt:

In the Weekly KPIs sheet, create a formula for a new column named qualification_rate.

Definition: qualification_rate = qualified_leads / leads.

Use the row values, not a total across all weeks. Format the result as a percentage. Return:
1. The proposed formula for row 2.
2. The cell references it uses.
3. A one-line explanation of the numerator and denominator.
4. One manual check using the first row.

Do not modify the existing data. Do not use opportunities or won_deals in this calculation. If the headers or range are unclear, say so instead of guessing.

If Sheets offers a plan or analysis steps, read them before accepting a formula. Keep the task read-only until the result passes the checks below. Do not insert the formula into a shared report yet.

Google documents formula generation through the Gemini side panel. The exact controls can vary by account and rollout. If your surface starts from a cell with =, use the same definition and review the proposed references before inserting anything.

3. Check the suggestion against the source

Write the proposed formula in a temporary note or an unused practice cell. Then check each row independently:

Row 2: 10 / 40 should be 25%.
Row 3: 15 / 50 should be 30%.
Row 4: 12 / 60 should be 20%.

Also inspect the references. A row formula should use the qualified_leads and leads cells from the same row. A formula that divides by opportunities, uses a fixed denominator, or sums all rows is answering a different question.

If the result is correct, add a new column named qualification_rate in the practice copy and insert the reviewed formula. Keep the source columns unchanged.

Illustrative output

A formula that matches the definition could be:

=C2/B2

Filled down, the expected values are:

25%
30%
20%

This is an editor-written expected result, not a formula generated or executed by Gemini.

A result such as 10%, 20%, and 16.67% may be mathematically valid for a different denominator. It does not pass this task unless the references and the stated KPI definition match.

Acceptance checks

The review is complete when all of these are true:

The formula uses qualified_leads as the numerator.
The formula uses leads from the same row as the denominator.
Row 2 checks to 25% by hand.
Row 3 checks to 30% by hand.
Row 4 checks to 20% by hand.
The existing source columns are unchanged.
No formula is copied into a shared report before these checks pass.

A plausible percentage is not enough. The references are part of the answer.

If the suggestion is wrong

The formula uses the wrong column. Restate the numerator and denominator in words, include the header names, and ask Gemini to return the references before inserting anything.

The formula returns a decimal. Format the temporary result as a percentage in Sheets, then check the underlying decimal. Formatting changes how a result is shown, not what it calculates.

Gemini uses a total across all weeks. Say “same-row calculation” and provide the exact range B2:C4 only if your sheet uses those columns. Recheck the formula after the change.

Ask Gemini is missing. Check the account plan and Workspace administrator settings. Google lists an eligible Workspace or Google AI plan as a prerequisite, and feature availability can vary.

The sheet is an Excel file. Save a copy as a native Google Sheets file, then repeat the access and reference checks. Keep the original file unchanged.

Limits and privacy

This workflow checks a simple ratio. It does not validate a KPI definition for your organization, reconcile source-system logic, or approve a consequential report. Use approved practice data and ask the metric owner to confirm the definition before publishing a business result.

Evidence label

Documentation-based troubleshooting guide with complete fictional KPI data and an illustrative formula and output. No Gemini execution, screenshot, measured result, or personal test is claimed.

Sources

Next lesson

If Gemini cannot see the intended range, read Gemini in Google Sheets Cannot See Your Data? Fix the Table First. It covers table boundaries and mixed ranges.

One useful idea for your workweek: Get The Weekly Verdict.