How to Get AI to Write Excel Formulas for You

Why Writing Formulas by Hand Is Slowing You Down (and How AI Now Fits Inside Excel)

You spend twenty minutes hunting through documentation for the right nested IF syntax, only to get a #N/A error because your VLOOKUP referenced the wrong column. Then the "numbers" in your sum column turn out to be text stored as numbers, and the whole calculation silently returns zero. This is the daily tax of manual spreadsheet work: formula syntax, lookup errors, and hours lost to cleaning data before any real analysis can begin.

That tax is no longer unavoidable. AI formula generation is now an official, mainstream Excel capability, not a hack. Microsoft states that Copilot in Excel helps speed up spreadsheet tasks like "adding columns and formulas, formatting tables, and coming up with insights about your data." And the landscape has shifted well beyond pasting prompts into a chat window: by 2026, ChatGPT for Excel reached general availability in May, and Claude for Excel is generally available on Pro, Max, Team, and Enterprise plans. Both now work as in-workbook add-ins rather than upload-only chat tools, editing your grid directly.

The capability, in other words, has arrived. So why do so many people still get wrong answers?

The problem isn't the tools — it's how most people use them. Two failure patterns show up consistently across the research:

• Vague prompts produce vague formulas. Asking an AI to "analyze this feedback data" gets you generic output; asking it to "find the top 3 complaint categories and show how they trend by month" gets a targeted, structured result.
• Trusting outputs blindly. No source in the research claims AI formulas can be accepted unreviewed — every analysis emphasizes checking results against your source data.
• Missing structural traps. AI formula tools fail on renamed columns, text-stored numbers, and mixed date formats — the messy realities of real spreadsheets.

There's also a deeper nuance worth internalizing: as one analysis puts it, AI cannot safely perform complex calculations or the deterministic logic encoded inside formulas. The formula itself remains your reliable calculation layer; AI is the writer, not the calculator. That's actually good news — it means a well-prompted, human-verified formula is just as trustworthy as one you typed by hand, at a fraction of the time cost.

The gap between "AI exists" and "AI works for me" is a skill gap, and it's a learnable one. Precise prompting, planted test traps, and a verification habit separate people who save hours from people who quietly import errors into their reports. Agent skills — instruction packages that teach AI agents exactly these kinds of procedures — are one way to codify that expertise, and SkillzBench is building a free directory to help you discover and vet which skills actually deliver. The sections that follow turn these principles into a practical workflow you can apply to your own workbooks today.

The Prompting Playbook: Getting Accurate Formulas and Data-Cleaning Logic on the First Try

The difference between a formula that works on the first try and one you spend twenty minutes debugging usually comes down to a single sentence: your prompt. AI tools don't read your mind — they read your instructions — and vague requests produce vague spreadsheets.

Compare two prompts against the same customer feedback dataset. "Analyze this feedback data" invites the AI to guess what you want, and you'll get a generic summary. "Find the top 3 complaint categories and show how they trend by month" specifies the calculation, the grouping, and the output shape — and, as tool comparisons show, it reliably produces targeted, structured results. The dataset didn't change; the specificity did.

Data cleaning needs the same treatment, with one addition: demand the logic. When you hand over a messy export, ask the AI to show its transformation steps — how it handled duplicates, missing values, and date parsing — before you accept any output. This matters because LLMs process spreadsheets linearly, token by token, and by row 50 they may have "forgotten" the headers in row 1, causing misalignments like mistaking a Debt figure for Revenue, as V7 Labs notes. Exposed logic is logic you can audit.

Three prompt patterns that consistently improve first-pass accuracy:

• Name your columns and expected output explicitly ("SUM column D, grouped by the values in column B, results in a new table").
• For cleaning, require a step-by-step account of duplicates, missing values, and date parsing before any formula is written.
• Ask the AI to explain each generated formula in one sentence a reviewer can check — the InfiniSynapse test protocol treats this as the dividing line between chat output and real in-Excel analysis.

