ChatGPT and Copilot for Excel Formulas: Write, Explain and Fix Formulas

By Ravindra Bagale, trainer · LinkedIn · Last updated: 23 October 2026

Generative AI course · Chapter 18 · Estimated reading time: 14 minutes

Practice file: download ai-excel-formulas-practice.xlsx. It has 2 sheets: Orders (7 invoices) and Products (4 product codes with sample GST rates). The names and numbers are made up, and the GST rates are sample values for practice only.

1. Suppose we have 7 invoices and 3 questions

Suppose we are Prakash, an accounts assistant at a stationery distributor in Pune. Our manager sends the order sheet and asks 3 questions:

1. Which product is on each invoice? (The sheet has only codes like P01.)
2. Is any invoice entered twice?
3. What is the GST on each invoice, CGST and SGST for Maharashtra customers, IGST for others?

The Orders sheet looks like this:

The Products sheet: A = Code, B = Product, C = GST % (rows 2 to 5: P01 Notebook pack 5%, P02 Ball pen box 18%, P03 Office chair 18%, P04 Printer 18%).

2. How to ask for a formula (the 5 facts)

The AI cannot see your sheet unless you describe it (or use Copilot inside Excel). Give these 5 facts every time:

1. Sheet names and columns: "Orders sheet: C = product code. Products sheet: A = code, B = name."
2. Rows: "data from row 2 to row 8".
3. What you want, in plain words.
4. What should happen if something is missing: "show Not found".
5. Your Excel version: "Excel 2021" or "Microsoft 365". Some functions, like XLOOKUP, are not in older versions such as Excel 2016.

The formula prompt template:

I use [Excel version]. Sheet "[name]": [column letters and what they hold], data in rows [2 to N].
[Other sheet details if needed.]
I want in column [X]: [what you want, in plain words].
If [something is missing], show [what].
Give the formula for row 2 (I will copy it down), then explain it in 2 lines.

3. Worked example 1: XLOOKUP and the VLOOKUP trap

Prompt:

I use Microsoft 365. Sheet "Orders": C = product code, rows 2 to 8.
Sheet "Products": A = code, B = product name, C = GST %, rows 2 to 5.
In Orders column H, I want the product name for each code. If the code is not in Products, show "Not found".
Give the formula for row 2, then explain it in 2 lines. Also give a VLOOKUP version for Excel 2016.

Expected output (yours will be similar, not the same):

XLOOKUP (Microsoft 365, Excel 2021 and later), in H2:
=XLOOKUP(C2,Products!$A$2:$A$5,Products!$B$2:$B$5,"Not found")

It looks for the code from C2 in the Products codes and returns the name from the same row.
If it does not find the code, it shows "Not found".

VLOOKUP version (older Excel):
=IFERROR(VLOOKUP(C2,Products!$A$2:$B$5,2,FALSE),"Not found")

Test it: type the formula in H2 and fill down to H8. These are the actual results we got when the practice file was recalculated (23 October 2026):

Row 7 has code P09, which is not in the Products sheet, so "Not found" is correct. This tells Prakash to check that invoice.

The VLOOKUP trap. Many people (and some AI answers) leave out the last part, FALSE:

=VLOOKUP("P09",Products!$A$2:$B$5,2)

We tested exactly this. It returned Printer, not an error. Without FALSE, VLOOKUP does an approximate match: it found the closest code below P09 (P04) and returned its name. A wrong answer that looks like a right answer is the most dangerous kind. Always use FALSE (exact match) with VLOOKUP, or use XLOOKUP, which does an exact match by default.

4. Worked example 2: find duplicate invoices

Prompt:

Sheet "Orders": A = invoice number, rows 2 to 8.
In column N, show "Duplicate" if the invoice number appears more than once in A2:A8, otherwise leave it blank.
Give the formula for row 2.

Expected output (yours will be similar, not the same):

=IF(COUNTIF($A$2:$A$8,A2)>1,"Duplicate","")

