Google Sheets: Data Analysis with AI

Spreadsheets are powerful but intimidating. Most people know SUM and maybe VLOOKUP — and dread anything more complex. Gemini in Sheets changes this: describe what you want in plain English, and the AI writes the formula.

🔄 Quick Recall: In the previous lesson, you used Gemini in Docs to generate and refine written content. In Sheets, Gemini shifts from text to data — analyzing numbers, writing formulas, and creating visualizations.

The Gemini Side Panel in Sheets

Open the side panel (✨ icon) and ask questions about your data in natural language:

Your spreadsheet has: Sales data with columns: Date, Product, Region, Revenue, Units Sold.

You ask: “What was our best-selling product in Q3?”

Gemini responds: Analyzes your data, identifies the product with the highest revenue or units in Q3, and shows the answer — often with a suggested chart.

Types of Questions You Can Ask

Question Type	Example
Aggregation	“What’s the total revenue by region?”
Comparison	“Compare Q3 and Q2 sales performance”
Trend	“Show me the revenue trend over the last 6 months”
Ranking	“What are the top 5 products by units sold?”
Filtering	“Show me all deals over $10,000 in the West region”
Formula	“Create a formula to calculate average order value”

✅ Quick Check: You ask Gemini: “Why did revenue drop?” for a spreadsheet with monthly revenue data. Can Gemini answer this? (Answer: Partially. Gemini can identify WHEN revenue dropped (e.g., “Revenue decreased 15% between March and April”) and correlate it with other columns in your data (e.g., “This coincided with a decrease in orders from Region B”). But it can’t know WHY — external factors like market changes, competitor actions, or operational issues aren’t in the spreadsheet. Gemini shows you the what; you provide the why.)

Formula Generation

This is the feature most Sheets users will love. Describe the calculation in plain English:

Prompt: “Write a formula that calculates the running total of column D”

Gemini suggests: =SUM($D$2:D2) and explains how it works.

Common Formula Requests

What You Want	Prompt	Gemini Generates
Average with conditions	“Average revenue for Q3 only”	`=AVERAGEIFS(D:D,A:A,">="&DATE(2026,7,1),A:A,"<"&DATE(2026,10,1))`
Count unique values	“How many unique products?”	`=COUNTA(UNIQUE(B2:B))`
Lookup across sheets	“Find the price for each product from the Pricing sheet”	`=VLOOKUP(B2,Pricing!A:B,2,FALSE)`
Percentage change	“Month-over-month revenue change”	`=(D3-D2)/D2`
Conditional formatting	“Highlight cells where revenue exceeds $5000”	Provides the formula for conditional formatting rules

Pro tip: When Gemini’s formula doesn’t work, tell it what’s wrong: “This formula returns #REF! — column D only goes to row 500.” Gemini adjusts.

The =AI() Function

The =AI() function turns every cell into an AI prompt. Put it in a cell with text input, and Gemini processes it:

Classification

=AI("Classify this customer feedback as positive, negative, or neutral: " & A2)

Drag down to classify an entire column of reviews.

Extraction

=AI("Extract the company name from this email address: " & A2)

Turns “jane@microsoft.com” into “Microsoft.”

Summarization

=AI("Summarize this product description in one sentence: " & A2)

Condenses long text for dashboards.

Generation

=AI("Write a 20-word product tagline for: " & A2)

Generates marketing copy from product names.

✅ Quick Check: You have 500 rows of customer feedback in column A. You want to classify each one as “Bug Report,” “Feature Request,” or “Praise.” How would you do this with =AI()? (Answer: In cell B2, enter: =AI(“Classify this customer feedback into exactly one category: Bug Report, Feature Request, or Praise. Return only the category name: " & A2). Then drag the formula down to B501. The key: specify the exact categories you want and ask for “only the category name” to get clean, consistent output you can filter and count.)

Multi-Table Analysis

Since October 2025, Gemini can analyze multiple tables within a single sheet:

If your spreadsheet has a “Sales” table and a “Returns” table:

Prompt: “What’s the return rate by product? Use the Sales table for total sold and the Returns table for items returned.”

Gemini cross-references both tables and generates the answer — something that previously required manual XLOOKUP or pivot table work.

Data Generation

Need sample data for testing or prototyping?

Prompt: “Generate a table with 20 rows of sample employee data: Name, Department (Engineering, Marketing, Sales, Support), Start Date (2020-2026), Salary ($50K-$150K), Performance Rating (1-5).”

Gemini creates a realistic sample dataset directly in your sheet.

Charts and Visualization

Ask Gemini to visualize your data:

Prompt: “Create a bar chart showing monthly revenue for 2025.”

Gemini generates a chart and inserts it into your sheet. You can then ask for modifications: “Make it a line chart instead” or “Add a trend line.”

Practice Exercise

Open a Google Sheet with any data (or create sample data with Gemini)
Ask the side panel: “Summarize this data” — see what Gemini identifies
Request a formula: “Calculate the average of column C where column B equals ‘Marketing’”
Try the =AI() function: In a new column, classify or summarize data from an existing column
Ask Gemini to create a chart from your data

Key Takeaways

The Gemini side panel answers data questions in plain English — no formula knowledge required
Formula generation turns natural language descriptions into working Sheets formulas
The =AI() function processes text in cells: classify, extract, summarize, or generate content
Multi-table analysis lets Gemini cross-reference multiple data sources in one sheet
AI-generated formulas are usually syntactically correct but may hit data quality issues (spaces, types)
Always verify AI-generated calculations against known values before trusting them for decisions

Up Next

In the next lesson, you’ll learn to use AI in Google Slides for presentations and Google Meet for meeting notes — two more apps where Gemini saves significant time.