What you’ll be able to do

Right, so this one’s actually pretty satisfying. You can get an AI tool to build you a working spreadsheet from nothing, formulas and all, and it’ll actually calculate things properly. Not just look like a spreadsheet. Actually work.

You can also hand it a messy pile of data, names spelled three different ways, dates in four formats, that sort of thing, and get it cleaned up without doing it row by row yourself. And you can ask it to run calculations across your data and just… tell you the answer. Total spend by category. Average per month. Whatever you need.

It won’t replace knowing your way around a spreadsheet entirely. But it’ll get you 90% of the way there without you touching a single formula bar.

Before you start

A few things worth knowing before you dive in. ChatGPT can genuinely build you a spreadsheet, it runs Python behind the scenes on whatever file you upload and hands you back a proper.xlsx file at the end. That’s the one that goes furthest, in my experience.

Claude’s good too, it’ll write formulas and read CSV or XLSX files you upload, but it’s working more as your formula-writing assistant than actually generating the finished file for you. Copilot, if you’ve got it built into Excel through a Microsoft 365 Copilot licence, will write formulas directly in your sheet and explain what they do. Gemini does a similar job inside Google Sheets, if your Workspace plan includes it. And both Excel and Sheets now have their own built in AI formula helpers, which is worth checking before you go anywhere else, because it’s already there.

None of these tools know your data unless you tell them. That sounds obvious but it trips people up constantly. You need to describe your columns, what’s in them, and what you actually want out the other end. Vague requests get vague spreadsheets.

And here’s the thing nobody tells beginners: you should always ask for the formula AND a plain-English explanation of what it’s doing. Every time. That way you’re not just trusting a black box, you’re learning what SUMIF or VLOOKUP or whatever actually does, which means next time you might not need to ask at all.

Get set up

Pick your tool based on what you’ve already got. If you’ve got ChatGPT (even the free version handles this reasonably well), that’s a solid starting point because it can build and hand back an actual file. If you live in Google Sheets and have Gemini available, or you’re in Excel with a Copilot licence, use what’s already sitting inside your spreadsheet, it saves you the upload and download dance entirely.

Before you write a single prompt, get your data into decent shape. Doesn’t need to be perfect, that’s often the whole point of asking the AI to help, but know roughly what you’ve got. How many columns, what’s in each one, roughly how many rows. If you’re working from a CSV or an existing spreadsheet, have it ready to upload.

One habit worth building from day one: keep your raw data on one sheet, and put your calculations on another. Don’t let the AI (or you) start editing the original data directly. It keeps things clean, and it means if something goes wrong with a formula, you haven’t wrecked your source information in the process.

Try it yourself

Here are three prompts that each do a different job. Copy them, tweak the details to match your own data, and see what comes back.

First, building something from scratch. This one’s a simple budget tracker:

I want a monthly budget tracker spreadsheet. Columns should be: Date, Category, Description, Amount. Add a summary section that shows total spend per category, and a running total for the month. Give me the formulas you're using and explain in plain English what each one does.

Second, cleaning up a mess. Say you’ve got a list of names and dates that’s all over the place:

Here's a CSV of customer names and signup dates. The names have inconsistent capitalisation and some have extra spaces. The dates are in a mix of formats (some DD/MM/YYYY, some MM-DD-YY). Clean this up so all names are properly capitalised with no extra spaces, and all dates are in DD/MM/YYYY format. Show me the formulas or steps you used.

Third, a calculation across rows:

I have a spreadsheet with columns for Region, Salesperson, and Sales Amount. I want a formula that shows total sales per region, and a separate formula that shows the average sale amount per salesperson. Explain what each formula does and where I should put it.

If you’re using ChatGPT and uploading an actual file, you can be more direct: ask it to build the whole spreadsheet, formulas and formatting and all, and hand you back the.xlsx. If you’re working inside Excel or Sheets with Copilot or Gemini, you’ll be asking for formulas to drop into cells you already have open.

Worth trying, too: ask it to build you a small test set with known answers first, so you can check the formula’s actually doing what it says. More on that next.

Check the result

Never trust a formula just because it looks right. This is the bit people skip, and it’s the bit that actually matters.

Start small. Ask the AI to build you a tiny test case, five or six rows, where you already know what the answer should be. Something like: “Before applying this to my full data, build a small example with 5 rows of made-up data and show me what the formula should return.” If the maths checks out on data you understand, you can trust it more on data you don’t.

Then check the real thing. Pick two or three rows in your actual spreadsheet and work out the answer yourself, by hand or with a calculator, and compare it to what the formula spat out. Doesn’t take long. If it’s a SUM or an AVERAGE, this is a two-minute job.

Watch for the classic mistakes: formulas that only pick up part of your range because a row got missed, dates that got misread because of format mixing (this one catches everyone at some point), and blank cells that throw off an average without you noticing. If a total looks suspiciously round, or suspiciously wrong, go back and check.

And read the plain-English explanation properly, don’t just skim it. If it says “this sums column C where column A equals ‘Marketing’” and that’s not actually what you wanted, you’ll catch it there rather than three formulas downstream.

If it doesn’t work

Sometimes the formula just doesn’t work, or it works but gives you a weird answer. Usually it’s because your instructions were vaguer than you thought. Go back and be specific: name your exact column headers, tell it exactly what row range you’re working with, and say precisely what output you want (“a single number” versus “a table broken down by month” are very different asks).

If the AI seems confused about your data structure, it’s often because you described it wrong or the file didn’t upload properly. Re-check what actually got read, ChatGPT in particular will sometimes tell you what it saw in your file, and it’s worth reading that bit rather than skipping to the answer.

Now, the important bit. Do not upload payroll data, bank statements, or customer personal details to a consumer AI tool without checking the terms first. I mean it. These tools have their own data policies, and you genuinely don’t always know where that file goes or how it’s used for training. If you need help with sensitive data, anonymise it first, swap real names for “Person A”, “Person B”, strip out account numbers, replace real figures with placeholders that keep the same structure. You can always ask the AI to build the formula logic on fake data, then apply the finished formula to your real spreadsheet yourself, without the real data ever leaving your machine.

That’s the safest way to work, honestly. Get the logic sorted on dummy data, then bring the real numbers in at the very end.

Keep exploring

Once you’ve got the basics down, there’s more worth trying. If you want to turn raw numbers into insight rather than just tidy them up, have a look at How to get AI to analyse my spending. If you need to turn your spreadsheet findings into something you can present to other people, there’s How to get AI to make a presentation. And if you find yourself wanting to automate something beyond what formulas can do, How to get AI to write code is the natural next step.

Sources and review notes

Review date: 15 September 2026 — every source above was opened and checked against the text on that date.