Actual result: rows 3 and 6 (both INV-102) show Duplicate; all other rows are blank.

Why it matters for the total: we added a total of all invoice totals (with GST, from the next example). With the duplicate, the grand total was ₹26,253. INV-102 is counted twice (₹5,664 each), so the correct total is ₹26,253 − ₹5,664 = ₹20,589.

What to do next:

1. Do not delete rows from a formula alone. Open both INV-102 rows and confirm they are the same order (same customer, quantity, rate).
2. If they are the same, remove one: select the data, then Data → Remove Duplicates, and choose the Invoice column. (Work on a copy of the file first.)

$A$2:$A$8 has dollar signs so the range does not move when you fill the formula down. If you forget them, the range shifts and the results go wrong in the lower rows. Ask the AI "why are there dollar signs?" if this is new to you.

5. Worked example 3: GST with IF

GST rule for this practice: if the customer is in Maharashtra (same state as the seller), split GST into CGST and SGST (half each). If the customer is in another state, charge IGST (the full rate).

Prompt:

Sheet "Orders": F = amount before GST, G = state, I = GST rate as a decimal (0.18 = 18%), rows 2 to 8.
Seller is in Maharashtra.
Column J = CGST: half of the GST if G is "Maharashtra", else 0.
Column K = SGST: same as CGST.
Column L = IGST: full GST if G is not "Maharashtra", else 0.
Column M = total with GST.
Give the 4 formulas for row 2.

(First fill column I with the GST rate using XLOOKUP, like example 1, but returning column C of Products. For the unknown code P09, use 0 as the "not found" value and check that invoice separately.)

Expected output (yours will be similar, not the same):

J2: =IF(G2="Maharashtra",F2*I2/2,0)
K2: =IF(G2="Maharashtra",F2*I2/2,0)
L2: =IF(G2<>"Maharashtra",F2*I2,0)
M2: =F2+J2+K2+L2

Actual results:

Check 3 rows by hand:

1. INV-101: 2,500 × 5% = 125, split 62.50 + 62.50, total 2,625. ✓
2. INV-104: 7,000 × 18% = 1,260 IGST, total 8,260. ✓
3. INV-106: 2,000 × 18% = 360, split 180 + 180, total 2,360. ✓

Row INV-105 shows no GST only because its product code was not found. That is a data problem to fix, not a correct GST of zero.

Note: G2="Maharashtra" must match the spelling exactly. If someone typed "Maharastra" or "MH", the formula treats it as another state and charges IGST. Ask the AI: How do I find cells in column G that are not exactly Maharashtra, Gujarat or Karnataka?

6. Ask AI to explain any formula

Inherited a sheet with a long formula nobody understands? Paste it:

Explain this Excel formula in simple words, step by step, as if I am new to Excel.
Then tell me one situation where it could give a wrong answer.
=IFERROR(VLOOKUP(C2,Products!$A$2:$C$5,3,FALSE),0)

Expected output (yours will be similar, not the same):

1. VLOOKUP looks for the code in C2 in the first column of Products!A2:C5.
2. FALSE means it must find an exact match.
3. 3 means: return the value from the 3rd column of that range (the GST %).
4. IFERROR means: if VLOOKUP gives an error (code not found), show 0 instead.

Risk: if a code is missing, you get 0, which looks like "0% GST" instead of an error.
You may not notice that a product code is wrong.

That risk is exactly what happened with INV-105. A good explanation always includes when the formula can mislead you.

7. Fix errors: #N/A, #VALUE! and wrong totals

Paste the formula, the error and a sample of the data:

This formula gives #N/A in some rows: =VLOOKUP(C2,Products!$A$2:$B$5,2,FALSE)
Sample codes in C that fail: "P02 " and "p03". The Products sheet has P02 and P03.
What is wrong and how do I fix it?

Expected output (yours will be similar, not the same):