That one-sentence-explanation trick is undervalued. If the AI can't describe what its formula does in plain English, you've found a problem before it reaches your numbers. And because no tool replaces checking results against source data, a formula you can explain is a formula you can verify.

One more habit separates durable outputs from disposable ones: insist on formula-driven, not hard-coded, results. If the AI types "412" into a cell, that number never updates when your assumptions change. If it writes a formula that computes 412, your workbook recalculates itself next month. For recurring work, persist those cleaning steps in Power Query or a template so you're not re-teaching the workbook every run.

If you want to push this further, agent skills can automate the surrounding workflow — cleaning, formatting, verification. SkillzBench is building a free, verified directory and MCP connector so you can browse, benchmark, and equip those skills per agent, with security scanning and Lift scores to show which ones actually help.

Trust but Verify: The 15-Minute Trap Test Before You Rely on Any AI Formula Tool

The fastest way to lose a weekend is trusting an AI-generated formula that quietly summed the wrong column. Large language models read a spreadsheet token-by-token, but your workbook is a two-dimensional grid — and by row 50, a model may have effectively "forgotten" the headers in row 1, producing misalignments as serious as mistaking a Debt figure for Revenue, as V7 Labs explains.

That structural limitation is why every credible source agrees on one rule: no AI formula output should ship unreviewed. As Querio's comparison puts it, none of these tools "replaces checking results against your source data." The good news is that you don't need a full audit to find out whether a tool deserves your trust — you need 15 minutes and a few planted traps.

InfiniSynapse's planted-trap test works like this: take a small copy of a real workbook and deliberately break it in three ways. Rename a join key (Account Key becomes Account ID), store an amount as text ("$1,234.00"), and keep a trailing TOTAL row in your data range. Then ask the tool for a lookup and a SUM.

• Does the tool notice the renamed key, or does the lookup silently fail or match the wrong column?
• Does it flag the text-stored amount, or silently coerce it and hope for the best?
• Does it exclude the TOTAL row from the SUM, or double-count your data?

InfiniSynapse's testing found that tools that skip the rename or silently coerce text failed their formula column outright. A tool that passes all three traps is far more likely to behave when your real data is messier than you remember.

Beyond the traps, run three quick checks before accepting any generated formula. Confirm that dates and amounts landed as usable values, not text strings that break downstream calculations. Verify your workbook's calculation settings haven't been changed to manual. And ask the AI to explain each generated formula in one sentence a reviewer can check — if it can't, that's a red flag in itself.

One caution deserves its own paragraph: never upload sensitive data to any AI tool before compliance has confirmed coverage. Querio and InfiniSynapse both warn against pasting PHI or confidential records into upload-based chat tools, which should only ever see a sanitized copy. The subscription price is only part of the real cost — review hours are the other part, and a tool that fails your trap test will burn far more of them than it saves.

The same trust-but-verify logic applies to the AI agents and skills increasingly writing these formulas for us. SkillzBench is building exactly this kind of pre-flight checking for agent skills — security scanning, version pinning, and sandboxed benchmarking — so you can see how a skill performs before it touches your workbooks.

Matching the Tool to the Task: Copilot, ChatGPT, Claude, and Bulk Work

The right AI tool depends less on which model is "smartest" and more on where the formula needs to land. Microsoft 365 Copilot writes formulas directly into workbook cells as editable formulas, while ChatGPT delivers text-based formulas you copy into Excel yourself — a small distinction that shapes your whole workflow, since an answer in a chat pane isn't the same as in-Excel analysis.

On cost, the trade-off is straightforward. The Copilot add-on runs $30 per user per month on top of a Microsoft 365 license, while ChatGPT Plus costs $20 per month and includes file uploads. Claude for Excel requires a Pro plan ($20/month), Max (from $100/month), Team, or Enterprise. None of these tools can be judged on price alone, though, because review time is the hidden line item. As one comparison puts it, "the subscription price is only part of the cost — factor in the time your team spends reviewing outputs, too." A cheap chat seat that forces exports can cost more in review hours than an in-grid seat.

