AI Does HelpCan AI do it? How far?

Can AI write Excel formulas for me?

AI Does Help·

Yes. An Excel formula generator is one of the few spreadsheet jobs AI can do end to end: describe your columns and the result you want in plain English, paste the formula it returns into the cell, and fill it down. What it won't do is know whether your description actually matches the sheet, so one check on your side is still part of the job.

Tools that work

There are three practical routes for how to use AI in Excel, and the free tiers cover most occasional formula work — the limits that bite are upload limits, not formula quality. Plans and prices change; check the current plan on the tool's site before you pay.

ChatGPT for Excel. Ask in chat and paste the formula back into your sheet, or upload the file itself. The Free plan includes unlimited text chats with limited messages with uploads, limited image creation, limited voice, limited deep research and limited memory (Pricing | ChatGPT). File uploads are limited to 512MB per file, about 50MB for CSV or spreadsheets and 20MB per image; Free users get 3 file uploads per day, and users are generally capped at 80 files every 3 hours (File Uploads FAQ). Plus costs $20 per month billed monthly, with no annual billing option (What is ChatGPT Plus?); Go offers higher limits than Free for image generation, file uploads and data analysis, and OpenAI says it may adjust pricing over time (What is ChatGPT Go?). OpenAI also warns that ChatGPT can produce incorrect or misleading outputs, including fabricated quotes or citations (Does ChatGPT tell the truth?).

Microsoft Copilot in Excel. This one sits inside the sheet: it can generate formula columns and rows, suggest a single-cell formula and explain how it works, build lookups (including recommending XLOOKUP) and explain formulas that already exist (Get data insights with Copilot in Excel). It requires a Microsoft 365 Personal or Family subscription with an AI credits plan, Microsoft 365 Premium, a commercial Microsoft Copilot subscription, or an eligible business or enterprise subscription (Copilot in Excel FAQ). US prices on Microsoft's page: Microsoft 365 Personal $9.99/month and Family $12.99/month include Copilot in Word, Excel, PowerPoint and Outlook with higher usage than free, and Microsoft 365 Premium is $19.99/month; in Family, the AI features are for the subscription owner only (Copilot Pricing Plans for Individuals). One structural requirement: Copilot in Excel needs AutoSave enabled, so the file must be saved to OneDrive and it won't work on unsaved files (Copilot in Microsoft 365 subscriptions FAQ).

Gemini in Google Sheets. Type what you want — for example "create a formula that divides goals by games" — and Gemini writes the formula; when a cell shows an error, hover and choose Fix. It requires an eligible Google Workspace or Google AI plan, it works best with native Google Sheets files, and an Excel file (.xlsx) should be saved as Google Sheets first (Collaborate with Gemini in Google Sheets). The Gemini app itself is free at $0/month with a Google Account, Google AI Plus is $4.99/month, and Google AI Pro is $19.99/month with 4x higher usage access than Free plus Gemini in Gmail, Docs and more and 5 TB of storage; prices differ by country, and the Australian page shows AUD (Google AI Pro & Ultra). Google advises double-checking responses and not relying on them for professional advice (Learn about responses from Gemini Apps).

A prompt to paste

Replace the parts in square brackets and paste it into ChatGPT, Gemini or Copilot:

You are writing one Excel formula.

Sheet and layout:
- Sheet name: [Sheet1]
- Lookup value is in cell: [A2]
- Lookup column: [B2:B500]
- Return column: [D2:D500]
- Formula goes in [F2] and is filled down to [F500]

My Excel version: [Microsoft 365 / Excel 2021 / Excel 2019 / Excel 2016]

What I want: [return the price from column D for the product code in A2]

Answer in this order:
1. The formula alone on one line, no explanation.
2. One sentence on what it does.
3. The error value I am most likely to see, and what it means.
4. If the formula uses a function my Excel version does not have, say so and give an alternative.

Two follow-ups worth having ready:

  • "Rewrite this without XLOOKUP — I'm on Excel 2019." XLOOKUP is not available in Excel 2016 and Excel 2019, so the substitute matters (XLOOKUP function).
  • "It returns the wrong total. Here are the first three rows of each column: [...]. What is it matching on?" This is the fastest way to catch a criteria range that doesn't line up.

How far AI gets you

AI does it. A formula request is self-contained: give the column letters, the target cell and the job, and the answer comes back ready to paste, because every fact needed is in your description rather than in outside knowledge. And you can confirm it instantly against a row you can already calculate in your head, which is what makes this safer to delegate than most spreadsheet work.

What you still do

  • Check it on one row you can work out by hand. If the formula agrees there, fill it down.
  • Match the function to your Excel version. XLOOKUP finds a value in one column and returns a result from the same row in another column, and it is not available in Excel 2016 or Excel 2019; if it finds no match and you haven't supplied an if_not_found value, it returns #N/A, and exact match is the default (XLOOKUP function).
  • Watch the argument order in SUMIFS. SUMIFS adds values meeting several criteria, and unlike SUMIF the sum_range is the first argument — a common source of problems. Criteria ranges must contain the same number of rows and columns as sum_range, and text criteria need quotes or you may get 0 (SUMIFS function).
  • Read error values, don't guess at them. IF returns one value when a condition is true and another when it's false, and a #NAME? error usually means a misspelled formula (IF function).
  • Step through anything nested. On the Formulas tab, use Evaluate Formula under Formula Auditing to see each part of a nested formula calculated in order. It checks one cell at a time, and volatile functions such as RAND, NOW and TODAY can give results that differ from the cell (Evaluate a nested formula one step at a time).
  • Treat AI output as a draft. Microsoft states that Copilot uses AI to generate suggestions and can make mistakes, misinterpret information or produce inaccurate results, and that the formulas it generates can be inaccurate or inappropriate — so review and verify before you rely on them (Copilot in Excel FAQ).
  • Decide what you are willing to upload. In ChatGPT you can stop new conversations being used to train OpenAI models by turning off "Improve the model for everyone" under Settings > Data controls; chats still stay in your history, and temporary chats are not used to improve models (Data controls in ChatGPT).
  • Use an official source or a qualified person for high-stakes numbers. Anything feeding a tax return, a regulatory filing, a contract, a payroll run or a set of published accounts should be checked against the official rules or by a licensed professional; an AI-generated formula is not a substitute for either.

Common questions

Do I need a paid plan to use ChatGPT for Excel formulas?

Usually not. The Free plan includes unlimited text chats, so typing your column layout and asking for a formula costs nothing; the paid tiers matter for uploads and volume — Free users are limited to 3 file uploads per day, while users are generally capped at 80 files every 3 hours (File Uploads FAQ).

Why does my generated formula return #N/A?

With XLOOKUP, #N/A is what you get when no valid match is found and no if_not_found value was supplied (XLOOKUP function). In practice it usually means one column has trailing spaces, or the two columns hold different data types.

Is Copilot in Excel included with Microsoft 365 Family?

Microsoft 365 Family at $12.99/month includes Copilot in Word, Excel, PowerPoint and Outlook with higher usage than the free version, but the AI features are for the subscription owner only, and Copilot in Excel also requires an AI credits plan, Microsoft 365 Premium, a commercial Microsoft Copilot subscription or an eligible business or enterprise subscription (Copilot Pricing Plans for Individuals, Copilot in Excel FAQ).

Sources