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.
How far should you let AI go in Excel?
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.
What does a prompt that gets a working formula look like?
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.
- Weak: give me a formula to total sales.
- Strong: column A is order date, B is region, C is rep, D is amount, rows 2 to 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.
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.
Which XLOOKUP, FILTER and LET examples actually paste cleanly?
These three work in Microsoft 365 and Excel 2021 or later. Adjust the ranges to your sheet.
- XLOOKUP for 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, and the fourth argument is what the cell shows when nothing matches.
- FILTER for West-region rows of 1,000 or more: =FILTER(A2:D500,(B2:B500=”West”)*(D2:D500>=1000),”No matches”). Multiplying conditions means AND, adding them means OR, and the result spills downward, so the cells below must be empty.
- LET to 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 region's share of sales without repeating the same calculation twice.
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.
How do you clean data and run scripts without wrecking the workbook?
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:
See the worked prompts on daylongs.com
Keep the function cheat sheet open while you test: Also read: Excel Functions
Top comments (0)