How to Use AI in Google Sheets (Practical Guide)

Last week I had a spreadsheet with 3,000 rows of messy sales data. Different date formats, inconsistent product names (“Tshirt” vs “T-Shirt” vs “t shirt”), and a column where someone had mixed currencies with plain numbers. Cleaning it up manually would have taken me two hours, easy.

I did it in about 15 minutes using a mix of Gemini’s side panel and a few ChatGPT-generated formulas. Not because AI is magic, but because the repetitive stuff — pattern matching, formula writing, categorization — is exactly what these tools are good at.

Here is everything I have learned about using AI in Google Sheets, including what actually works, what does not, and how to get the best results.

Gemini in Google Sheets: What It Can Actually Do

Google’s built-in AI sits right inside Sheets now. Click the sparkle icon in the top-right corner, and you get a Gemini side panel where you can type requests in plain English.

Here is what Gemini handles well:

• Generate formulas from descriptions (“Calculate the percentage change between column B and column C”)
• Create tables from scratch (“Make me a monthly budget tracker with categories for rent, food, transport, and savings”)
• Build pivot tables and apply filters from a single prompt
• Add conditional formatting (“Highlight cells in column D that are below 50 in red”)
• Summarize data without you writing a single QUERY function
• Find and replace across your sheet with natural language instructions

Since October 2025, Gemini can handle multi-step tasks from a single prompt. So instead of asking it to sort your data first, then add a filter, then format it as a table, you can say all of that at once.

How to Access It

For personal Google accounts, you need Google AI Pro (previously called Google One AI Premium) at $19.99/month. That gets you Gemini in Sheets, Docs, Slides, Gmail, and the Gemini app itself.

For work accounts, your admin needs Google Workspace Business Standard ($14/user/month) or higher.

A new feature rolling out in March 2026 is conversation history in the side panel, so you can pick up where you left off instead of re-explaining your data every time you open the sheet.

For a deeper look at what Gemini can do beyond Sheets, check out our Google Gemini review.

Quick Gemini Example

Say you have sales data in columns A through D (Date, Product, Quantity, Revenue). Open the Gemini panel and type:

“Create a pivot table that shows total revenue by product, sorted highest to lowest. Add it to a new sheet.”

Gemini will generate the pivot table, create the new sheet tab, and populate it. Takes about five seconds.

Want a chart too? Follow up with:

“Create a bar chart from that pivot table showing revenue by product.”

Done. Two prompts, maybe 20 seconds total, and you have a presentation-ready summary.

Using ChatGPT for Google Sheets Formulas

Here is the thing about Gemini: it is great when you are already inside Sheets. But sometimes you need a formula that Gemini struggles with, or you want to understand the logic before pasting it in. That is where ChatGPT comes in.

The workflow is simple: describe what you need in ChatGPT, copy the formula, paste it into your sheet. No add-on required.

The trick is being specific. Tell ChatGPT:

1. Which columns your data is in
2. What format the data is in
3. That you want a Google Sheets formula (not Excel — the syntax is slightly different)
4. What the output should look like

Prompt That Actually Works

Bad prompt:

“Write a formula to find duplicates”

Good prompt:

“I have a Google Sheets column A with email addresses from A2 to A3000. Write a formula for column B that shows TRUE if the email in that row appears more than once in column A, and FALSE otherwise. Use COUNTIF.”

ChatGPT’s response:

=COUNTIF($A$2:$A$3000, A2) > 1

Paste that in B2, drag it down, and you have your duplicates flagged. Simple, but the specificity is what makes it reliable.

A Harder Example

This one is from my actual messy data problem. I needed to standardize product names where people had typed them inconsistently:

“I have product names in column B of Google Sheets. The same product appears with different spellings like ‘T-Shirt’, ‘Tshirt’, ‘t shirt’, ‘T shirt’. Write a formula that normalizes all variations to a standard name. Put the formula in column E. Use nested REGEXMATCH and IFS functions.”

ChatGPT gave me:

=IFS(
  REGEXMATCH(LOWER(B2), "t[- ]?shirt"), "T-Shirt",
  REGEXMATCH(LOWER(B2), "hoodie|hoody"), "Hoodie",
  REGEXMATCH(LOWER(B2), "cap|hat|beanie"), "Cap",
  TRUE, B2
)

