Skip to content

AI for Everyday Work: Writing, Research, Meetings & Data · Spreadsheets, data and presentations · lesson 14 of 17 · 13 min

Spreadsheets and data analysis with AI

You don't need to be a spreadsheet expert

AI can help you write formulas, clean messy data, find patterns and create charts, even if you've never used a pivot table. Depending on your tools, you can:

  • Ask an assistant to explain or write formulas you then paste into Excel or Google Sheets.
  • Upload a spreadsheet or CSV to an assistant that can analyze data (many can run calculations with code behind the scenes; availability varies by plan).
  • Use AI built into your spreadsheet app (for example, Copilot in Excel or Gemini in Google Sheets, where your organization has enabled them).

Formulas on demand

I'm using [Excel / Google Sheets]. Column A has dates, column B has product names, column C has sales amounts.
I want a formula that totals sales for "Product X" in March 2026.
Give me the formula, explain each part in plain English, and tell me where to put it.

Test the formula on a few rows where you know the answer. If it's wrong, paste the error or the unexpected result back and ask for a fix.

Analyzing a dataset: step by step

1. Describe the data and your goal.

I've uploaded our sales for the last 6 months. Columns: date, region, product, units, revenue.
Goal: find which regions and products are growing or shrinking.
First, check the data for missing values, duplicates or odd entries and tell me what you find. Don't analyze yet.

2. Decide how to handle problems (for example, "remove exact duplicates; flag rows with zero revenue but positive units").

3. Ask clear questions.

Show monthly revenue by region in a table and a line chart.
Which 3 products grew most in revenue from the first 3 months to the last 3 months? Show the numbers.
Explain the main trends in 5 bullet points a non-expert would understand.

4. Check the numbers. Pick two or three results and confirm them yourself with a simple total or filter in your spreadsheet. Ask the AI: "Show me how you calculated this."

Worked example

Hina runs an online clothing shop from Lahore. She exports six months of orders (removing customer names and phone numbers first) and uploads the file to her assistant. It finds duplicate orders from a payment retry issue, removes them after she agrees, and shows that one product category is growing fast while another is declining. It also notices that most returns come from one size range. She checks two totals against her shop dashboard: they match. She adjusts her next stock order and updates the size guide.

Sample output check

A good analysis answer includes: a note on data quality issues found, a clear table with totals, a chart with labeled axes, and plain-English insights with the actual numbers. It should also mention limits, such as "only six months of data, so seasonal patterns may not be visible". If insights don't include numbers, ask for them.

Where the analysis happens in 2026

  • Upload and ask: ChatGPT, Claude, Gemini and Copilot can analyze uploaded spreadsheets and CSV files, usually by writing and running code behind the scenes, and produce tables and charts. Some newer tools go further: Microsoft 365 Copilot's Analyst agent turns raw data into a report with charts, and Gemini Notebook now includes a secure cloud computer that can run code over your sources.
  • Inside the spreadsheet: Copilot in Excel (including Agent Mode on eligible plans) and Gemini in Google Sheets can create formulas, pivot tables, charts and summaries in place, where your organization enables them.
  • Formulas on demand: any assistant can write and explain a formula you paste yourself.

Whichever route you use, ask the assistant to show its steps (the filters, formulas or code it used) so you can check them.

Hands-on: the four-prompt analysis

