How to Use AI in Excel and Google Sheets (Formulas and Analysis)

I am not a spreadsheet wizard. For years, anything past SUM had me googling formula syntax and pasting things that didn’t work. AI quietly fixed that, and it’s probably the use that’s saved me the most pure frustration. You describe what you want in plain English, and it writes the formula. Here’s how to do it well, whether you use Excel, Google Sheets, or the AI built into them.

Quick note: the built-in AI features in Excel (Copilot) and Sheets (Gemini) evolve constantly, and exact menus change. This reflects mid-2026 — the approach below works regardless of which version you’re on.

The core trick: describe the result, get the formula

This is the whole game and it’s gloriously simple. Instead of remembering formula syntax, you describe what you want in words and let AI write it. You can do this two ways: with the AI built into your spreadsheet, or with any chatbot on the side.

Tell it: “In Google Sheets, write a formula that adds up column B only where column A says ‘Paid’.” Back comes =SUMIF(A:A,"Paid",B:B), ready to paste. No more hunting through formula documentation. The key is describing your columns and your goal clearly — that’s all it needs.

Use 1: Writing and fixing formulas

This is the bread and butter. Some prompts that work every time:

• “Write a formula to calculate the percentage change between cell B2 and C2.”
• “I need a formula that pulls the price from the Products sheet based on the item name in A2.” (It’ll reach for VLOOKUP or its modern cousins.)
• “This formula gives a #REF error: [paste formula]. What’s wrong and how do I fix it?”

That last one is underrated. When a formula breaks and you have no idea why, paste it in and ask. AI is excellent at debugging spreadsheet errors and, crucially, explaining them so you learn.

Use 2: Cleaning messy data

Real data is a mess — inconsistent capitalization, extra spaces, dates in five formats, names stuck together in one column. AI is great at telling you how to fix it. “How do I split a ‘Full Name’ column into first and last name in Excel?” or “Give me a formula to clean up inconsistent capitalization in column A.” It’ll hand you the formula or the menu steps. For big cleanups, describe the before and the after you want, and let it map the path.

Use 3: Actually analyzing your numbers

Beyond formulas, AI can help you think about the data. Paste a small table (or describe it) and ask: “What trends or outliers stand out here?” or “What’s a good way to summarize this sales data for a one-page report?” The built-in tools (Copilot in Excel, Gemini in Sheets) can go further and generate summaries, charts, and pivot tables from a plain-English request right inside your file. For sensitive data, keep it in the built-in tools rather than pasting into a general chatbot.

Use 4: Learning as you go

Here’s a bonus that compounds. Every time AI gives you a formula, ask it to explain how it works: “Explain that formula piece by piece.” Over a few weeks you’ll actually start understanding spreadsheets instead of just copy-pasting — which means you’ll need the AI less, not more. It’s the rare shortcut that also teaches you the long way.

The one habit that prevents disasters

Spreadsheets run on real numbers, often important ones, so verify before you trust. When AI gives you a formula, test it on a couple of rows where you know the right answer. Does =SUMIF actually total the right cells? Does the percentage match your hand calculation? AI gets formulas right most of the time, but “most of the time” isn’t good enough when the output feeds an invoice or a budget. A 30-second sanity check on known values saves you from a confidently wrong number propagating through your whole sheet.

A realistic example

I had a messy export of 500 orders — mixed-case names, dates as text, a total column that wouldn’t add up. I described the file to a chatbot and worked through it step by step: a formula to standardize the names, another to convert the text dates, and a fix for the total (the culprit was hidden spaces). Twenty minutes, zero formula-googling, and I understood each fix because I asked it to explain. The old me would’ve lost an afternoon to that.

Frequently asked questions

Do I need the paid AI features in Excel or Sheets? Not necessarily. The built-in tools (Copilot, Gemini) are convenient and work inside your file, but you can get most of the formula help from any free chatbot on the side.

Is it safe to paste my data into AI? For sensitive or confidential data, use the AI built into your spreadsheet rather than pasting into a general chatbot, and check your organization’s policy. For non-sensitive data, a chatbot is fine.

Can AI build charts and pivot tables? The built-in tools can generate charts, summaries, and pivot tables from a plain-English request. A side chatbot can tell you the steps to do it yourself.

What if the formula it gives me doesn’t work? Paste the error back and ask it to fix it — AI is great at debugging. And always test formulas on rows where you know the answer.

Does this work for both Excel and Google Sheets? Yes. Just tell the AI which one you’re using, because the function names occasionally differ. Say “in Google Sheets” or “in Excel” and it’ll give you the right syntax for your app.

I’m a complete beginner — is this really for me? Especially for you. You don’t need to know any formulas to start; you just describe what you want. And because you can ask it to explain each answer, it doubles as the gentlest spreadsheet tutor you’ll ever have.

Where to go next

Spreadsheets are one piece of using AI to cut busywork. For turning long documents into something usable in minutes, see my guide to summarizing long PDFs and documents with AI.

Leo Tan writes hands-on AI guides at AI Play Books. I am living proof you don’t need to be a spreadsheet expert to use this well. Got a great formula prompt? Tell me.