How to Automate Excel With AI Without Breaking Your Workbook

Posted on Nov 5 • Originally published at daylongs.com

Automating Excel with AI comes down to one loop: describe your columns and a few sample rows, let the model draft a formula, a cleanup plan or a script, then verify the result on a copy before it touches the real workbook. The draft takes seconds. The check is your job, and skipping it is how a plausible-looking formula quietly totals the wrong column for a month.

This is a practical walkthrough, not a tool ranking. It leans on Microsoft's documentation and on what actually pastes cleanly into Microsoft 365, and it says so when a detail could not be confirmed.

Think in three layers, each saving more time and carrying more risk than the one below it. Layer one is formulas: you ask for a SUMIFS or an XLOOKUP and paste it into one cell. Layer two is cleanup: you ask for an order of operations for trimming spaces, splitting columns and fixing dates, then build it in Power Query so it replays next week. Layer three is scripts: Office Scripts or VBA that touch thousands of cells in one run.

Spend a few weeks on layer one before touching layer two. Layer three earns its place only when you do the identical job every week, because writing and debugging a script for a one-off task takes longer than doing the task by hand.

If your basics are shaky, keep a formula cheat sheet open in another tab. You can only catch an AI mistake in a formula you could have written yourself, slowly.

A good prompt has four parts: the column layout, two or three sample rows, the exact result you want, and your Excel version. Leave any one out and the model guesses, and the guess is usually wrong in a way that looks right.

The strong prompt returns something like =SUMIFS(D2:D500,B2:B500,F2) with an explanation you can follow. Always add the request to explain it line by line. If you cannot follow the explanation, you are not ready to ship the formula.

These three work in Microsoft 365 and Excel 2021 or later. Adjust the ranges to your sheet.

All three return #NAME? in Excel 2016 and 2019. Asking an AI to rewrite an existing formula with LET is one of the more reliable requests you can make; asking it to invent a function it half remembers is one of the least.

Split cleanup by frequency. A one-time fix can be formulas or Copilot. A weekly fix belongs in Power Query, which records your steps and replays them when new data arrives. Use the AI as the planner: describe the columns and ask in what order you should split, trim, convert dates and remove duplicates, then build those steps yourself.

Copilot in Excel needs the workbook saved to OneDrive or SharePoint with AutoSave on, and a plan that includes it. A greyed-out button on a file sitting on your desktop is expected, not a bug. Menu names and plan details shift, so check Microsoft's current FAQ before you assume something is missing.

For scripts, fix a test routine before you run one. Save a copy named _test. Ask the AI to comment every line and to list any line that deletes or overwrites data. Run on a 10-row sample first, then compare row counts and totals against the original. VBA cannot be undone with Ctrl+Z, so the copy is your undo button.

Two closing habits. Never paste customer names, account numbers or salaries into a public chatbot; use anonymized samples or a company-approved tool. And when the AI's answer references a function you have never heard of, assume it is invented until the documentation says otherwise.

The full set of worked prompts and the Copilot licensing table are in the original post:

Keep the function cheat sheet open while you test:

Keep the function cheat sheet open while you test: Also read: Excel Functions