You know what you want Excel to do but can’t remember the formula. AI chat tools like ChatGPT, Microsoft Copilot, Google Gemini and Claude are very good at turning a plain-English request into a working formula, and at explaining formulas you don’t understand.
The key is giving the AI enough detail. Here’s how.
The prompt template that works
A vague prompt gets a vague formula. Include four things:
- Your tool and version: Excel 365, Excel 2016, or Google Sheets. This decides which functions are available.
- Your layout: which columns hold what, and where the data starts.
- What you want: the result and where it goes.
- Edge cases: blanks, missing values, duplicates.
Example prompt:
I use Excel 365. Column A has order dates, column B has customer names, column C has order amounts. Data starts in row 2 and goes to row 500. In F2 I have a customer name. Write a formula for G2 that returns the total amount for that customer in September 2026. Return 0 if there are no orders.A good answer would be a SUMIFS formula with the customer and two date conditions, plus a short explanation.
8 ready-to-use prompts
1. Write a lookup
In Google Sheets, Sheet "Products" has codes in A and prices in C. On Sheet "Orders", codes are in B. Write a formula for D2 that returns the price, or "Missing" if the code isn't found.2. Explain a formula
Explain this Excel formula step by step in simple words: =INDEX(C:C, MATCH(1, (A:A=F2)*(B:B=G2), 0))3. Fix an error
My formula =VLOOKUP(E2, A2:C100, 3, FALSE) returns #N/A even though the value exists. What are the likely causes and how do I check each one?4. Clean messy text
Column A has phone numbers in mixed formats like "(973) 1234-5678", "973 12345678" and "+97312345678". Write an Excel 365 formula that returns only the digits.5. Conditional logic
Write an Excel formula that returns "Overdue" if the due date in C2 is before today and the status in D2 is not "Paid", otherwise returns nothing.6. Dates
Write an Excel formula that calculates the number of working days between the start date in A2 and end date in B2, excluding the holidays listed in H2:H20.7. Simplify a long formula
Simplify this nested IF formula for Excel 365 and explain the new version: =IF(B2>=85,"A",IF(B2>=75,"B",IF(B2>=65,"C","F")))8. Conditional formatting rule
Give me a custom formula for Excel conditional formatting that highlights the entire row when column E says "Urgent". My data is in A2:H200.Always check the result
AI tools are helpful but not perfect. They sometimes use functions your version doesn’t have, mix up column letters, or misunderstand your layout. Before trusting a formula:
- Test it on a few rows you can check by hand. Pick an easy case and an edge case (blank cell, missing value).
- Ask for an explanation. If the AI can’t explain each part clearly, be cautious.
- Check function availability. XLOOKUP, FILTER, TEXTSPLIT and LET only work in newer Excel versions. You’ll see #NAME? in older ones.
- Watch for separators. Some regional settings use semicolons instead of commas between arguments. Ask the AI to adjust if needed.
- Paste the error back. If it fails, tell the AI exactly what you see (“returns #VALUE! when B2 is blank”) and ask for a fix.
Protect sensitive data
You don’t need to paste real data to get a formula. Describe the columns, or use made-up sample rows. Avoid pasting customer details, salaries or other confidential information into public AI tools, and follow your company’s AI policy.
AI built into Excel and Sheets
Microsoft 365 Copilot can write formulas and create columns directly in your workbook, and Gemini offers similar help in Google Sheets on eligible plans. See what Copilot can do in Copilot in Excel: What It Can Do.
Learn as you go
The best way to use AI is as a tutor, not just a formula machine. Ask it to explain each new function, then try writing the next one yourself. Our guides on 25 essential Excel formulas and writing better AI prompts will speed that up.