AI for Spreadsheets — AI Tools Masterclass

The formula nobody could write

The sales team needed a report: total revenue by region, but only for deals closed in Q4, excluding refunds, grouped by sales rep, with percentage of quota achieved. The spreadsheet had 15,000 rows.

The analyst spent 45 minutes building nested SUMIFS, VLOOKUP chains, and conditional formatting. The formula was 200 characters long. It broke when someone added a column.

Then a colleague typed into ChatGPT: "Write an Excel formula that sums column E where column B is 'Q4' and column F is not 'Refund', grouped by column C." Ten seconds later, she had a working formula — with an explanation of how it worked.

That analyst now uses AI for every complex formula. Not because she can't write them — because it's faster and less error-prone.

2Bpeople use spreadsheets worldwide

88%of spreadsheets contain errors (Ray Panko, 'What We Know About Spreadsheet Errors,' 1998 meta-analysis — based on audited professional spreadsheet samples)

5minto do what used to take 45 minutes

Three ways to use AI with spreadsheets

1. AI as formula writer

The most immediately useful approach: describe what you want in plain English, get a working formula back.

Works with: ChatGPT, Claude, Google Gemini, Copilot

What you type	What AI gives you
"Sum sales where region is West and month is January"	`=SUMIFS(D:D, B:B, "West", C:C, "January")`
"Find the most recent date for each customer"	`=MAXIFS(E:E, A:A, A2)`
"Calculate the percentage change between this month and last month"	`=(B2-B1)/B1` with formatting instructions
"Write a VLOOKUP that matches product IDs to product names from the lookup table on Sheet2"	`=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)`

How to do it:

Open ChatGPT, Claude, or any AI chat
Describe your data layout: "I have a spreadsheet with columns A (Name), B (Region), C (Sales), D (Date)"
Ask for the formula: "Write a formula that calculates the average sales per region for Q1 2026"
Paste the formula into your spreadsheet

⚡

Write formula prompts

25 XP

For each task, write a clear prompt you'd give to an AI to get the right formula: 1. Find duplicate values in column A 2. Count how many cells in column B contain the word "urgent" 3. Calculate the running total of column C 4. Highlight cells in column D that are more than 30 days old Remember: include your column layout and what the data looks like.

2. AI built into your spreadsheet

Several products now embed AI directly into the spreadsheet interface — no need to switch to a separate chat.

Tool	AI feature	What it does
Microsoft Excel + Copilot	Copilot sidebar	Analyze data, create charts, write formulas, generate insights — all from natural language inside Excel
Google Sheets + Gemini	"Help me organize"	Suggest formulas, clean data, create templates
Rows	Built-in AI analyst	Ask questions about your data in natural language, get answers with charts
Numerous.ai	GPT inside cells	Use AI functions directly in cell formulas: `=AI("Categorize this text:", A2)`

💭You're Probably Wondering…

There Are No Dumb Questions

Should I stop learning formulas and just use AI?

No. Understanding formulas helps you verify AI output and catch errors. Think of AI as a calculator — you should still understand math, but you don't need to do long division by hand.

Is it safe to upload my company's data to ChatGPT?

Check your company's AI policy. For sensitive data, use enterprise versions (ChatGPT Enterprise, Claude for Business) that don't train on your data. Or use locally-running AI tools. Never upload customer PII, financial data, or trade secrets to free-tier AI without clearance.

3. AI for data analysis

Upload an entire spreadsheet and ask questions about it. No formulas needed.

ChatGPT Advanced Data Analysis:

Upload your CSV or Excel file
Ask: "What are the top trends in this data?"
Get: Analysis, charts, and insights — automatically

Example prompts for data analysis:

"Summarize this dataset. How many rows? What's the date range? Any missing values?"

"What's the trend in revenue over the past 12 months? Create a line chart."

"Which customer segment has the highest average order value?"

"Find any outliers or anomalies in the data and explain what might cause them."

"Create a pivot table showing total sales by product category and quarter."

🔑The 80/20 of AI + spreadsheets

Most spreadsheet users spend 80% of their time on 3 tasks: writing formulas, creating charts, and cleaning data. AI handles all three dramatically faster. Start with formula writing (it's the easiest win), then move to data analysis (the biggest time saver).

Common AI + spreadsheet workflows

Data cleaning

Problem	Prompt
Inconsistent formats (Jan, January, 01)	"Standardize all date formats in column B to YYYY-MM-DD"
Duplicate entries	"Find and highlight duplicate rows based on email address in column C"
Missing values	"Fill missing values in column D with the average of that column"
Messy text	"Extract just the phone number from the text in column E"

Reporting

Need	Prompt
Executive summary	"Create a one-paragraph summary of this quarter's sales performance"
KPI dashboard	"Create a dashboard showing revenue, growth rate, and top customers"
Comparison	"Compare Q1 vs Q2 performance and highlight the biggest changes"

⚡

Build an AI-powered analysis

50 XP

Think of a spreadsheet you work with regularly (or imagine one for your role). Plan an AI-powered analysis: 1. What data is in the spreadsheet? (describe columns and rows) 2. What question do you most want answered from this data? 3. Write the exact prompt you'd give to ChatGPT or Claude to analyze it 4. What chart or visualization would be most useful? 5. Who would you share the results with, and in what format?

Tips for better AI + spreadsheet results

**Describe your data layout** — "Column A is customer name, B is order date, C is amount" gives AI the context it needs

**Specify the spreadsheet app** — "Write this for Google Sheets" vs "Write this for Excel" (formulas differ slightly)

**Ask for explanations** — "Write the formula AND explain how it works" teaches you while solving the problem

**Test on sample data first** — Verify the formula on a few rows before applying to 10,000

**Iterate** — "That formula works but I also need to exclude blank cells" builds on the first answer

⚠️Always verify

AI-generated formulas can have subtle errors — wrong column references, off-by-one errors, or misunderstanding your data structure. Always test on a few rows with known answers before trusting the output on your full dataset.

Key takeaways

AI can write spreadsheet formulas from plain English — saving hours on complex SUMIFS, VLOOKUPs, and array formulas
Three approaches: AI as formula writer (ChatGPT/Claude), AI built into spreadsheets (Copilot/Gemini), AI for analysis (upload and ask)
88% of spreadsheets contain errors — AI reduces (but doesn't eliminate) formula mistakes
Always describe your data layout, specify your spreadsheet app, and verify output
Start with formula writing (easiest), then data analysis (biggest impact)
Understanding formulas still matters — AI is an accelerator, not a replacement

Knowledge Check

1.What's the most effective way to get AI to write a spreadsheet formula?

2.Why should you still understand spreadsheet formulas if AI can write them?

3.What percentage of spreadsheets contain errors, according to research?

4.Before uploading company data to an AI tool, what should you do first?