"P02 " has an extra space at the end. VLOOKUP needs an exact match, so it fails.
Fix: =VLOOKUP(TRIM(C2),Products!$A$2:$B$5,2,FALSE)
"p03" in small letters should still match, because VLOOKUP is not case-sensitive.
If p03 still fails, check for a hidden space or a different character.

Common causes the AI will usually find:

8. Copilot in Excel

Copilot in Excel works inside the workbook, so it can see your columns and you do not need to describe them.

Who has it (Microsoft help pages, checked October 2026):

• Microsoft 365 Personal or Family with AI credits (the subscription owner only; family members do not get the AI features), or Microsoft 365 Premium.
• Business and enterprise plans with Copilot or Copilot Chat access.
• The file must be saved to OneDrive with AutoSave on.

How to open it: look for the Copilot button in the lower-right corner of the workbook. Microsoft moved it there from the ribbon. Then ask in plain words, for example: Add a column with the product name from the Products sheet or Highlight duplicate invoice numbers.

Copilot in Excel not showing? Check in this order:

1. Look in the lower-right corner first. The button may have moved, not disappeared.
2. Save the file to OneDrive and turn AutoSave on.
3. Make sure Excel is signed in with the account that owns the subscription.
4. Update Office, then go to File → Account → Update License, and restart Excel.
5. Check File → Account → Account Privacy → Manage Settings: "Experiences that analyze your content" and "All connected experiences" must be on.
6. Work accounts: ask your IT team; your organisation controls access.

Copilot also makes mistakes. Use the same 3-row check on everything it adds.

9. The 3-row check for every AI formula

Before you fill a formula down to 10,000 rows:

1. A normal row: calculate it by hand or with a calculator.
2. An edge row: a missing code, a blank cell, a different state.
3. A row you know the answer to: an invoice you remember.

If all 3 are right, fill down. Then check the total in a second way (for example, SUM of the column vs a pivot table).

10. Compare the tools

11. Worked example 4: same GST IF formula in 4 tools

Take the GST IF formulas from example 3. Ask the same prompt in ChatGPT, Microsoft Copilot (in Excel or Bing), Gemini, and Claude. Compare. Health-check phrase: EXPANDOCT23-GST-IF-FOUR-TOOLS.

Shared prompt (copy-paste into each tool):

Sheet Orders: F2 amount before GST, G2 state, I2 GST rate as decimal (0.18).
Seller in Maharashtra.
Give Excel 365 formulas:
J2 CGST half if Maharashtra else 0;
K2 SGST same as CGST;
L2 IGST full if not Maharashtra else 0;
M2 total.
Explain each in one line. Do not use VBA.

What to check in each answer:

1. Does CGST use F2*I2/2 only when state is Maharashtra?
2. Does IGST use the full F2*I2 for other states?
3. Did the tool invent a different tax rule? Reject that.
4. Paste into Excel and run the 3-row check from this chapter.

Expected output (yours will be similar, not the same): four answers that should match the formulas in example 3. If one tool disagrees, trust Excel after your 3-row check, not the longest chat.

12. Beginner mistakes and quick fixes

13. Try at home

Recap

Download the practice file and do the VLOOKUP trap test now. After you see "Printer" appear for a code that does not exist, you will never forget FALSE again.

Previous chapter: AI for Emails and WhatsApp Business Messages (Polite, Fast, Correct)

Next chapter: AI PPT Maker: Make a Presentation with ChatGPT, Gemini, Copilot or Gamma

Ravindra Bagale, trainer: linkedin.com/in/ravindra-bagale. The company, customers, invoices and product codes on this page are made up for learning, and the GST rates in the practice file are sample values; always check the current GST rate for a real product. The results tables are the actual values from recalculating the practice file in LibreOffice Calc 25.2 on 23 October 2026; Excel gives the same results for these formulas. Copilot requirements were checked on Microsoft's support pages on 23 October 2026. AI answers change every time, so your output will be similar, not the same.