Not perfect out of the box — I had to add a few more product categories — but it gave me the structure in seconds. Writing that regex logic from scratch would have taken me much longer.

If you use ChatGPT for more than just Sheets, our ChatGPT guide covers the full range of what it can do.

If you work with Excel too, we have a dedicated piece on using ChatGPT for Excel with formula examples specific to that platform.

Best AI Add-Ons for Google Sheets

When Gemini and ChatGPT are not enough — especially for bulk operations or running AI directly inside cells — add-ons fill the gap.

1. GPT for Sheets (by DocGPT.AI)

This is the heavyweight. It lets you run AI prompts directly in your cells using custom functions, and it supports multiple LLMs including ChatGPT, Claude, Gemini, and Perplexity.

What makes it useful:

• Process up to 400 prompts per minute across 200,000 rows
• Use =GPT() as a cell function with your prompt as the argument
• Choose which AI model to use per task
• Bulk classify, summarize, extract, or generate content

Pricing: Starts at $29 for 29 million tokens (roughly enough for tens of thousands of simple prompts). Tokens expire after one year.

Example use case: You have 500 customer feedback entries in column A. In column B, enter:

=GPT("Classify this feedback as Positive, Negative, or Neutral: " & A2)

Drag it down, and in a few minutes you have all 500 entries categorized.

2. SheetAI

SheetAI takes a slightly different approach with its own set of custom functions:

• =SHEETAI("prompt") for general AI responses
• =SHEETAI_LIST("prompt") for generating lists
• =SHEETAI_FILL(range, pattern) for smart autocomplete based on patterns in your data
• =SHEETAI_BRAIN("question", source_range) for Q&A against your own data

Pricing: Free tier includes 5 formula generations and 50 function calls. Unlimited plan is $8/month or $72/year.

Best for: Quick in-cell AI tasks when you do not need industrial-scale bulk processing.

3. Coefficient

Coefficient is less about AI formulas and more about connecting live data sources to your sheet. It pulls data from Salesforce, HubSpot, Shopify, and 100+ other platforms directly into Sheets, then uses AI to help you analyze it.

Best for: Teams that need live business data in Sheets without manually exporting CSVs every week.

For more AI productivity tools beyond Sheets, our roundup of the best AI tools for office work covers the broader landscape.

5 Real Examples With Prompts

Here are actual tasks I have used AI for in Google Sheets, with the exact prompts and formulas.

Example 1: Clean Up Date Formats

The problem: Column A has dates in mixed formats — “03/15/2026”, “March 15, 2026”, “2026-03-15”, and “15-Mar-2026” all in the same column.

Gemini prompt:

“Convert all dates in column A to YYYY-MM-DD format. Put the clean dates in column F.”

Gemini handles this natively because it can parse the different formats visually. For a formula-based approach, ChatGPT gave me:

=TEXT(DATEVALUE(A2), "YYYY-MM-DD")

This works for most formats. For the really weird ones, I needed:

=TEXT(IF(ISNUMBER(A2), A2, DATEVALUE(A2)), "YYYY-MM-DD")

Example 2: Extract Domains From Email Addresses

The problem: 2,000 email addresses in column C, and I need just the domain names for analysis.

Formula (no AI needed, but ChatGPT wrote it for me in 3 seconds):

=REGEXEXTRACT(C2, "@(.+)")

Then to count how many emails per domain:

=QUERY(F2:F2000, "SELECT F, COUNT(F) GROUP BY F ORDER BY COUNT(F) DESC LABEL COUNT(F) 'Count'")

That QUERY function syntax is exactly the kind of thing that is painful to write from memory but takes ChatGPT about two seconds to generate.

Example 3: Categorize Expenses Automatically

The problem: A column of transaction descriptions from a bank export, and I need to sort them into categories like Food, Transport, Software, and Utilities.

GPT for Sheets formula:

=GPT("Categorize this bank transaction into exactly one of these categories: Food, Transport, Software, Utilities, Entertainment, Other. Transaction: " & B2)

Accuracy: About 85-90% correct. It misclassified some things (a payment to “Shell” went to Software instead of Transport), so I spot-checked and corrected maybe 30 out of 500 entries. Still way faster than doing all 500 manually.

Example 4: Generate Summary Descriptions

The problem: Product catalog with technical specs in columns B through G, but no human-readable descriptions for the website.

