How to Use AI to Write Excel Formulas: A Step-by-Step Beginner’s Guide

Every Excel user knows the moment: you know exactly what you want the spreadsheet to do — total the sales for one region, flag every overdue invoice, pull a price from another sheet — and you have no idea how to say it in formula. You can picture the answer; the syntax will not come. This is the single best use of AI in spreadsheets, because the gap between “what I want” and “=SUMIFS(…)” is precisely a translation problem, and translation is what these tools do best. Microsoft has built the translator into Excel itself as Copilot, which Microsoft says can “perform calculations and apply formulas across multiple sheets,” and OpenAI’s ChatGPT will happily write and explain formulas in a chat window for you to paste in. This guide walks through the process step by step — how to describe a formula so the AI gets it right first time, how to insert and test what it gives you, and how to check the result before it quietly poisons a report. It assumes no formula knowledge beyond knowing what a column is.

Step 1: Describe the result, not the formula

The mistake beginners make is trying to meet the AI halfway — half-remembering function names and feeding it fragments like “VLOOKUP something with IF?” Do not. Your job is to describe the destination with total clarity; the AI’s job is the route. A good formula request has four ingredients. First, the layout: which columns hold what (“Column B has dates, column C has region names, column D has order values”). Second, the location: where the answer should appear (“in column E, starting at row 2”). Third, the logic in plain words, including edge cases (“add up the order values, but only for rows where the region is North and the date is in March 2026; if a row has no date, leave the result blank”). Fourth, the request for an explanation: “Give me the formula and explain each part in simple words.” That last ingredient is not decoration — the explanation is your first checking tool, and it is also how you learn. Microsoft notes that Copilot in Excel can also work from your data conversationally, generating formulas across sheets from a description, but the discipline is identical whether the AI sits inside Excel or in a chat tab: layout, location, logic, explanation.

Step 2: Ask — inside Excel or in a chat

If you have Copilot in Excel, open it from the Copilot icon and type your request in the pane. Microsoft describes three modes, and for formula work the distinction matters: in chat mode Copilot will discuss and suggest without changing your workbook, while in edit mode it can apply the formula directly. A sensible routine is to start in chat mode for anything new — read the suggested formula and its explanation while your sheet stays untouched — and use edit mode once you trust the pattern. If you are using ChatGPT, the workflow is copy-based: paste your four-ingredient description, receive a formula, and copy it into the target cell. OpenAI’s documentation for data analysis emphasises giving the tool structured context — clear column names and one record per row — and the same advice applies when you describe a sheet you have not uploaded: the clearer your description of the layout, the fewer assumptions the AI has to invent. Either way, one habit separates good results from junk: iterate in the same conversation. “That worked for March — now make it ignore rows where column D is empty” is a far better second prompt than starting over, because the AI already holds your layout in context. This iterative briefing is the same skill our prompt checklist teaches for chatbots generally, applied to a grid.

Step 3: Insert it — and read what you inserted

When Copilot offers to apply a formula, or when you paste one from ChatGPT, pause before you celebrate. Click the cell and read the formula in the formula bar, comparing it against the explanation you asked for. You are checking three things. Does it reference the columns you actually named — not the ones the AI assumed? Does it start and end where your data starts and ends? And does it handle your stated edge cases, or silently drop them? If the explanation and the formula disagree, believe the formula — it is the one that will run — and ask the AI to reconcile them. This thirty-second read is the cheapest insurance in all of spreadsheet work, and it is the step everyone skips.

Step 4: Test on one row you can verify by hand

Never fill a new formula down a column untested. Pick one row where you already know the right answer — calculate it on paper or with a calculator — and compare. Then pick a second, nastier row: the one with the blank date, the zero value, the duplicate name. AI formulas most often fail exactly there, at the edges you mentioned in your prompt and the ones you forgot to mention. If both test rows agree with your hand calculations, fill the formula down and do one final sanity check on the totals: does the sum of the new column bear a sensible relationship to the sum of the source column? A total that is wildly larger or smaller than expected is the smoke that signals fire. These habits belong to a bigger discipline covered in our pillar guide, AI for Excel and Data, and the failure catalogue in 7 AI spreadsheet mistakes — read both before AI formulas touch anything that feeds a decision.

Step 5: Ask the AI to explain, simplify and harden

Once a formula works, three follow-up prompts multiply its value. “Explain this formula to a beginner” turns a one-off fix into actual learning — next month you may write it yourself. “Is there a simpler way to get the same result?” often replaces a fragile nested construction with something readable. And “What would make this formula return a wrong answer?” is the most underused prompt in spreadsheet AI: the tool is genuinely good at enumerating its own assumptions — text-formatted numbers, unexpected blanks, duplicate keys — which reads like a personalised checklist of what to verify in your sheet. Save the prompts that worked in a notes file, with the layout they assumed; formula prompting is a reusable asset, and a small library of proven requests will save you more time than any single answer. If your data itself is the problem — duplicates, mixed formats, mystery blanks — fix that first with our guide to cleaning messy data with AI, because no formula, however elegant, survives garbage input.

What this means

The formula was always the moat around spreadsheet competence. Millions of people who understand their own data perfectly — what should be counted, what should be excluded, what “overdue” means in their business — were locked out of acting on that understanding by a syntax designed in another era, and they paid the toll in hours, in errors, or in deference to whoever in the office “knew Excel.” AI formula help demolishes the moat but relocates the skill: from remembering SUMIFS argument order to specifying intent precisely and verifying results sceptically. For US readers, the comparison that matters is with every previous wave of office automation, each of which rewarded the people who learned to check the machine’s work rather than merely receive it. The worker who can now produce in an afternoon the formula-driven tracker they once requested and waited a week for has genuinely gained power — provided they keep the habit this guide ends on: the AI writes the formula, but the human signs it. Treat every AI formula as a draft from a brilliant, careless intern, test it twice, and the moat becomes a bridge.

There is a learning dividend hiding in this workflow, too, if you take it. Every explained formula is a free lesson pitched at exactly your level, on exactly your problem — a tutor that never tires of your questions. People who use AI this way report the same arc: within a few months they stop asking for whole formulas and start asking narrower questions, because they have absorbed the patterns — how lookups think, how conditions nest, where errors breed. The AI did not replace the skill; it apprenticed them into it. The opposite arc exists as well: paste without reading, and a year later you are exactly as helpless as the day you started, only faster. Which arc you get is decided entirely by the habit in Step 3 — reading what you inserted. The formula bar is a classroom. Sit in it for thirty seconds a time and the tuition compounds.

Bottom line

Describe the result you want, let AI write the formula, then check the answer against a row you can verify by hand. The syntax barrier is gone; the need to know what “correct” looks like is not.

Read Next

• AI for Excel and Data: How AI Helps With Spreadsheets, From Formulas to Full Analysis
• 7 AI Spreadsheet Mistakes to Check Before You Trust the Answer

Sources

• Get started with Copilot in Excel — Microsoft Support (accessed 7 November 2026)
• Data analysis with ChatGPT — OpenAI Help Center (accessed 7 November 2026)