How to Use ChatGPT for Excel and Google Sheets (Formulas, Cleanup, and Analysis)

If you can build a SUM but freeze up at INDEX/MATCH or a messy export, here's the short answer: ChatGPT for Excel and Google Sheets works well for writing formulas and cleaning data, and it's surprisingly good at explaining a formula someone else wrote. You describe what you're trying to do in plain English, and it hands you a formula or a set of steps. The thing to keep in mind is that ChatGPT can't actually see your spreadsheet, so it guesses your column layout unless you tell it. Get that part right and many of its answers work on the first try, though you should still verify.

I use it for this almost every week. Below is the workflow that actually holds up, not just a copy-pasted VLOOKUP.

Why most ChatGPT for Excel advice fails

A broken formula usually isn't ChatGPT being dumb. You asked it to write a formula for a sheet it's never seen. It doesn't know your customer names are in column B and the dates are in F. So it assumes. It'll confidently write =VLOOKUP(A2, D:E, 2, FALSE) when your lookup table is actually in columns H and I, and then you paste it and get #N/A and assume the AI is useless.

It's not useless, though. It guessed because you let it. The fix is to stop making it guess.

How to describe your sheet so the formula works the first time

Before you ask for anything, spend ten seconds telling ChatGPT what your data looks like. The three things that matter most: which columns hold what, what a real row looks like, and what you want the answer to be.

Here's a prompt shape I use:

"I have a Google Sheet. Column A is order date, column B is customer name, column C is product, column D is amount. I want a formula in column E that totals all amounts for the same customer name in column B. Row 2 is the first row of data."

That's it. Naming the columns and giving one concrete example row removes almost all the guesswork. If you can paste five sample rows directly into the chat, even better. ChatGPT will read your structure off the example instead of inventing one. This is the same idea behind the prompting habits that get sharper answers everywhere else. Be specific about the inputs and you get specific outputs.

One more detail that trips people up: tell it whether you're in Excel or Google Sheets, and mention if your formulas use semicolons instead of commas. In locales that use a comma as the decimal separator (common in much of Europe and elsewhere), spreadsheets use ; as the argument separator, and a formula written with commas will throw an error the second you paste it.

Cleaning a messy column without learning regex

Exports from other tools are almost always a mess, and this is the part I actually rely on ChatGPT for. Names crammed into one cell. Dates in four different formats. Trailing spaces you can't see, and phone numbers with random dashes.

The move is to show, not describe. Paste a few ugly rows, then show what you want them to become:

"Here are some values from column A: 'john SMITH ', ' Mary-Jane Watson', 'BOB jones'. I want them cleaned to proper case with no extra spaces, like 'John Smith'. Give me a formula for column B."

ChatGPT will hand you something with TRIM and PROPER wrapped together, and it often works as-is. Need to split "John Smith" into first and last name in separate columns? Ask. Need to pull the domain out of a list of email addresses? Ask. You never have to touch regex, though if your cleanup is genuinely gnarly, it can write that too and explain each piece.

What I like about pasting examples instead of describing the problem is that the examples carry the weird cases you'd forget to mention. A stray apostrophe, a name in all caps, a date that's text in one row and a real date in the next. ChatGPT sees those in the sample and accounts for them, where a written description would have skipped right over them.

The honest caveat: always run the result on a copy first. I've had it produce a formula that worked on my three sample rows and choked on a later row because that row had a middle name I didn't show it. Garbage in the example, garbage in the formula.

Getting ChatGPT to explain a formula you inherited

Most of my spreadsheet pain isn't writing formulas. It's opening a sheet someone else built and finding a 200-character nested monster in one cell. Pasting in that formula and asking what it does, in plain English, beats squinting at it for ten minutes.

It'll walk through each piece, tell you what the nested IFs are checking, and flag the part that looks fragile. I've caught real bugs this way. One formula silently returned the wrong value when a cell was blank, and it had gone unnoticed. You can also ask it to rewrite a clunky formula more simply, or to add error handling so a blank cell shows "—" instead of #DIV/0!.

The same trick helps with other everyday business tasks ChatGPT handles well, and if your inbox is the time sink, you can automate repetitive email the same way.

What you still have to double-check

ChatGPT is confident even when it's wrong, so a few things never skip my review:

Check the column references first. That's the big one. Always glance at the column letters in the formula and confirm they match your actual sheet. If you didn't describe your layout, assume they're wrong.

Watch the separator and function names. A formula that uses a function name slightly off, or commas where your locale wants semicolons, fails instantly. Easy to spot, easy to fix by telling it your setup.

Then check the actual numbers. Run the formula on a handful of rows where you already know the answer. If you know last month's total was 4,210 and the formula says 4,210, trust it. If it's off, the logic is wrong somewhere, usually a range that's one row too short or a condition that's too loose.

And the edge cases: blank cells, text stored as numbers, duplicate names. These are where formulas quietly misbehave. Ask ChatGPT directly: "what happens if column B has a blank cell?" It'll usually tell you and adjust.

None of this takes long. A formula I'd have spent twenty minutes googling takes about two, including the sanity check. Get the layout right up front and you'll spend more time using the formula than fixing it.

If you're weighing tools, here's how ChatGPT compares to Claude and Gemini for this kind of work.