GPT for Sheets formula:

=GPT("Write a 2-sentence product description based on these specs. Keep it casual and helpful, no marketing fluff. Product: " & A2 & ", Material: " & B2 & ", Size: " & C2 & ", Price: " & D2)

This generated decent first drafts for 200 products in about 10 minutes. They needed editing, but having a starting point beat staring at a blank cell.

Example 5: Spot Trends in Monthly Data

Gemini prompt (with data already in the sheet):

“Analyze the revenue data in column D grouped by month. Tell me which months had the highest growth rate, which had the biggest drop, and whether there is a seasonal pattern. Show your findings as a summary below the data and create a line chart.”

Gemini produced a text summary with specific numbers and a line chart. The analysis was surface-level — it will not replace a real data analyst — but for a quick sanity check on trends, it saved me 20 minutes of pivot table fiddling.

Limitations and When AI Gets It Wrong

I would be lying if I said this stuff works flawlessly. Here are the traps I have hit:

Gemini sometimes breaks your formatting. It can overwrite cells you did not ask it to touch. Always save a named version (File > Version history > Name current version) before letting Gemini make changes. The undo behavior for Gemini actions is inconsistent.

ChatGPT confuses Google Sheets and Excel syntax. Even when you specify Google Sheets, it occasionally gives you XLOOKUP (Excel-only until recently) or uses semicolons as argument separators (common in non-US locales). Double-check the syntax before pasting.

AI formulas fail silently on edge cases. A formula that works on 95% of your data might return errors or wrong results on the other 5% — blank cells, unexpected text in number columns, or dates stored as text. Always spot-check.

Bulk AI add-on calls can get expensive. Running GPT for Sheets across 10,000 rows burns through tokens fast. A single =GPT() call on one cell is cheap; doing it 10,000 times adds up. Estimate your token usage before committing to a bulk run.

AI hallucinations hit data too. If you ask Gemini to “fill in missing data” or “estimate missing values,” it will happily make numbers up. It will look plausible. It will be wrong. Never let AI generate data points you cannot verify.

Complex nested formulas sometimes have logic errors. AI might generate a formula that runs without errors but produces subtly wrong results. A SUMIFS that misses a criteria range, or an ARRAYFORMULA that double-counts. Test against known values.

Tips for Better Results

After months of using AI in Sheets daily, here is what I have learned:

Be absurdly specific in your prompts. Do not say “column with dates.” Say “column A, rows 2 through 3000, containing dates in mixed formats including MM/DD/YYYY and YYYY-MM-DD.” The more context, the better the formula.

Tell the AI what function to use. If you know you want a VLOOKUP, say so. If you want QUERY instead of a pivot table, specify it. AI defaults to whatever it thinks is simplest, which is not always what you need.

Test on 10 rows before applying to 10,000. Copy a small sample to a test sheet, run the formula or Gemini action there, verify the output, then apply to your full dataset.

Use named versions as checkpoints. Before any AI-driven bulk action, go to File > Version history > Name current version. Name it something obvious like “Before AI cleanup.” You will thank yourself later.

Combine tools strategically. Use Gemini for quick visual tasks (charts, formatting, pivot tables). Use ChatGPT for complex formula generation where you need to understand the logic. Use GPT for Sheets when you need to run AI across hundreds or thousands of rows.

Keep a formula library. When AI generates a formula that works well, save it somewhere. I keep a “Formulas” sheet in a personal Google Sheet with descriptions and examples. Faster than re-prompting every time.

Wrapping Up

AI in Google Sheets is not about replacing your spreadsheet skills. It is about skipping the tedious parts — the formula syntax lookups, the repetitive data cleaning, the “how do I write a QUERY function again” moments — so you can focus on actually analyzing your data and making decisions.

Start with Gemini’s side panel for everyday tasks. Use ChatGPT when you need formula help. Add GPT for Sheets or SheetAI if you have bulk processing needs. And always, always verify the output before trusting it with anything important.

The 3,000-row mess I mentioned at the start? Standardized product names, cleaned dates, flagged duplicates, and generated a summary pivot table with a chart. Fifteen minutes, a few prompts, and one or two formula tweaks. That is the actual value here — not some futuristic AI takeover, just less time staring at cells.

For more insights, check out our guide on productivity.