Guides · Generative AI

How to Write Excel Formulas with ChatGPT and Copilot (7 Worked Examples)

By Ravindra Bagale, trainer · LinkedIn · Published: 19 October 2026 · Last updated: 19 October 2026 · 10 min read

To get a correct Excel formula from ChatGPT, tell it where your data is (sheet, range, headers), what result you want and which cell it goes in, and your Excel version. Paste the formula, then check it on 2 or 3 rows by hand. In Excel itself, use the Copilot pane (the Copilot icon at the lower right) with a Microsoft 365 plan that includes Copilot. The old =COPILOT() cell function was retired on 14 September 2026.

1. Suppose we manage sales for a tea and coffee distributor

Suppose we are Pooja. We work for a small tea and coffee distributor that supplies shops in Pune, Mumbai and Nashik. Our manager sends a WhatsApp: "How much Tea did we sell in Pune this month? And how many Coffee orders came from Mumbai?"

Here is our sheet, named Sales. Copy this table into a new Excel sheet starting at cell A1, so you can follow every example.

So the data is in A1:E11: headers in row 1, 10 orders in rows 2 to 11. The dates are real Excel dates (day-month-year, as in India).

2. The 4-part formula prompt

A weak prompt is: "excel formula for total". The AI does not know your columns, so it guesses =SUM(A:A), which adds up order IDs.

A strong prompt has 4 parts:

Copy this template and reuse it for every formula:

I use Excel for Microsoft 365. My data is in sheet "Sales", range A1:E11,
headers in row 1:
A = OrderID, B = Date (real dates), C = City, D = Product, E = Amount.
Goal: [what you want, in plain words]
Put the formula in cell [H2]. [Any input cell, e.g. "the order ID is typed in G2"]
Give one formula, then explain each part in simple English.

3. Example 1: total with two conditions (SUMIFS)

Goal: total Tea sales in Pune.

[template] Goal: total Amount where City is "Pune" and Product is "Tea". Put it in H2.

