Let AI Write Your Excel and Google Sheets Formulas for €0
Updated: November 2026 · Prices checked: November 2026
In short
- Any free AI chat writes working XLOOKUP, SUMIFS, text-cleaning and date formulas if you give it your column letters, three sample rows and the program you use.
- Built-in AI costs money: Gemini in Google Sheets and the =AI() function need an eligible Workspace or Google AI plan (Google AI Pro is €21.99 a month). Microsoft 365 Personal and Family include Copilot with 60 AI credits a month.
- Excel’s =COPILOT() cell function was retired on 14 September 2026. Old cells now return #NAME? when they recalculate.
- Always test a new formula on 5 rows you can check by hand before filling it down.
Most people know what they want from a spreadsheet. They just do not remember whether it is SUMIF or SUMIFS, or why VLOOKUP returns #N/A. You do not need a paid AI add-on for this: a free chat writes the formula, and you paste it in. The trick is to describe your sheet the way the AI needs it.
What you save
The paid route is AI built into the spreadsheet. In Google Sheets, Gemini and the =AI() function come with eligible Workspace business plans or a Google AI plan. Google AI Pro is listed at €21.99 a month on Google’s Irish subscriptions page. In Excel, Copilot comes with Microsoft 365 Personal and Family, but only 60 AI credits a month, shared across Word, Excel, PowerPoint and the other apps.
The maths: if you only need formulas, Google AI Pro at €21.99 × 12 = about €264 a year buys you something a free chat already does. Writing formulas is a small, text-only job. It is exactly what free tiers handle well.
| Option | Price | Writes formulas | Sees your sheet |
|---|---|---|---|
| Free ChatGPT, Gemini or Claude | €0 | Yes | Only what you paste |
| Gemini in Google Sheets | Eligible Workspace or Google AI plan (AI Pro €21.99/month) | Yes, inserts and fixes | Yes |
| Copilot in Excel (M365 Personal/Family) | Included, 60 AI credits/month | Yes | Yes |
The prompt that gets working formulas
Bad formulas come from vague questions. “How do I sum by month?” gets a generic answer with made-up columns. Give the AI four things instead:
- The program and version. “Google Sheets” or “Excel for Microsoft 365”. XLOOKUP does not exist in Excel 2016 or 2019.
- The separator. If your computer uses a comma for decimals, formulas use semicolons between arguments. Say so.
- The layout. Sheet names, column letters, header row, where the data starts and ends.
- Three sample rows and the expected result. Fake the data if it is sensitive. Keep the format.
I use Google Sheets. My locale uses semicolons in formulas.
Sheet "Orders": A = Date, B = Customer, C = Product, D = Amount. Headers in row 1, data from row 2 to 5000.
Sample:
2026-10-03 | Petrov Ltd | Chair | 120
2026-10-15 | Ivanova | Desk | 340
2026-11-02 | Petrov Ltd | Chair | 120
Write one formula for cell G2 that returns the total Amount for the customer in F2 for the month in E2 (E2 holds any date in that month).
Explain each part in one line. Then give me 3 test cases I can check by hand.The last line matters. Asking for test cases makes you check the result instead of trusting it. Our guide to writing AI prompts that work explains why context and examples beat clever wording.
Ready examples: lookups, sums, text, dates
These are typical answers you should expect, shown with commas. Swap in semicolons if your locale needs them.
| Task | Formula | Note |
|---|---|---|
| Find a price by product code | =XLOOKUP(A2, Prices!A:A, Prices!C:C, "not found") | Works in Sheets and Excel 2021/365. Replaces VLOOKUP. |
| Same in old Excel | =IFERROR(VLOOKUP(A2, Prices!A:C, 3, FALSE), "not found") | FALSE means exact match. Forgetting it is the classic error. |
| Sum by customer and month | =SUMIFS(D:D, B:B, F2, A:A, ">="&EOMONTH(E2,-1)+1, A:A, "<="&EOMONTH(E2,0)) | Dates in column A must be real dates, not text. |
| Clean names | =PROPER(TRIM(B2)) | Removes extra spaces, fixes capitals. Check names like “McDonald”. |
| Keep only digits in a phone number | =REGEXREPLACE(B2, "[^0-9]", "") | Google Sheets. In Excel, ask the AI for a version that fits your build. |
| Text “03.10.2026” to a real date | =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)) | Safer than DATEVALUE, which depends on locale. |
| Working days between two dates | =NETWORKDAYS(A2, B2) | Add a holiday range as a third argument. |
When a formula fails, do not rewrite the question. Paste the formula and the exact error back into the same chat: “This returns #VALUE! in row 14, where A14 is 03.10.2026.” The fix usually takes one round.
Built-in AI in Sheets and Excel
If you already pay for a plan that includes it, use it. The built-in tools see your real columns, so you skip the description step.
- Google Sheets: click Ask Gemini at the top right, describe the formula, then click the target cell and Insert. If a formula shows an error, hover over the cell and click Fix. The =AI() function (also =Gemini()) runs a prompt per cell, but it returns text only, cannot sit inside other formulas like IF, and has daily usage limits.
- Excel: open the Copilot pane and ask in plain words. Each request uses AI credits from your monthly 60 on Personal and Family. The old =COPILOT() cell function stopped working on 14 September 2026; Microsoft now points users to the Copilot pane.
Claude also started building and editing spreadsheets directly, which helps when you need a whole model, not one formula. Before you pay for any of these, read our free vs paid AI subscription maths.
Limits and traps
- Confident and wrong. A formula that returns a number is not a correct formula. Test it on 5 rows you can add up in your head.
- Dates stored as text. The most common reason SUMIFS returns 0. Ask the AI how to check with ISNUMBER(A2).
- Whole-column ranges. A:A is easy but slow on big files. Ask for fixed ranges if the sheet lags.
- Private data. Do not paste real customer lists into a free chat to get a formula. Three fake rows do the same job.
- Version mismatch. Formulas from the AI may use functions your colleague’s older Excel does not have. Name the oldest version that must open the file.
When should you pay? When you work in spreadsheets every day and need AI to read whole tables, build pivot tables and charts, or fix many formulas at once. Then the built-in tool saves real time. Companies that roll AI out across the team, like Uber giving ChatGPT to every employee, buy it for that kind of daily use, not for the occasional formula.
FAQ
Can free ChatGPT write Excel formulas?
Yes. Free ChatGPT, Gemini and Claude all write Excel and Google Sheets formulas well. Tell them the program, your column letters, the separator your locale uses and three sample rows. Ask for test cases too, and check the formula on a few rows before you fill it down.
Is the Excel COPILOT function still available?
No. Microsoft retired the =COPILOT() function on 14 September 2026. Cached results stay, but cells return #NAME? when they recalculate. Microsoft recommends the Copilot pane instead, which covers summarizing, classifying and generating content with plain-language prompts.
Do I need to pay for Gemini in Google Sheets?
Yes. Google lists eligible plans such as Workspace Business Standard and Plus, Enterprise plans and Google AI Pro or Ultra. On a free personal account you can still open Gemini or another free chat in a separate tab, ask for the formula and paste it in.
Why does my AI formula return #N/A or 0?
Usually the data, not the formula. #N/A means the lookup value is not found, often because of extra spaces or numbers stored as text. A SUMIFS that returns 0 often means dates are text. Paste the error and one example row back to the AI and ask why.
Read also: 500 Product Titles and Meta Descriptions With AI for Under €1 · Clean a Messy Customer Spreadsheet With AI for €0, Safely · Learn Excel or SQL With a Free AI Tutor: 30-Day Plan for €0
Ivan’s Take
This is the easiest money you will save with AI. I would not pay for a spreadsheet AI add-on just to write formulas. Save the prompt template above, use fake sample rows and always ask for test cases. Pay for the built-in tools only when spreadsheets are your daily work and you want AI to read the whole file.
Sources: Google: Gemini in Google Sheets · Google: AI function in Sheets · Google AI plans (EUR) · Google: XLOOKUP · Microsoft: XLOOKUP · Microsoft: COPILOT function · Microsoft: AI credits
HawkValue
If it doesn’t save you time or money, you won’t see it here
- Facts, not hypeEvery price and number is checked against the official source
- Always the real priceYou see what it costs before you click
- I say when it’s weakEven when it’s OpenAI or Google
