Gemini in Google Sheets: the AI function and data analysis
Use =AI() in Google Sheets to tag feedback, write ad variants and enrich lists, plus AI columns, Fill with Gemini and the analysis side panel.
Gemini in Google Sheets lets you run a prompt on every row of a spreadsheet. Type =AI("prompt", A2), or =Gemini(), then click Generate and Insert. You can tag 2,000 survey answers by theme, score review sentiment, draft ad variants for each product, or enrich a list with current facts from Google Search. Add AI columns to tables, use Fill with Gemini to complete a pattern, and use the side panel for charts and analysis. It needs an eligible Workspace or Google AI plan, works on up to 350 selected cells at a time, and only sees the ranges you pass in.
- Syntax: =AI("prompt", [range]). Always pass the range. The function can't see the rest of your sheet or Drive.
- It can generate, summarise, categorise, analyse sentiment and pull real-time info from Google Search, with sources on hover.
- Up to 350 selected cells generate at a time, with short-term and 24-hour limits.
- Output doesn't update automatically. Use Refresh and Insert, and paste values when you're done.
- Give it fixed category lists. Open-ended tagging gives you 40 near-duplicate themes.
Checked 5 November 2026 (CET) against Gemini Apps Help, the Gemini release notes, Google Workspace Help, Google Search Central, Search Console Help, the Gemini API docs and the Google blog posts cited in Sources. Google renames and moves Gemini features often, so confirm labels and limits in your own account. "Our view" marks MentionX opinion. Ledgerline is a fictional company used for illustration.
01What can Gemini do in Sheets?
| Feature | What it does | How to start |
|---|---|---|
| AI function | Runs a prompt per cell, using the range you pass | =AI("prompt", A2) or Insert > Function > AI |
| AI column | Adds a column to a table with the AI function in the first row, ready to autofill | Last column of a table > Insert AI column right |
| Fill with Gemini | Completes a column from the pattern in filled cells, or from a prompt | Drag the new fill handle, or select empty cells and choose Fill |
| Side panel | Asks questions about the sheet, creates charts, pivot tables and formulas | Ask Gemini button at the top right |
On AI Pro and Ultra, Gemini in Sheets uses Google's current fast model. Confirm the model name in Sheets help before you quote it. Workspace editions use whatever model that edition currently includes.
02How do you write an AI function that works?
Google's syntax is AI("prompt", [optional range]). The range is optional, but Google "highly recommends" it, because the function can't see anything else.
Good marketing examples, adapted from Google's own patterns:
=AI("Classify this customer comment into exactly one of: Price, Onboarding, Integrations, Support, Features, Other. Output only the label.", C2)
=AI("Sentiment of this review: Positive, Neutral or Negative. Output one word.", D2)
=AI("Write a Google Ads headline under 30 characters for this product, using its main benefit.", A2:C2)
=AI("Summarise this sales call note in one sentence, starting with the main objection.", E2)
=AI("What is the headquarters city of this company? Output only the city.", B2)The last one uses real-time Google Search. Hover over the cell and click Sources to see where it came from.
03What are the limits?
From Google's help page:
| Limit | What it means for you |
|---|---|
| Text output only | No images or charts from the function itself |
| Sees only the range you pass | Put context, such as definitions, in the sheet and reference it |
| 350 cells per generation | Work in batches of 350 rows |
| Short-term and long-term limits | If you hit the long-term limit, wait 24 hours |
| No undo or redo of the function | Regenerate instead, or copy the sheet first |
| No nesting | =IF(AI(...)) isn't supported; put AI output in its own column, then use IF on that column |
| No Box, Dropbox or Egnyte files | Convert to a native Google Sheet first |
When you click Generate and Insert, the edit is attributed to you in version history.
04Which marketing jobs is it best for?
| Job | Columns | Prompt pattern |
|---|---|---|
| Voice-of-customer tagging | Comment, Theme, Sentiment | Fixed label list, one-word output |
| Review mining | Review text, Feature mentioned, Complaint | "Extract the feature mentioned, or None" |
| Keyword intent | Keyword, Intent | "Informational, Commercial, Transactional or Navigational" |
| Ad copy variants | Product, Benefit, Headline 1-3 | Character limits in the prompt |
| Lead list enrichment | Company, Industry, HQ | Search-grounded, then spot-check sources |
| Content audit | URL, Title, Summary, Suggested action | "Keep, Update, Merge or Remove, with a reason in 10 words" |
| AI prompt tracking | Prompt, Answer text, Brand mentioned? | "Does this text mention Ledgerline? Yes or No" (lesson 20) |
05How do you check the quality?
- Pilot 30 rows. Read every output and fix the prompt.
- Lock the labels. Use a fixed list and "output only the label".
- Audit a sample. After the full run, check 1 row in 20 by hand and note the error rate.
- Paste values. When you're happy, copy the column and paste values only, so results don't change on refresh.
- Check sources for any search-grounded column before you use it externally.
Our view: an error rate under 5% on a hand-checked sample is good enough for internal theme analysis. It isn't good enough for anything published or sent to a customer. That needs a full human review.
06When should you use the side panel instead?
Use the AI function for row-by-row work. Use the side panel for questions about the whole table:
- "Create a pivot table of Theme by Sentiment."
- "Which theme has the highest share of Negative comments this quarter versus last?"
- "Build a chart of weekly sign-ups with a 4-week moving average."
- "Write a formula that flags rows where Spend > 1000 and Conversions = 0."
Check the formulas it writes before you build a report on them.
07Worked example: Ledgerline
Ledgerline is a fictional accounts-payable software company. Details are illustrative.
Ledgerline had 1,400 open-text answers from a churn survey sitting unread in a sheet.
| Step | Setup | Result (illustrative) |
|---|---|---|
| Theme | Fixed 7-label list in H1 | 1,400 rows tagged in 4 batches |
| Sentiment | Positive, Neutral, Negative | 61% Negative, as expected for churn |
| Competitor mentioned | "Competitor A, B, C, D or None" | Competitor C named in 9% of answers |
| Audit | 70 rows checked by hand | 4 errors (about 6%), mostly Integrations vs Features |
| Fix | Added a one-line definition for each label in H2 | Re-audit: 2 errors in 70 |
Walkthrough
- The analyst copied the sheet as a backup, since AI functions can't be undone.
- She put the label list in H1 and definitions in H2, and referenced both by concatenation.
- She piloted 30 rows, tightened the prompt to "output only the label", and then ran batches of 350.
- She audited 70 rows, fixed the definitions, and refreshed.
- She pasted values, then used the side panel to build a Theme x Competitor pivot table.
- The finding (churners citing Competitor C's ERP integrations) went to product marketing for the next teardown.
08Common mistakes
- No range argument. The function then has no data to work with.
- Open-ended categories. You get dozens of overlapping labels.
- Nesting AI inside IF. It isn't supported.
- Leaving live AI cells in a report. Paste values to freeze the results.
- Trusting search-grounded cells without checking sources.
09Try it yourself
Export 100 reviews or survey answers into a sheet. Add a label list in H1 and run a theme and sentiment column with the AI function. Hand-check 20 rows, write down the error rate, fix the prompt once, and re-check. Then ask the side panel for a pivot of theme by sentiment.
10Quick quiz
1. What are the two ways to write the AI function?
Answer: =AI() and =Gemini().
2. How many selected cells generate at once?
Answer: The first 350.
3. How do you stop AI results from changing?
Answer: Copy the column and paste values only.
11What should you check before the next lesson?
- You've run the AI function on a real data set with a fixed label list.
- You know your error rate from a hand-checked sample.
- You've frozen the results with paste values.
Previous: Gemini in Gmail, Docs, Slides and Workspace Studio. Next: Gemini Notebook (formerly NotebookLM) for marketing research. Or return to the Gemini for Marketers track. Related: Claude in Excel and Google Sheets, GA4 exports for AI analysis, Search Console API and AI analysis.
12Common questions
How do I use the AI function in Google Sheets? Type =AI("your prompt", A2) or =Gemini() in a cell, or go to Insert > Function > AI. Select the cells, then click Generate and Insert.
How many cells can the AI function run at once? Google says only the first 350 selected cells with AI functions are generated at a time. Wait for them to finish, then select the next batch.
Can the AI function read my whole spreadsheet? No. It only sees the range you pass in. Put the data or context you need in the sheet and reference it.
Questions people ask
3 answersHow do I use the AI function in Google Sheets?
+
Type =AI("your prompt", A2) or =Gemini() in a cell, or go to Insert > Function > AI. Select the cells, then click Generate and Insert.
How many cells can the AI function run at once?
+
Google says only the first 350 selected cells with AI functions are generated at a time. Wait for them to finish, then select the next batch.
Can the AI function read my whole spreadsheet?
+
No. It only sees the range you pass in. Put the data or context you need in the sheet and reference it.
Sources
- Google Docs Editors Help, Use the AI function in Google Sheetssupport.google.com
- Google Workspace, Pricingworkspace.google.com
- Gemini Apps, Release notesgemini.google
- All accessed 5 Nov 2026.