How to Automate Excel With AI: Formulas, Cleanup and Scripts, Safely (2026)

To automate Excel with AI, describe your columns and a few sample rows to a chatbot or to Copilot in Excel, let it draft a formula, cleanup step or script, then verify the output on a copy before it touches your real workbook. That loop covers most of what US office workers want: faster formulas, cleaner data and fewer repeated chores.

My view is simple. AI writes the first draft in seconds and you own the check. This guide leans on Microsoft’s published documentation and current search results, and it does not pretend I personally tested every tool listed. Licensing and menu names are shifting, so treat the availability tables as a starting point and confirm on the vendor’s page. This is written with US Microsoft 365 users in mind.

How can you automate Excel with AI, and how far can it go?

Think of three layers. Each one saves more time and carries more risk than the one below.

Layer	What it does	Typical tools	Risk
1. Formula writing	Turns a sentence into a formula	ChatGPT, Gemini, Claude, Copilot in Excel	Low: you see the result
2. Data cleanup	Fixes spaces, formats, duplicates, splits columns	Power Query, Copilot, formulas	Medium: can damage source data
3. Task automation	Runs a repeated sequence as code	Office Scripts, VBA	High: hard to undo

I’d spend a few weeks on layer 1 before touching layer 2. Layer 3 earns its place only when you do the identical job every week. Writing and debugging a script for a one-off task takes longer than doing the task by hand.

If your Excel basics are shaky, keep 10 Excel formulas everyone should know and the Excel functions cheat sheet open in another tab. You can only catch an AI mistake in a formula you can read.

What should a ChatGPT Excel formula prompt look like?

A good prompt has four parts: column layout, sample rows, the result you want and your Excel version. Skip any one and the model guesses, and the guess is usually wrong in a way that looks plausible.

Weak prompt: “Give me a formula to total sales.”

Strong prompt:

Column A is order date, B is region, C is rep, D is amount. Data runs from row 2 to row 500. Sample row: 2026-10-03 / West / Smith / 1200. I use Microsoft 365. In F2 I type a region name. Give me a formula that totals amounts for that region and explain it line by line.

That produces something you can paste, like =SUMIFS(D2:D500,B2:B500,F2). Here is the same idea across common jobs.

Job	What to include in the prompt	What you get back
Conditional total	Column names, where the criteria cell is	SUMIFS
Pull a value from another table	Both tables’ headers, the key column	XLOOKUP
Extract rows that meet conditions	Two or more conditions, output location	FILTER
Shorten a long formula	The full current formula	A LET version
Diagnose an error	Full formula plus the error code	Cause and a fix

Always add “explain it line by line.” If you cannot follow the explanation, you are not ready to ship the formula. For choosing a model, see ChatGPT vs Claude vs Gemini, and for prompt habits in general, ChatGPT prompt mastery.

Which XLOOKUP, FILTER and LET examples actually work?

These three paste cleanly into Microsoft 365 or Excel 2021 and later. Adjust the ranges to your sheet.

XLOOKUP: pull a unit price by product code

=XLOOKUP(TRIM(A2),Prices!A:A,Prices!C:C,"Code not found")

TRIM is there because a trailing space on a code is the classic cause of #N/A. I cover that in fixing VLOOKUP #N/A errors. The fourth argument is what the cell shows when no match exists, instead of an error.

FILTER: only West-region rows of $1,000 or more

=FILTER(A2:D500,(B2:B500="West")*(D2:D500>=1000),"No matches")

Multiplying conditions is AND; adding them is OR. FILTER spills downward, so the cells below must be empty or you get a #SPILL! error.

LET: name the pieces of a long formula

=LET(total,SUM(D2:D500),part,SUMIFS(D2:D500,B2:B500,F2),IF(total=0,0,part/total))

It returns the share of sales for the region in F2. You avoid repeating the same calculation and the formula reads better. Asking an AI to “rewrite this with LET” is one of the more reliable requests you can make.

All three return #NAME? in Excel 2016 and 2019. The full triage order is in Excel formula not working.

How do you clean messy data with AI and Power Query?

Split the work by frequency. Do it once: formulas or Copilot. Do it weekly: Power Query.

Symptom	Formula	Note
Leading, trailing or doubled spaces	=TRIM(A2)	Use =TRIM(CLEAN(A2)) to strip line breaks too
Numbers stored as text with commas	=VALUE(SUBSTITUTE(B2,",",""))	Result is a true number
Keep the first word of a name	=TEXTBEFORE(A2," ")	Newer Microsoft 365 function
Mixed phone formats	Give the AI five samples and ask for a rule	You verify the rule

Power Query (Data tab, Get & Transform) records your cleanup steps and replays them when new data arrives. Use AI as the planner here. Describe your columns and samples and ask, “In what order should I split columns, trim spaces, convert dates and remove duplicates?” Menu names differ by version, so if the AI names an item you cannot find, pick the closest option on screen rather than arguing with the chatbot.

What are the Copilot in Excel requirements and licensing rules?

Copilot in Excel opens from a ribbon button and lets you ask for formula columns, highlights, sorting and summaries on a table in plain English. It is convenient, but the conditions are picky.

