Get AI to Write Your Spreadsheet Formulas and Clean Up the Mess

Most people who struggle with spreadsheets are not bad at spreadsheets. They just never learned the formula syntax, which is genuinely fussy and unforgiving. A single misplaced comma turns a working formula into #VALUE!. The good news is that describing what you want in plain words is now a completely valid way to get a formula, and it works well enough that I rarely write one from scratch anymore.

This piece covers two things: getting AI to write formulas you can paste in, and using it to clean up the kind of messy data that shows up in real exports. I'll be specific about what to type and where the whole approach falls down.

Getting a formula without knowing the syntax

The trick is to give the AI three things: what your columns actually contain, what you want the answer to be, and which program you're using. Excel and Google Sheets share most functions but differ in small ways (Excel has TEXTJOIN and dynamic arrays; Sheets has QUERY, which is its own little language), so naming the tool matters.

Here's a prompt I'd actually type into ChatGPT, Claude, or Gemini:

"I'm in Google Sheets. Column A has full names like 'Maria Alvarez-Reyes'. Column B has email addresses. I want column C to show just the first name. Give me a formula for cell C2 that I can fill down, and explain what each part does in one line."

You'll get something like =INDEX(SPLIT(A2," "),1) with a short explanation. Paste it into C2, press Enter, then drag the little blue square at the bottom-right of the cell down the column. That's the whole loop.

A few habits make this dramatically more reliable:

• Paste a few real rows. Instead of describing your data, copy five or six actual rows into the chat. The AI catches things you'd forget to mention, like a stray middle initial or names in "Last, First" order.
• Say what should happen to blanks and errors. "If column B is empty, leave C blank instead of showing an error" saves a second round trip. Otherwise you get formulas that break on the one empty cell in row 400.
• Name the cell. Ask for "a formula for C2," not "a formula." You want relative references (A2, B2) that shift correctly when you fill down, and being concrete gets you that.

When the formula errors out

Paste the exact error back. "I used that and got #REF! in C2" is enough. Better still, tell it what the cells look like: some errors come from text that looks like a number, dates stored as plain text, or extra spaces. The AI can only fix what it can see, so the more of the actual mess you show it, the faster you're done.

The formulas worth asking for by name

You don't need to memorize functions, but it helps to know what's possible so you can ask. A short menu of things people don't realize they can request:

• Pulling one table's data into another by matching an ID (the classic "look up this customer's plan by their email"). This is XLOOKUP in modern Excel or VLOOKUP in older versions, and it's the single most useful thing to automate.
• Counting or summing only rows that meet conditions — "total revenue for orders in Texas over $500." Ask for SUMIFS or COUNTIFS.
• Flagging duplicates so you can spot the same person entered twice.
• Extracting the domain from an email, the area code from a phone number, or the year from a date. These are one-liners once someone writes them for you.

If you use Microsoft 365 or Google Workspace, there's an even shorter path for simple stuff. Copilot lives inside Excel and Gemini lives inside Google Sheets, so you can highlight a range and ask in a side panel, "add a column that grades each score as Pass or Fail with 70 as the cutoff." It writes the column for you. I still prefer the copy-a-formula-into-a-chat method for anything tricky, because you can see and understand what you're pasting, but the built-in assistants are faster for the obvious tasks.

Cleaning up genuinely messy data

Real exports are ugly. Names in ALL CAPS, dates in four different formats, "N/A" and "n/a" and blank all meaning the same thing, phone numbers with and without dashes. Two ways to attack this, and they suit different sizes of job.

Small batch: paste it in, get it back clean

If you have a few dozen rows, just paste them into ChatGPT or Claude and say what you want:

"Here are 40 rows of contacts. Standardize the phone numbers to (XXX) XXX-XXXX, fix the capitalization on names to proper case, and change any 'N/A' or blank company to 'Unknown'. Return it as a table I can copy back into Sheets."

You copy the result, paste it back, and you're done. This is genuinely the fastest option for one-off cleanups, and Claude in particular handles a few hundred rows in one go without losing the plot.

Bigger batch: get formulas or a repeatable recipe

Once you're past a few hundred rows, or you'll be doing this same cleanup every month, don't paste the data. Ask for a method instead. "Give me formulas in helper columns that clean this, so I can apply the same steps every month" gets you something you can reuse. Common building blocks: TRIM to strip extra spaces, PROPER to fix capitalization, CLEAN for invisible characters that come from PDF copies. Ask for them by describing the symptom, not the function name.

The honest limitations

This is where I have to be straight with you, because the failure modes are real.

AI cannot see your spreadsheet unless you show it. A chatbot has no idea what's in your file. Everything depends on you pasting representative rows or an accurate description. Vague input gets a formula that works on the data it imagined, not yours.

It will occasionally invent a function or use the wrong dialect. If a formula errors immediately, the most common cause is Excel-vs-Sheets syntax, or a made-up function name. Paste the error, name your program again, and it corrects itself. Don't assume the first answer is right — test it on a couple of rows you can verify by eye.

Never paste sensitive data into a public chatbot. Customer records, medical info, anything with real names and financial details — keep it out. For those, describe the shape of the data with fake examples and ask for the formula, then apply it yourself to the real file. If your workplace provides an enterprise version of a tool with a data agreement, that's a different situation, but the free consumer chatbots are not the place for private records.

Check the math on anything that matters. For a formula that sums money or feeds a report, spot-check a few rows manually. AI is very good at spreadsheet logic and still occasionally wrong. Two minutes of checking beats sending a boss a number that's off by a column.

None of this requires learning to code. It requires learning to describe your problem clearly and to sanity-check the answer, which are skills worth having anyway.