Expected formula (the AI's wording will differ, the formula should match):

=SUMIFS(E2:E11, C2:C11, "Pune", D2:D11, "Tea")

What it means: add the values in E2:E11, only for rows where C is "Pune" and D is "Tea".

Result: 2700.

Check by hand: Pune Tea rows are 1001 (1,200) and 1005 (1,500). 1,200 + 1,500 = 2,700. Correct.

4. Example 2: count with two conditions (COUNTIFS)

Goal: how many Coffee orders came from Mumbai.

=COUNTIFS(C2:C11, "Mumbai", D2:D11, "Coffee")

Result: 2 (orders 1002 and 1009).

5. Example 3: find a value by ID (XLOOKUP)

Goal: type an order ID in G2 and get its Amount in H2. Show "Not found" if the ID does not exist.

=XLOOKUP(G2, A2:A11, E2:E11, "Not found")

Test: type 1007 in G2. Result: 1100. Type 2000. Result: Not found.

If you use an older Excel without XLOOKUP (for example Excel 2016), say so in the prompt. The AI will give you =IFERROR(VLOOKUP(G2, A2:E11, 5, FALSE), "Not found") instead, which does the same job here. This is why the version line in the prompt matters.

6. Example 4: a label for each row (IF)

Goal: in F2, label each order "High" if Amount is 1000 or more, "Medium" if 500 or more, otherwise "Low". Then copy it down to F11.

=IF(E2>=1000, "High", IF(E2>=500, "Medium", "Low"))

In Microsoft 365 you can also ask for the shorter IFS version:

=IFS(E2>=1000, "High", E2>=500, "Medium", TRUE, "Low")

Check: row 2 (1,200) shows High, row 5 (600) shows Medium, row 9 (300) shows Low.

7. Example 5: total between two dates

Goal: total Amount from 1 October to 5 October 2026.

=SUMIFS(E2:E11, B2:B11, ">="&DATE(2026,10,1), B2:B11, "<="&DATE(2026,10,5))

Result: 5750 (orders 1001 to 1006: 1,200 + 800 + 950 + 600 + 1,500 + 700).

Why DATE()? Writing the date as text, like ">=01-10-2026", can be read as 10 January on a computer with US date settings. DATE(year, month, day) is always clear. A good AI answer uses it; if yours does not, ask: "Use DATE() so it works with any date settings."

8. Example 6: fix a #N/A error with AI

Suppose a colleague types Pune (with a space at the end) in cell J2, and this formula shows #N/A:

=XLOOKUP(J2, C2:C11, E2:E11)

Ask the AI to find the problem. Give it the formula, the error, and what you see in the cell:

This Excel formula returns #N/A: =XLOOKUP(J2, C2:C11, E2:E11)
J2 looks like "Pune" and C2:C11 contains Pune. What are the common reasons,
and how do I fix it? Give the fixed formula.

Expected answer (similar, not the same): the most common reason is extra spaces or a different spelling. Fix it with TRIM:

=XLOOKUP(TRIM(J2), C2:C11, E2:E11, "Not found")

Result: 1200. XLOOKUP returns the first match, which is order 1001. If you wanted the total for Pune, the right formula is SUMIFS, not XLOOKUP, so say "total" in your prompt when you mean total.

9. Example 7: explain a formula someone else wrote

You open an old file and see this:

=IFERROR(SUMIFS(E2:E11, C2:C11, G5, D2:D11, H5) / COUNTIFS(C2:C11, G5, D2:D11, H5), 0)

Prompt:

Explain this Excel formula step by step for a beginner, in simple English,
and tell me what it returns if G5 = "Pune" and H5 = "Coffee":
[paste the formula]

Expected answer (similar): it finds the average Amount for the city in G5 and product in H5 (total divided by count), and shows 0 if there are no matching rows. For Pune Coffee: (950 + 400) / 2 = 675.

You can also ask: "Is there a simpler formula?" The AI should suggest =IFERROR(AVERAGEIFS(E2:E11, C2:C11, G5, D2:D11, H5), 0), which gives the same 675.

10. Doing the same with Copilot inside Excel

Copilot works inside your workbook, so it can see your columns. You do not need to describe the range as carefully.

1. Click inside your data and press Ctrl + T to make it an Excel table. Copilot works best with clean tables: one header row, no merged cells.
2. Click the Copilot icon at the lower right of Excel.
3. Choose a mode. Edit changes the workbook, Plan shows a plan for you to approve first, and Chat answers without changing anything (these three modes are on Windows, Mac and the web).
4. Type: "Add a formula in a new column that labels each order High if Amount is 1000 or more, Medium if 500 or more, otherwise Low."
5. Click into the new column and read the formula Copilot wrote. Check 2 or 3 rows.

Who gets Copilot in Excel (October 2026)? Microsoft's support page lists: Microsoft 365 Personal or Family with AI credits, Microsoft 365 Premium, or an eligible business or Copilot plan. Microsoft's credits page says Personal and Family include 60 AI credits a month for Copilot in the Office apps, and Premium has much higher use. Check your plan in your Microsoft account before you buy.

The =COPILOT() function is gone. Microsoft retired it on 14 September 2026. Old cells that used it show #NAME? after they recalculate. Use the Copilot pane instead.

For 40 more Copilot prompts with a checking routine, see our Copilot in Excel prompts for analysts guide.

11. ChatGPT, Gemini, Claude or Copilot?

Privacy: do not paste salary sheets, customer phone numbers or client data into a free AI chat. Describe the columns, or use 5 fake rows like the table above.

12. Beginner mistakes and quick fixes

13. Try at home

Recap

Take one formula you use at work every week, ask the AI to explain it with the prompt in Example 7, and you will understand your own sheet better by tomorrow.

Learn prompts properly: Prompt Engineering for Beginners: Role, Task, Context and Format

Ravindra Bagale, trainer: linkedin.com/in/ravindra-bagale. Pooja, the distributor and all orders and amounts are made up for learning. Every formula result on this page was checked against the sample table. Copilot plans, credits, modes and the =COPILOT() retirement were checked on Microsoft's support pages on 19 October 2026 and can change. AI answers change every time, so the wording you get will be different.

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