Microsoft’s Copilot in Excel FAQ says the workbook must be saved to OneDrive or SharePoint with AutoSave turned on. A greyed-out Copilot button on a file sitting on your local drive is the most common complaint, and this is usually why.

Tool	Conditions (summary)	Where to confirm
Copilot pane in Excel	Eligible Copilot license, cloud-saved file, AutoSave on	Microsoft support FAQ
COPILOT function	Copilot subscription, staged rollout, 100 calculations per 10 minutes	Function documentation
Office Scripts	Commercial or education Microsoft 365, OneDrive for Business	Office Scripts platform limits
Gemini in Google Sheets	A supported Google plan	Google Help Center
ChatGPT, Claude and similar chatbots	Account only for formula writing, with free-tier limits	Each vendor’s pricing page

I compiled that table from searches in early November 2026 and some cells cannot be stated in one line; plan details differ and change. If the button is missing at work, your admin may have turned it off, so ask IT about license assignment first.

The COPILOT function documentation shows the form =COPILOT(prompt, context) and notes it can be blocked in workbooks carrying certain confidentiality labels. The line to remember: it uses AI, so it can be wrong, and the same inputs may return different results later. Keep totals and lookups in regular formulas. Use COPILOT for jobs like labeling 100 survey comments as positive or negative.

How do you test AI-written Office Scripts and VBA safely?

A script can change thousands of cells in one run, so fix a test routine before you ever run one.

1. Save a copy of the workbook as _test.
2. Ask the AI to comment every line and list any line that deletes or overwrites data.
3. If a commented line makes no sense to you, don’t run it.
4. Run it on a 10-row sample sheet first.
5. Compare row counts and totals against the original.
6. Only then run it on the full _test copy, and finally on the real file.

Here is an Office Script that trims leading and trailing spaces from every text cell on the active sheet.

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet();
  const range = sheet.getUsedRange();
  const values = range.getValues();
  const cleaned = values.map(row =>
    row.map(cell => (typeof cell === "string" ? cell.trim() : cell))
  );
  range.setValues(cleaned);
}

One catch. getValues and setValues handle values only, so any formula inside the used range gets replaced by its current result. Run this on a sheet that mixes formulas and data and your formulas are gone. That is the most common way an AI script goes wrong: the syntax is fine, but the model has no idea what is in your sheet.

Microsoft’s Office Scripts platform-limits page lists supported environments and license requirements. Community posts say the Automate tab can be missing on personal Microsoft 365 plans; check that page for the current official position.

VBA cannot be undone with Ctrl+Z, needs an .xlsm file, and many employers block macros. Check your policy before you run anything.

What mistakes does AI make in Excel, and how do you spot them?

Four patterns show up again and again.

Mistake	What you see	How to check
Invented function	#NAME?	Type the name; does autocomplete show it?
Function missing in your version	#NAME? only on your PC	Re-ask and state your version
Shifted range	Total is slightly off	Formulas tab, Evaluate Formula
Regional separator	Argument error	Commas vs semicolons

Here is a failure case, a composite of a common pattern and not a real person. An analyst asks an AI for month-over-month growth on a sales table and gets =(C2-B2)/B2. It works, so they fill it across twelve months. But the April value was typed as text by a previous owner of the file, and Excel treats it as zero in that math, so May growth shows an absurd jump. That number goes into a slide deck. The formula wasn’t wrong. The input was.

The AI cannot see that. Build three habits instead: does the total match your gut sense, does the first and last row match a hand calculation, and does =COUNT(range) equal =COUNTA(range)? The last check catches text-stored numbers every time.

Is it safe to put work data into an AI chatbot?

Often it isn’t. Whatever you paste may be stored or used according to the service’s terms and your settings, and many employers prohibit outside AI tools altogether. Read the vendor’s privacy documentation for the exact handling.

Run this checklist before you paste:

• Does it contain names, phone numbers, SSNs or account numbers?
• Does it show client names or contract values?
• Does your company have an AI policy?
• Would structure alone be enough? (Usually it is.)

Formula help doesn’t need real data. Five rows with “Jane Doe” and round numbers like 100, 250, 80 will do. Copilot inside Microsoft 365 is described as processing data within your organization’s Microsoft 365 environment, but the details depend on your license agreement and admin settings, so ask your IT team. If chatbot memory is your worry, ChatGPT memory and privacy covers what to switch off.

Which tasks should you hand to AI first?

This is the order I’d follow.

Order	Task	Why
1	Formulas you can never remember	A bad result costs you a copy
2	Explaining formulas in an inherited file	Instant payoff
3	Text-cleanup rules	Easy to read and verify
4	Power Query step planning	Skeleton of recurring work
5	Office Scripts	Only for weekly repeats

For wider workflows, see best AI tools for workers and AI productivity tools.

A last honest note on time. AI does shorten the time you spend writing formulas. It does not shorten verification, and in week one you will probably spend more time checking than you saved. That is fine. Once checking becomes habit, speed arrives and errors drop. Just keep one fact in view: the person whose name is on the numbers is still you.

This article is general information written for US users on Microsoft 365. Feature names, license conditions and pricing reflect searches in early November 2026; confirm current details on Microsoft’s and Google’s official pages. Save a copy of any important workbook before changing it.