Performance claims deserve special skepticism. A vendor-run benchmark of 20 finance tasks reported 77.3% accuracy versus 66.3% for Claude for Excel — but the source itself flags it as a vendor claim, not a neutral test. Similarly, claims that one bulk engine processes up to 1,000 rows per minute while Copilot caps out around 100 function calculations per 10 minutes (per Microsoft's own documentation) come from a competitor's blog. No independent accuracy benchmark for AI-generated Excel formulas exists in any of the available research.

Where tools genuinely differ is task type:

• In-grid workbook edits and recurring monthly reports favor Copilot or the ChatGPT/Claude Excel add-ins
• One-off analysis of an uploaded file copy suits ChatGPT-style chat — never upload sensitive data until compliance confirms coverage
• Bulk row-by-row work across thousands of rows is where most assistants fail; Copilot cannot reliably classify or tag entire columns at scale
• Function-based prompting (GPT(), COPILOT(), =AI() in Sheets) lets you copy a prompt down rows inside cells

Whatever you choose, test it before trusting it. Rename a join key, store an amount as text, keep a trailing TOTAL row, then ask for a lookup and a SUM — tools that skip the rename or silently coerce text will fail your real work too. And ask the AI to explain each generated formula in one sentence a reviewer can check, because AI cannot safely perform the deterministic logic encoded inside formulas.

Agent skills can automate the rest of this workflow — SkillzBench is building a free directory and benchmarking platform so you can see which skills actually lift performance before you wire them into your agents.

Go Further: Automating the Whole Spreadsheet Workflow with Agent Skills

Getting one formula right is a win — but the real payoff comes when the AI handles the whole recurring workflow, not just a single calculation. As one 2026 tool review put it, "Recurrence decides": first-pass quality across tools was close, but the gap opened when it came time to re-run "next month" without re-teaching the workbook from scratch.

Think about the spreadsheet work you repeat every month. It usually involves the same steps: cleaning messy imports, standardizing date formats, tagging or classifying entire columns, then rebuilding the same calculations. Most built-in assistants struggle here — comparative testing found that tools like Copilot "cannot reliably apply prompts across thousands of rows (for example, classifying or tagging entire columns)." A one-off chat answer doesn't survive contact with month two.

That's where agent skills come in. An agent skill is an instruction package (a SKILL.md file) that teaches an AI agent — in Claude Code, Codex, Cursor, Gemini CLI, or a chat app — how to handle a specific task the same way every time. Instead of re-prompting your cleaning rules each month, you install a skill once and the agent applies it consistently, whether that's deduplicating rows, parsing mixed date formats, or writing the transformation logic into a refreshable Power Query template rather than hard-coded values.

A few principles make these workflows durable:

• Persist cleaning steps in Power Query or a saved template, so the work refreshes instead of restarting.
• Ask the agent to explain its transformation logic — including how it handled duplicates, missing values, and date parsing — so a reviewer can verify it.
• Prefer formula-driven output over pasted values, so results recalculate when assumptions change.

One caution before you equip an agent with skills from the open internet: published research found roughly 26% of skills carry at least one vulnerability. Installing an unvetted skill that touches your financial workbooks is a real risk, not a theoretical one.

This is the problem SkillzBench is built to solve. It's a free, independent directory of agent skills that pins every version to a commit SHA, validates and license-checks each skill, and runs security scanning and sandboxed benchmarking — testing every skill with the same model, with and without it, to measure the actual Lift. A free MCP connector then lets you equip any agent with only the skills you've chosen, and alerts keep you current as upstream skills change. Everything — the directory, the connector, the benchmarks — is free.

Skills, proven. Join the SkillzBench waitlist to get notified when the directory and MCP connector go live, and be first to browse security-scanned, benchmarked skills for your spreadsheet workflows.