Google Sheets AI: 25 Gemini Prompts for Formulas, Cleanup & Analysis
Most people use about five percent of what Google Sheets can do, and the other ninety-five percent is locked behind formulas they can't remember and cleanup work they never get around to. Gemini in Google Sheets changes the math: you describe what you want in plain English, and it writes the formula, tidies the data, or tells you what the numbers say. This is a working prompt library — 25 prompts you can copy straight into the Gemini side panel or the =AI() function today.
If you want the feature-by-feature overview of Gemini across Docs and Sheets, read our guide to Gemini in Google Docs & Sheets first. This post is narrower and more practical: it assumes you already know Gemini exists in Sheets, and it hands you the exact wording that gets useful results. Every prompt includes a one-line note on what it does and when to reach for it.
One caveat up front: Google ships changes to Gemini regularly. The prompts below work against capabilities that have been stable since Gemini was bundled into Workspace Business and Enterprise plans in January 2025, but always verify current features and limits in your own account and on Google's official documentation.
How to open Gemini in Google Sheets
There are two ways to use Google Sheets AI, and the prompts in this post are split between them:
- The Gemini side panel — open any spreadsheet and click the Gemini icon (the sparkle) in the top-right corner. A panel opens where you can type questions about your sheet, ask for formulas, request summaries, and generate charts. This is where most of the prompts below go.
- The
=AI()function — type it into a cell like any other formula. It runs a Gemini prompt over a cell or range and returns the result in place, so you can fill it down a column. The syntax is=AI("your prompt", A2).
You'll also see “Help me organize” in the side panel, which builds a structured table or tracker from a description — useful when you're starting from a blank sheet rather than cleaning up an existing one.
Gemini in Sheets is included in Google Workspace Business Starter, Business Standard, Business Plus, and Enterprise plans. If your side panel doesn't show it, you're most likely on a personal Gmail account or a legacy plan. See our Google Workspace Gemini overview for the plan details.
Formula prompts (7)
This is the single most useful thing Gemini does in Sheets. Describe the calculation, and it writes the formula — including the functions most people never learn, like SUMIFS, XLOOKUP, and ARRAYFORMULA. Gemini reads your header row to figure out which columns you mean, so name your columns clearly before you ask.
Write a formula that sums the Amount column where Status is "Paid" and Date is in the current month.— Produces a SUMIFS with a date range condition. The most common “how much did I actually collect” question.Write a formula that looks up the Client Name from the Clients tab using the Client ID in column B.— Generates a cross-sheet lookup, usually XLOOKUP or VLOOKUP. Saves you from memorizing the argument order.Create a formula that calculates days overdue: today minus the Due Date, but only if Status is not "Paid", otherwise return blank.— Wraps a date calculation in an IF so paid invoices don't show negative numbers.Write an ARRAYFORMULA that fills the Total column for every row as Quantity times Unit Price, skipping empty rows.— One formula in the header that auto-fills the whole column, so you don't drag formulas down after every new entry.Write a formula that returns the top 5 products by Revenue along with their revenue amounts.— Uses SORTN or a SORT plus ARRAY_CONSTRAIN combination. Instant leaderboard without a pivot table.Write a formula that counts how many unique clients appear in column C.— Typically COUNTA(UNIQUE(...)) with a blank filter. Answers “how many customers do I really have.”Explain what this formula does step by step: =IFERROR(INDEX(B:B, MATCH(E2, A:A, 0)), "Not found")— Reverse mode: paste any formula you inherited and get a plain-English breakdown. Essential when you take over someone else's sheet.
Always spot-check the result. Gemini is very good at formulas, but it can misread a header, pick the wrong tab, or use a closed range where you wanted an open one. Test the formula against two or three rows where you already know the answer before you trust it across a thousand.
Cleaning & organizing prompts (6)
Messy data is the tax you pay on every export from a payment processor, CRM, or web form. Gemini in Sheets can suggest and apply cleanup steps — trimming spaces, splitting names, standardizing dates, and removing duplicates — without you writing a single SPLIT or TRIM function.
Find and remove duplicate rows based on the Email column, keeping the most recent entry by Date.— Deduplicates on one key while keeping the newest row. Standard cleanup before any email or mailing list export.Split the Full Name column into separate First Name and Last Name columns.— Handles the most common form-export problem. Check rows with middle names or two-word surnames afterward.Standardize all dates in column D to YYYY-MM-DD format and flag any cells that aren't valid dates.— Fixes the mix of 3/4/26, March 4, and text dates that breaks sorting and date math.Trim leading and trailing spaces from every text cell and convert the Category column to Title Case.— Invisible spaces are the number one reason lookups return “not found.” This kills them.Help me organize: build a freelance invoice tracker with columns for Invoice #, Client, Issue Date, Due Date, Amount, Status, and Days Overdue, with a dropdown for Status.— Uses the “Help me organize” feature to generate a structured table from scratch, with data validation already set.Identify any rows where Amount is blank, zero, or negative, and list their row numbers.— Surfaces data-entry errors before they corrupt your totals. Run it before every month-end report.
Analysis & insights prompts (6)
Once the data is clean, the side panel becomes an analyst. Ask questions about your sheet and Gemini reads the data, spots trends and anomalies, and produces pivot-style summaries in plain language. Be specific about which columns and which time period you mean.
Summarize this sheet in three bullet points: total revenue, the biggest client by revenue, and the month with the highest sales.— The 30-second briefing. Good for pasting into a client update or a status email.Show me monthly revenue totals for the last 12 months and tell me which months were above and below the average.— A pivot-style monthly rollup with a trend read. No pivot table required.Are there any anomalies or outliers in the Amount column? List the rows and explain why they stand out.— Catches the $5,000 invoice that should have been $500, or the refund entered as a positive number.Which clients have unpaid invoices older than 30 days, and what is the total outstanding for each?— Your accounts-receivable aging report, generated from a plain-language question.Compare Q2 and Q3 sales by product category and tell me which categories grew or shrank by more than 10%.— Period-over-period comparison with a materiality threshold, so you only see the changes that matter.Based on the last six months of data, what is the average number of days it takes each client to pay an invoice?— Payment-behavior analysis. Tells you who to invoice early and who needs a deposit next time.
=AI() function prompts (4)
The =AI() function is different from the side panel: it runs a Gemini prompt per row and returns the answer in the cell. Write it once, fill it down, and you've classified, summarized, or extracted from hundreds of rows. Each example below assumes the source text is in column A starting at row 2.
=AI("Classify this customer feedback as Positive, Neutral, or Negative. Reply with one word only.", A2)— Sentiment tagging for survey responses, reviews, or support tickets. The “one word only” keeps the output consistent enough to filter and count.=AI("Summarize this meeting note in one sentence of 15 words or fewer.", A2)— Turns a column of rambling notes into a scannable digest. Useful for CRM activity logs.=AI("Extract the company name from this email signature. If there is none, reply with N/A.", A2)— Pulls structured data out of unstructured text. Works for phone numbers, cities, product names, or anything else you can describe.=AI("Write a friendly two-sentence payment reminder addressed to this client by first name, mentioning that their invoice is overdue.", B2)— Generates per-row text you can paste into email or a mail merge. Personalized without being manual.
Two things to know about =AI(). First, it has usage limits — you can't run it over an unlimited number of cells at once, so batch large jobs and check Google's current quotas. Second, the output is generated text, not a deterministic formula. Review it before you act on it, especially for anything customer-facing like the reminder example above.
Charts & reporting prompts (2)
The side panel can also generate charts directly from a description. You tell it what to plot; it picks a sensible chart type and inserts it. You can then adjust it like any other Sheets chart.
Create a column chart of total revenue by month for 2026, with months in order on the x-axis.— The default monthly performance chart. Specifying the axis order avoids alphabetical months (April, August, December…).Create a pie chart showing the share of total revenue by client, grouping any client under 5% into "Other".— Shows client concentration at a glance. If one slice is over half the pie, you have a dependency problem worth knowing about.
Worked example: a freelancer's invoice tracker in 10 minutes
Here's how the prompts chain together on a real task. Say you're a freelance designer with a year of invoices exported from your payment tool into a fresh sheet: columns for Invoice #, Client, Issue Date, Due Date, Amount, and Status, and the usual export mess.
- Clean first. Open the side panel and run the standardize-dates prompt on the Issue Date and Due Date columns, then the trim-and-Title-Case prompt on Client. Follow with the duplicate check on Invoice #. Three prompts, two minutes.
- Add the calculated column. Ask for the days-overdue formula. Gemini returns something like
=IF(F2<>"Paid", TODAY()-D2, ""). Spot-check it against one paid and one unpaid row, then ask Gemini to convert it to an ARRAYFORMULA so new rows fill automatically. - Get the totals. Run the SUMIFS prompt for revenue collected this month, and the “unpaid invoices older than 30 days by client” analysis prompt. You now have cash-in and cash-owed without touching a pivot table.
- Add per-row text. In a new column, use the
=AI()payment-reminder prompt on the overdue rows only. Review the wording, then paste into Gmail — where Gemini in Gmail can polish the tone if needed. - Chart it. Ask for the revenue-by-month column chart and the revenue-by-client pie. Drop both into a Google Doc for your quarterly review.
The same sequence works for a small-business sales sheet — swap Client for Product and Amount for Sales, and the prompts barely change. For a broader look at how small businesses use the whole suite, see our Google Workspace for small business guide.
Tips for better results
- Keep a clean header row. Gemini uses your headers to understand the sheet. “Amount” beats “Amt (USD) *final*”, and a single header row beats merged cells and title banners. This is the single biggest factor in accuracy.
- Name the columns in your prompt. “Sum the Amount column where Status is Paid” gets a better formula than “add up the paid ones.”
- Constrain the output. For
=AI(), say “reply with one word,” “15 words or fewer,” or “YYYY-MM-DD format.” Unconstrained answers are hard to filter, sort, or count. - Ask for an explanation. Add “and explain the formula” to any formula prompt. You'll learn the function and catch mistakes at the same time.
- Set custom instructions once. As of September 2026, the Gemini side panel supports persistent custom instructions across Workspace apps, including Sheets. Tell it your preferred date format, currency, and tone, and it stops asking. Verify availability in your account, as this is rolling out.
- Work on a copy. For cleanup operations that rewrite data, duplicate the tab first. Undo works, but a copy is cheaper than finding out three steps later that a split went wrong.
Limits and things to watch
Google Sheets AI is genuinely useful, and it is not magic. Three limits come up constantly:
- Messy headers confuse it. Multiple header rows, merged cells, or headers that don't match the data underneath lead to formulas pointing at the wrong column. Fix the structure before you prompt.
- Formulas need a spot-check. Treat every generated formula as a first draft from a capable colleague. Verify against known values, and watch for closed ranges (A2:A100) where you needed an open one (A2:A).
=AI()has quotas and non-deterministic output. There are usage limits on how many cells you can process, and the same prompt can return slightly different text on re-run. Fine for classification and drafting; not something to build a financial model on.
Which plans include Gemini in Sheets
Gemini in Sheets — the side panel, formula generation, Help me organize, and the =AI() function — is included in Google Workspace Business Starter, Business Standard, Business Plus, and the Enterprise tiers. There is no separate AI add-on to buy. Full plan details are on our pricing page.
For comparison, Microsoft 365 Copilot in Excel is a paid add-on on top of a Microsoft 365 subscription — roughly $30 per user per month as of 2026. For a five-person team, that gap is about $1,800 a year for capabilities that Sheets bundles into a plan that starts under $10 per user per month. As always, verify current pricing on both vendors' official pages before you decide.
Frequently asked questions
Is Google Sheets AI free?
Gemini in Google Sheets is included at no extra cost with Google Workspace Business Starter, Business Standard, Business Plus, and Enterprise plans — there is no separate AI add-on. It is not part of a free personal Gmail account. Verify current availability for your plan on Google’s official Workspace pages.
What is the =AI() function in Google Sheets?
=AI() is a Sheets function that runs a Gemini prompt over a cell or range and returns the result in the cell, for example =AI("Classify this feedback as positive, neutral, or negative", A2). It is useful for categorizing, summarizing, extracting, and generating text row by row, and it fills down like any formula. It has usage limits and produces generated text, so review results before relying on them.
Can Gemini write formulas in Google Sheets?
Yes. Describe the calculation in plain English in the Gemini side panel — for example, "sum the Amount column where Status is Paid" — and Gemini writes the formula, including functions like SUMIFS, VLOOKUP, XLOOKUP, and ARRAYFORMULA. Always spot-check the result against a few rows you know the answer for.
Why does Gemini pick the wrong column in my sheet?
Almost always because of the header row. Gemini reads your headers to understand the data, so merged cells, multiple header rows, blank headers, or headers that do not match the data beneath them lead it astray. Use a single, clean header row with short descriptive names, and name the columns explicitly in your prompt.
How is Gemini in Sheets different from Copilot in Excel?
Functionally they overlap — both generate formulas, clean data, and analyze tables from plain-language prompts. The main difference is cost: Gemini is bundled into Workspace Business and Enterprise plans, while Microsoft 365 Copilot in Excel is a paid add-on of roughly $30 per user per month as of 2026. Check both vendors’ official pricing before deciding.
Does Gemini see my spreadsheet data?
Gemini reads the sheet you are working in to answer questions and generate formulas. Under Google Workspace terms, customer data in Workspace is not used to train Gemini models without permission, and Workspace data-protection commitments apply. Review Google’s current Workspace privacy documentation for the specifics that apply to your plan and region.
The bottom line
The value of Google Sheets AI isn't that it does anything a spreadsheet expert couldn't. It's that it makes the expert moves available to everyone else — the SUMIFS you never learned, the cleanup you kept postponing, the aging report you assembled by hand every month. Keep your headers clean, spot-check the formulas, and these 25 prompts will pay for the Workspace subscription many times over in saved hours.
Related reading: Gemini in Google Docs & Sheets · Gemini in Gmail · Google Workspace Gemini Overview · Google Workspace Pricing