If you have ever spent twenty minutes trying to remember the syntax for a VLOOKUP, or stared at a column of dates in seven different formats, or pasted data from a PDF that came out as a single column of garbled text — this article is for you.
Spreadsheets are a practical target for AI assistance because the bottleneck is often the friction between “I have data” and “I have answers.” The goal is not to replace the spreadsheet; you still need the file, its structure, and a reproducible result.
Three things changed:
- Major spreadsheet tools offer AI features inside the workbook. Current documentation for Copilot in Excel and Gemini in Google Sheets lists tasks such as drafting formulas, creating charts, and analysing data, subject to account and file requirements.
- Some chat products can inspect uploaded spreadsheet files and, where code execution is available, perform analysis or create a revised file.
- These tools can attempt multi-step workflows from plain-language instructions, but capability varies by product, account, file, and task. You still need to inspect and reproduce the result.
This article walks through how to actually use these capabilities, in the order most people will encounter them.
Three modes of AI + spreadsheets
Roughly speaking, you have three options. Each is right in different situations.
Mode 1: AI inside the spreadsheet. Use Excel’s Copilot or Gemini in Sheets when your account supports the needed action. Ask from inside the workbook; depending on the product and task, it can propose formulas, charts, summaries, or edits. Best when you want to keep the work and its review trail in the spreadsheet.
Mode 2: AI outside the spreadsheet. In a product and account that supports file analysis, upload a compatible CSV or Excel file and ask questions. The product may use code, return a written analysis, create charts, or offer a revised file. Best for exploratory work when you can inspect the steps and validate the output.
Mode 3: AI as a formula writer. Describe the intended result in plain English and ask for a candidate formula for your spreadsheet product and locale. Best when you know roughly what you want but cannot remember the syntax, and can test the references and edge cases yourself.
We will cover all three.
Mode 1: AI inside the spreadsheet
Excel Copilot and Gemini in Google Sheets both accept natural-language requests, but their interfaces and supported actions differ. In either product, identify the relevant sheet, range, columns, and requested output before you run the task.
Useful starting prompts for inside-the-spreadsheet AI:
Look at the data in columns A to F. Summarise it: tell me the date range, the column types, and any obvious issues (missing values, inconsistent formats, outliers).
Add a column to the right that classifies each row as “high,” “medium,” or “low” priority based on the value in column D.
Make a chart that compares monthly revenue across the three product categories. Make it readable on a slide.
Find the rows where the “status” column has anything other than “open,” “closed,” or “in progress” — these are likely typos.
Calculate the running 7-day average of column C and put it in column G.
The useful pattern is to be specific about which columns, what operation, and what the output should look like. The AI may draft a formula or chart; review its proposal, the underlying references, and the resulting values before accepting it.
The limits are product- and workbook-specific. A task that spans multiple sheets, several layers of conditional logic, or substantial cleanup may be easier to audit in a dedicated analysis environment, but moving it outside the workbook does not make it correct automatically.
Mode 2: AI outside the spreadsheet
First confirm that your account supports the file type, size, and analysis tools you need. Upload a copy rather than the only version, remove data the vendor is not approved to process, and ask the product to describe the workbook before changing it. Depending on the product and account, it may analyse the file, expose generated code, or create a downloadable result; none of those capabilities should be assumed in advance.
A practical workflow:
I’m uploading a spreadsheet of [what it is]. First, give me a quick overview:
- What’s in it — rows, columns, what each column appears to be.
- Any data quality issues (missing values, weird formats, likely typos, duplicates).
- A first round of obvious-but-useful summary stats (counts, ranges, distributions).
Then wait for my specific question.
This first pass gives you a concrete assessment of what you are dealing with. Runtime depends on the file, tool, and current service load. Now ask the real question:
Of the entries in this spreadsheet:
- How many came from Estonia, Finland, and Germany?
- What is the average deal size in each country?
- Which sales rep has the highest conversion rate?
Give me a clean summary table. Also: are there any rows where the country field is ambiguous (e.g., “DE” vs “Germany” vs “Deutschland”)? Flag those.
The model may produce the answer along with a chart and a cleaned version of the file you can download. Include the “flag ambiguous rows” instruction; without it, data-quality problems can hide inside the summary.
For multi-step analysis:
I want to understand which customers churned, why, and what predicts churn. Walk me through your analysis in three steps:
- First, identify churned customers — define your criterion (e.g., no activity in 90 days).
- Then compare churned vs active customers on the dimensions I have data for.
- Then tell me which dimensions show the biggest gap and would be the best leading indicators of churn.
Use the approved standard configuration as the baseline. If a supported higher-reasoning setting is available, use it only as a comparison on the same data and success checks. Show the code, formulas, and intermediate results used to reach the answer so I can reproduce and sanity-check it.
For multi-step tasks, first define an eligible baseline that uses the same data, permissions, and output requirements. Add a supported higher-reasoning setting only when the comparison shows a worthwhile gain in measured correctness or completeness relative to latency and cost. Product names and picker labels change frequently, and more reasoning does not guarantee a correct result. Ask for executable code, formulas, intermediate results, and a way to reproduce the calculation.
Mode 3: AI as a formula writer
The simplest mode is to ask a chat assistant for a candidate formula when you know the intended result but cannot remember the syntax. Specify Excel or Google Sheets and the relevant locale, paste the formula into a test cell, then verify it on known inputs before filling the column.
Useful starting examples:
I have a list of email addresses in column A. I want column B to show just the domain. What Google Sheets formula should I use?
In Excel, how do I count the rows where column D is between 10 and 20 AND column E says “active”?
I have dates in column A that look like “2026-04-12T14:30:00Z” — full ISO format. I want column B to show just the date in DD/MM/YYYY format, in Estonian time zone.
I have a column of currency strings like “€1,234.56”, “$987.00”, “£42.10”. I want to split it into two columns: numeric value, and currency symbol.
I have two sheets. Sheet1 has customer IDs and names. Sheet2 has customer IDs and dollar amounts. I want a third sheet that joins them, with customer name, customer ID, and amount.
The model should return a candidate formula. For the last example, it may suggest VLOOKUP, INDEX/MATCH, or XLOOKUP with an explanation. Check the references and result before reusing it.
A particularly useful follow-up for formulas you are using regularly: “Now explain what this formula is doing, line by line, so I understand it.” This is a practical way to learn spreadsheet syntax because the explanation is grounded in your actual problem.
Cleaning messy data
Data cleaning is an easy place to lose time, and a practical place to use AI with verification. Some common cleanup operations and how to ask:
Inconsistent date formats:
Look at column A. Dates are in mixed formats: some “2026-04-12”, some “12/04/2026”, some “April 12, 2026”, some weirdly typed. Normalise them all to ISO format (YYYY-MM-DD). Flag any rows you couldn’t parse.
Mismatched company names:
Look at column B (Company). Some rows say “Apple Inc.”, some “Apple”, some “apple inc”, some “Apple Computer Inc.”. Group these into canonical names. Add a new column with the canonical name for each row.
Phone numbers in many formats:
Column D has phone numbers. Some have country codes, some don’t; some have parentheses, dashes, spaces. Normalise them to E.164 international format (+372…). Assume Estonia if no country code is given. Flag any I cannot parse.
Email addresses with hidden errors:
Column E has email addresses. Find any that are likely invalid (missing @, missing TLD, has spaces, has typos like “.con” instead of “.com”). Flag them in a new column.
Depending on the tool and task, the model may return a cleaned file along with a summary of what it changed. Review both before trusting the result.
A trap to watch for: silent errors
AI doing data analysis can produce silently wrong answers in two ways.
Hallucinated calculations. The model writes plausible-sounding numbers without actually computing them. This can happen even when a tool has built-in code execution (Python/Code Interpreter). The protection: explicitly ask “show me the code or formula you used, and give me a way to verify.”
Misinterpreting the data. The model assumes a column is something other than what it is — “total” when it is “subtotal,” “active customers” when it is “all customers.” Always show the model the actual column names and ask it to confirm interpretation:
Before doing the analysis, tell me what each column is. Confirm with me that your interpretation matches what I intend. Wait for confirmation before computing anything.
This short confirmation step can catch a misread schema before it affects every result.
When the spreadsheet gets too big
File and workload limits vary by product and account, while row count alone is a poor measure of difficulty: formulas, tabs, cell width, and task complexity also matter. Check the vendor’s current upload documentation and the in-product limit before sending the file. When a workbook no longer fits or the analysis must be repeatable, shift to a versioned Python, R, SQL, or BI workflow.
A useful intermediate move for a large dataset is to create a documented, representative sample, explore it with the assistant, and then reproduce and confirm the result against the full dataset in a proper analysis tool. Do not choose a convenient first-N-rows slice and call it representative.
Describe the problem, spot-check the result
Three modes — inside the spreadsheet, outside the spreadsheet, formula writer. Pick the right one for the task, and part of the work can shift from recalling syntax to describing the problem in plain English.
You do not need to memorise formula syntax for every task. You do need to be specific about the data, the operation, and the output. You also need to spot-check the results; verification is part of the task, not optional overhead.
Try one real spreadsheet task today using the right mode.