1. INSPECT (don't analyze yet)
I've uploaded [file]. Columns: [list]. Before any analysis, report: row count,
date range, missing values, duplicates, and anything odd (negative amounts, text in number columns).

2. CLEAN (you decide the rules)
Apply these rules and tell me how many rows each affected:
remove exact duplicates; exclude test orders (customer = "TEST"); keep refunds as negative revenue.

3. ANSWER ONE QUESTION AT A TIME
Show monthly revenue by region as a table and a line chart.
Then list the 3 products with the biggest revenue change between the first 3 and last 3 months, with the numbers.

4. EXPLAIN AND CHECK
Explain the main trends in 5 bullets for a non-expert.
List the exact steps/formulas you used, and 3 totals I can check against my own spreadsheet.

Before: "Analyze my sales" produces a confident paragraph that includes test orders and double-counts duplicate rows.

After: you learn the file has 42 duplicate rows and 6 test orders (illustrative), you decide how to treat them, and your final chart is built on clean data with totals you've checked against a pivot table.

A 60-second sanity check

Before you share any AI analysis, recompute one total yourself (a SUM or a quick pivot). If it matches, you can trust the pipeline more. If it doesn't, find out why before anyone sees a chart.

Remember: correlation isn't causation

AI might notice that sales rise on days you post on social media. That doesn't prove posting caused the rise; a holiday, a promotion or payday might explain both. Treat patterns as ideas to test, not proven facts.

Protect personal data

Customer spreadsheets often include names, emails, phone numbers and addresses. Before uploading:

  • Delete columns you don't need for the analysis.
  • Replace names with IDs where possible.
  • Use your organization's approved tool for any customer or employee data.

Pitfalls

  • Trusting results without spot-checking.
  • Not defining terms ("best-selling" by units or by revenue?).
  • Drawing conclusions from very small numbers.
  • Uploading full customer lists when only totals are needed.

Video lecture: Spreadsheets and data analysis with AI

Lecture coming soon · 10 chapters · about 8 minutes. Read the full transcript below.

  1. Spreadsheets with AI
  2. Why care
  3. The kitchen analogy
  4. Three ways to work
  5. Example 1: the formula
  6. Example 2: the regional sales review
  7. Watch me do it
  8. Correlation isn't causation
  9. Common mistakes
  10. Recap and try this now

Lecture transcript

Spreadsheets with AI

Do you have a spreadsheet you've been meaning to analyze for weeks, but every time you open it, the rows just stare back at you? You're not alone. Most people use a tiny fraction of what spreadsheets can do, because formulas and pivot tables feel like a foreign language. AI changes that. You can ask for a formula in plain English, upload a file and ask what's going on, or let the AI build charts right inside Excel or Google Sheets. In this lecture you'll learn a four prompt analysis method that gives you answers you can actually trust. You'll see two examples, watch me analyze a sales file, and learn the sixty second check that stops embarrassing mistakes.

Why care

Why does this matter? Because data questions come up in almost every job. Which products are growing? Which region is slipping? How much did we spend on travel? AI can answer those in minutes. But here's the key idea. AI analysis looks equally polished whether it's right or wrong. A beautiful chart built on duplicate rows or test orders is still a wrong chart, and it's more dangerous than a messy one because people believe it. So the skill isn't just asking questions. It's making sure the data is clean, the steps are visible, and at least one number is checked by you.

The kitchen analogy

Think of data analysis like cooking. First, you inspect the ingredients. Is anything missing, spoiled, or not what the label says? That's asking the AI to report missing values, duplicates and odd entries before any analysis. Next, you clean and prep, and you decide how. Remove duplicates? Exclude test orders? Keep refunds? Those are your decisions, not the AI's. Then you cook one dish at a time: one clear question per prompt. And finally, you taste before serving. You check one total yourself before anyone else sees the results. Skip any step and you might serve something that looks great and tastes terrible.

Three ways to work

There are three ways to work. First, upload and ask. ChatGPT, Claude, Gemini and Copilot can analyze uploaded spreadsheets and CSV files, usually by writing and running code behind the scenes. Microsoft 365 Copilot's Analyst agent turns raw data into a report with charts, and Gemini Notebook can now run code over your sources too. Second, inside the spreadsheet. Copilot in Excel and Gemini in Google Sheets can build formulas, pivot tables and charts in place, where your organization has them switched on. Third, formulas on demand. Describe what you want in plain English, get a formula, and paste it yourself. Whichever you use, ask the assistant to show its steps.

Example 1: the formula

A simple example. You're in Google Sheets. Column A has dates, column B has product names, column C has sales amounts. You want the total sales for one product in March. You ask: I'm using Google Sheets. Give me a formula that totals sales for product X in March twenty twenty six. Explain each part in plain English and tell me where to put it. You get a formula that checks the product name and the date range, with an explanation of each piece. Then, and this is the important bit, you test it on a few rows where you already know the answer. If it's wrong, you paste the unexpected result back and ask for a fix. Within two minutes you've got a formula you understand and trust.

Example 2: the regional sales review

Now a realistic case, with illustrative numbers. Aliyu is a sales operations analyst at a distribution company in Lagos, preparing a six month regional review. He uploads the order export, after removing customer names and phone numbers because the analysis doesn't need them. Prompt one: inspect, don't analyze. The report says forty two exact duplicate rows and six test orders from a system trial. Prompt two: he sets the rules. Remove duplicates, exclude test orders, keep refunds as negative revenue. Prompt three: monthly revenue by region, as a table and a chart. Prompt four: explain the trends and list the steps used, plus three totals he can check. He builds one quick pivot table himself. The totals match. Without step one, his chart would have overstated one region's growth.

Watch me do it

Let me run it. I've uploaded a CSV of online orders. Prompt one: before any analysis, report the row count, date range, missing values, duplicates and anything odd. It finds some orders with a negative quantity. Interesting. Those turn out to be returns. Prompt two: remove exact duplicates, treat negative quantities as returns and subtract them from revenue. It tells me how many rows each rule affected. Prompt three: show monthly revenue by channel as a table and a line chart. Prompt four: explain the trends in five bullets for a non expert, list the exact steps you used, and give me three totals I can check. Now the taste test. I open the file in my spreadsheet, do a quick SUM for one month, and it matches. Now I'm comfortable sharing it.

Correlation isn't causation

One thinking trap deserves its own moment. The AI finds that sales rise on days you post on social media. Great, so posting causes sales? Maybe. Or maybe you tend to post on paydays, or before weekends, when people buy more anyway. When two things move together, that's correlation. It doesn't prove one causes the other. Ask the AI: what else could explain this pattern? And if the decision matters, test it. Post on some normally quiet days and compare. AI is brilliant at finding patterns. You're responsible for deciding what they mean.

Common mistakes

The common mistakes. Analyzing before inspecting, which is how duplicates and test data sneak into your charts. Letting the AI decide the cleaning rules silently. It might drop rows you needed or keep ones you didn't. Uploading personal data the analysis doesn't need, like customer names, emails and phone numbers. Remove or replace them first, and use work approved tools for business data. Asking five questions in one prompt, which makes it hard to check each answer. And sharing a chart without recomputing a single total yourself. That sixty second check is the cheapest insurance you'll ever buy.

Recap and try this now

Let's recap. Inspect before you analyze. You decide the cleaning rules. Ask one question at a time. Ask the AI to explain its findings and show its steps. And before you share, recompute one total yourself. Here's your try this now. Pick a spreadsheet you actually use, remove any personal data, and run the four prompts from the lesson text. Note what the inspection step finds, because it almost always finds something. Then do the sixty second check. When the numbers match, you'll have an analysis you can present with confidence, and you'll have learned more about your data in twenty minutes than in the last three months.

Key takeaways

  • Inspect before you analyze: ask for row counts, date ranges, missing values, duplicates and odd entries.
  • You set the cleaning rules; the AI applies them and reports what changed.
  • Ask one question at a time and ask the AI to show the steps, formulas or code it used.
  • Recompute at least one total yourself, remove personal data first, and remember correlation isn't causation.

Try it

Ask AI to write one formula you need this week and test it on known rows. Then analyze a small, non-sensitive dataset using the four steps and spot-check two results.