Use ChatGPT for Excel Formulas: Prompts, Copilot, Checks

To use ChatGPT for Excel formulas, tell it five things: your software and version (for example “Excel 2019” or “Microsoft 365”), the sheet layout (which column holds what and where data starts), the result you want with one expected answer, the awkward cases (blanks, half days, “AB”), and a request to explain the formula simply. Then test the formula on rows you can check by hand before copying it down. AI tools write and explain formulas well, but they can also give formulas that look right and are wrong, and they should never see your real salary or customer data.

How to use ChatGPT for Excel formulas: a prompt that works

Compare two prompts.

Weak: “Formula for attendance.”

Strong: “I use Excel 2019. Sheet ‘Attendance’ has employee IDs in A5:A60 and daily codes for October 2026 in C5:AG60. Codes: P present, A absent, HD half day, CL, SL, EL leave, H holiday, WO weekly off. In AH5 I want days present, counting HD as half a day. For a row with 20 P and 2 HD the answer should be 21. Give one formula I can copy down and explain each part.”

A correct answer is =COUNTIF(C5:AG5,"P")+0.5*COUNTIF(C5:AG5,"HD"). The expected answer you gave (21) lets you confirm it in seconds.

More prompts worth saving:

• Lookup: “Excel 2016. Product codes in Products!A2:A200, rates in D2:D200. Fetch the rate for the code in A2, show ‘Not found’ if missing, and do not use XLOOKUP.” Expected: an INDEX with MATCH or VLOOKUP with FALSE, wrapped in IFNA.
• Conditional total: “Microsoft 365. Column B city, C month, D amount, headers in row 1. Total for Lucknow in October.” Expected: =SUMIFS(D:D,B:B,"Lucknow",C:C,"October").
• Explain a formula: paste only the formula: “Explain step by step for a beginner: =IFNA(INDEX(Rates!$D$2:$D$200,MATCH(A2,Rates!$A$2:$A$200,0)),"Not found").”
• Fix an error: “This VLOOKUP returns #N/A for some rows though the codes look identical. The codes were pasted from a PDF.” A good answer suggests hidden spaces or numbers stored as text and proposes TRIM or VALUE.

Common AI formula mistakes to catch

• Wrong version: XLOOKUP, IFS, FILTER or TEXTSPLIT given to someone on an older Excel, which shows #NAME?.
• Argument order: SUMIF and SUMIFS mixed up (SUMIF puts the sum range last, SUMIFS first).
• Missing exact match: VLOOKUP without FALSE, or MATCH without 0, which can return plausible but wrong values.
• No $ signs where the formula will be copied, so ranges drift.
• Invented functions or options that do not exist.
• Made-up arithmetic: asking “what is the total of these rows?” invites wrong numbers. Let Excel calculate; let AI write the formula.

Example: Pooja, an accounts assistant in Surat, got =D2*C2/2 for CGST with no rounding and an unlocked rate cell. Her test rows showed the error in row 3. She asked again with “copy down, rate is per row in column C, round to 2 decimals” and got =ROUND(D2*C2/2,2), which passed her checks.

Copilot in Excel and Gemini in Google Sheets

Copilot in Excel is a pane inside Excel that can suggest formula columns, highlight data and summarise. Microsoft says it needs an eligible plan, such as certain Microsoft 365 personal plans with AI credits or a business Copilot licence, and it edits only when calculation is set to Automatic. A separate preview worksheet function, =COPILOT, was withdrawn in September 2026; old workbooks that used it show #NAME? when recalculated.

Gemini in Google Sheets offers a side panel and an AI function for eligible Google Workspace or Google AI plans. Google itself warns that output may be inaccurate.

Plans and features change often. Check what your organisation has approved before using any assistant on work files, and follow your employer’s AI policy even inside an approved tool.

The checking routine: every formula, every time

1. Read it. Identify each function and argument. Look up anything unfamiliar on Microsoft’s or Google’s help page.
2. Test known rows. Pick 3 to 5 rows you can work out by hand, including an awkward one: a blank, a zero, a half day, a missing code.
3. Check after copying. Open the formula in the last row: did the ranges shift correctly?
4. Cross-check the total another way: a pivot table, a SUMIFS or a filtered SUBTOTAL.
5. Check versions for everyone who will open the file.
6. Leave a note (Review, New Comment) saying what the formula does and when you checked it.

And the data rule: never paste salary sheets, Aadhaar or PAN numbers, bank details, student marks with names or customer lists into an AI tool your organisation has not approved for that data. Describe the columns, or paste five made-up rows with the same structure.

Frequently asked questions

Can I use ChatGPT for Excel formulas?

Yes. ChatGPT and similar tools are good at writing and explaining Excel and Google Sheets formulas if you state your version, describe the layout and give an expected answer. Always test the formula on rows you can verify before relying on it.

Is Copilot in Excel free?

No. Microsoft makes Copilot in Excel available with eligible paid plans, and the eligible plans change from time to time. Check Microsoft’s current plan page or your organisation’s IT team.

Why does the formula from ChatGPT show #NAME?

Usually the formula uses a function your Excel version does not have, such as XLOOKUP or IFS in Excel 2016, or the function name is misspelt. Tell the tool your exact version and ask for an alternative using INDEX and MATCH or nested IF.

Is it safe to upload my Excel file to ChatGPT?

Not if it contains personal, salary, customer or confidential data, unless your employer has approved that tool for such data. Uploaded files include hidden sheets and comments too. Describe the structure or use dummy rows instead.

Quick quiz

Sources

• Microsoft Support: Get started with Copilot in Excel (eligible plans and requirements)
• Microsoft 365 Message Center, MC1454373: retirement of the =COPILOT function in Excel
• Google Workspace Help: Gemini in Google Sheets
• Microsoft Support: COUNTIF, SUMIFS, VLOOKUP, INDEX and MATCH functions

Learn formulas well enough to check any AI answer: Excel and AI for Office Work certificate course.

This page is for education and general information, not legal advice for a specific case. Law as on 29 October 2026.