In this guide (5 steps)
  1. Describe your columns
  2. Fix broken formulas
  3. Clean messy data
  4. Analyze with an upload
  5. Verification checklist

Guides · 8 min read · Updated Oct 15, 2026

How to use AI for Excel and Google Sheets formulas and data cleanup

Get working spreadsheet formulas, clean messy data and analyze tables using ChatGPT, Claude or Copilot, with prompts and a verification checklist.

In this guide (5 steps)
  1. Describe your columns
  2. Fix broken formulas
  3. Clean messy data
  4. Analyze with an upload
  5. Verification checklist

You don't need to memorize formulas anymore. You need to describe your sheet precisely.

Step 1

Describe your columns

AI can't see your file unless you upload it, so tell it the layout:

I'm using Google Sheets. Column A = order date, B = customer name, C = product, D = quantity, E = unit price. Data is in rows 2-500.
Write a formula for F2 that calculates the order total, and a formula that sums total revenue for "Widget Pro" in March 2026.
Explain each formula in one sentence.
Step 2

Fix broken formulas

This Excel formula returns #N/A: =VLOOKUP(B2,Prices!A:B,2,FALSE)
Column B has product codes; Prices!A has codes too. What are the likely causes and how do I fix them? Suggest an XLOOKUP version.

(The usual culprit: stray spaces or numbers stored as text. AI will suggest TRIM or VALUE.)

Step 3

Clean messy data

Give me formulas to: split "Last, First" in A into two columns; standardize phone numbers in C to (555) 123-4567; flag duplicate emails in D.
Step 4

Analyze with an upload

ChatGPT, Claude and Gemini can read uploaded CSV/XLSX files on many plans. Ask: What are the top 3 trends in this sales data? Show a table by month and point out anything unusual. Remove personal data before uploading.

Step 5

Verification checklist

  • Test the formula on 3 rows you can check by hand.
  • Ask the AI to show its working for any summary number.
  • Lock ranges with $ before dragging formulas down.
Check before you send: AI can invent facts, names and numbers. Read the output once, fix anything wrong, and never paste passwords, client secrets or private health/financial data into a chatbot.
Open This spot is open for sponsors. Put your AI tool in front of readers who are learning to use AI right now. See sponsor options →

Quick tips on this topic

⚡ Describe your columns, get a formula

AI can't see your sheet. Tell it what's in each column and you'll get a working formula.

⚡ Paste the error, get the fix

#N/A, #REF, #VALUE: paste the formula and the error, and ask for likely causes.

⚡ Clean names in seconds

Splitting 'Last, First' or fixing ALL CAPS? Ask for the cleanup formulas.

Swipe 50+ quick AI tips →One tip per screen, 10 seconds each
Get one practical AI how-to every weekday. Free, 3-minute read, no hype.

We only use your email for the newsletter. Privacy.