Source: https://oguzerol.com/en/chatgpt-for-excel/

[![Example scenario: the late-order formula an AI wrote returns 2 on an 8-row test table because it skips blank ship dates; the fixed formula finds 4 and is not used on the live table until the totals check passes.](https://oguzerol.com/gorseller/senaryo/chatgpt-for-excel-senaryo-en.webp)

View full size](https://oguzerol.com/gorseller/senaryo/chatgpt-for-excel-senaryo-en.webp)

# ChatGPT for Excel: Writing Formulas, Catching Errors, Analysis

[Oğuz Erol](https://oguzerol.com/en/about-me/)  |  6 min read  |  Published: 3 August 2026  |  Updated: 16 October 2026

## Quick summary

How to prompt ChatGPT for Excel formulas and catch separator, shifting-range and blank-cell errors before they reach a report. Four worked examples.

Asking ChatGPT, or any other AI chat service, to write Excel formulas works, on two conditions. The first is the prompt: state your Excel language and list separator, the sheet and column names, the rows the data sits in, two or three fake sample rows, the result you expect and the edge cases (blank cells, numbers stored as text). The second is a test: before a formula goes into a live sheet, try it on a small table whose answers you already know. In the fictional example below, none of a machine shop’s four formula requests gives the right result as first written, and three of them fail without any warning. Those three are the dangerous ones.

This page is for finance, planning and operations staff who build reports in Excel.

## What should a good Excel prompt include?

A good prompt describes the file the model can’t see. Otherwise the model fills the gaps with its own assumptions: usually English Excel, commas and a fixed row range. The template I recommend:

```
I use Excel for Microsoft 365, English function names, comma separator.

Sheet "Orders". Headers in row 1, data in rows 2–41, and new rows are added
at the bottom every week.
A Order no (text), B Customer (text), C Qty (number), D Amount (number),
E Due date (date), F Ship date (date; blank means not shipped yet).

Sample rows (fake data):
S-01 | Customer-A | 120 | 18600 | 2026-11-03 | 2026-11-02
S-02 | Customer-B | 40  | 9200  | 2026-11-05 | (blank)

What I need: for each customer in H2:H6, the total of column D, in column I.
The formula goes in I2 and will be copied down.
Edge cases: F can be blank; some Qty cells may have arrived as text.

Output: one formula and one sentence on what each argument does.
```

Don’t ask the model for test data: it would share the formula’s assumptions. Build the test table yourself, as below.

The separator line matters. According to Microsoft, Excel takes the argument separator from the list separator in Windows regional settings and rejects a formula typed with a different character; the US English default is a comma ([Microsoft Learn](https://learn.microsoft.com/troubleshoot/microsoft-365-apps/excel/formula-errors), read 16 October 2026). To check yours, type `=SUM(1,2)` in an empty cell; if Excel rejects it, try `=SUM(1;2)`.

Function names change with the Excel language, and Microsoft’s Functions Translator add-in is no longer available ([Microsoft Support](https://support.microsoft.com/en-gb/office/excel-functions-translator-f262d0c0-991c-485b-89b6-32cc8d326889), read 16 October 2026). Ask for your language’s names and check them in Microsoft’s help.

## Example: four formulas in a machine shop

*The shop, customers and numbers are fictional. “Example prompt” and “example answer” texts were written to show typical errors; I did not run an AI model for this page. I tested the logic and every number in a Python script, not in Excel.*

Picture a CNC machine shop. Every Monday the planning lead exports orders from the ERP and reports order value per customer, late orders, and working days left on open orders. “Orders” has 40 rows (2–41) with the columns above; the report date, a Monday, is in K1. Its customers are Customer A–E; Arda and Bilge belong only to the test table below.

### Start with a test table

The quickest test is an 8-row table whose answers you can work out on paper, one row per case. Report date: 2026-11-09, a Monday; all dates are in 2026.

| No | Customer | Qty | Amount | Due → shipped |
| --- | --- | --- | --- | --- |
| T-01 | Arda | 100 | 12,000 | 11-03 → 11-02 |
| T-02 | Bilge | “60” (text) | 7,200 | 11-04 → 11-06 |
| T-03 | Arda | 40 | 4,800 | 11-05 → blank |
| T-04 | Bilge | 80 | 9,600 | 11-13 → blank |
| T-05 | Arda | 20 | 2,400 | 11-09 → 11-09 |
| T-06 | Bilge | 50 | 6,000 | 11-06 → blank |
| T-07 | Arda | “30” (text) | 3,600 | 11-02 → 11-03 |
| T-08 | Bilge | 70 | 8,400 | 11-11 → blank |

Known answers: Arda 22,800, Bilge 31,200, together equal to the column D total (54,000). Late orders: 4 (T-02 and T-07 shipped late; T-03 and T-06 are unshipped and past due; T-05 shipped on its due date, so it isn’t late). Total quantity is 450. T-04 has 5 working days to go and T-08 has 3 (both ends counted). A formula that misses these numbers stays out of the live sheet.

**To build the same test on your own file:**

1. Copy 8–10 rows covering every case (blanks, text numbers or dates, boundary dates) to a new sheet.
2. Write the expected answers by hand first.
3. Adapt the formula to that sheet and compare.
4. In the live sheet, cross-check: the summary total must equal the raw data total.

### 1. Customer totals: a range that shifts when copied

Example prompt (with no Excel language and no row range stated): “Sum the amounts in column D for each customer in column B, and put the result next to the customers in H2:H6.”

Example answer: `=SUMIFS(D2:D41, B2:B41, H2)`

It’s right in I2, then gets dragged down to I6. A relative reference changes when copied ([Microsoft Support](https://support.microsoft.com/en-us/office/switch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e-5f0d8d0baca9), read 16 October 2026): the formula in I3 looks at D3:D42, the one in I6 at D6:D45. Each row down loses one more row from the top.

In the script’s 40-row week, four of the five customer totals come out short. Customer D shows 22,600 instead of 46,600, and the five customers add up to 533,050 instead of 585,700. The gap is 52,650, and Excel shows no warning.

The test table doesn’t catch this (the shift only drops Arda’s T-01, never part of Bilge’s total). The cross-total does: if `=SUM(I2:I6)` and `=SUM(D2:D41)` don’t match, the summary is losing rows. The fix is the dollar sign; select the reference and press F4:

`=SUMIFS($D$2:$D$41,$B$2:$B$41,H2)`

### A fixed range: new rows never enter the formula

An absolute reference locks the range at row 41. In week two, 6 new orders worth 88,200 land in rows 42–47. `$D$2:$D$41` doesn’t see them: the report still says 585,700 while the real total is 673,900.

The lasting fix is a Table: press Ctrl+T and name it “Orders” on the Table Design tab. Column references don’t shift and include new rows:

`=SUMIFS(Orders[Amount],Orders[Customer],H2)`

### 2. Late orders: a blank cell counts as zero

Example prompt: “Count the orders where the ship date is after the due date.”

Example answer: `=SUMPRODUCT(--(F2:F41>E2:E41))`

No error, and it returns 2 on the test table instead of 4. An empty ship date counts as zero, and zero is not greater than any date, so past-due orders that haven’t shipped, the most urgent ones, never enter the count. In the 40-row week the formula says 4; in fact 7 orders are late, and 3 of them haven’t shipped at all.

The corrected version adds orders shipped late to orders not shipped whose due date is before the report date:

`=SUMPRODUCT((F2:F41>E2:E41)+(F2:F41="")*(E2:E41<$K$1))`

I recommend a report date in K1 over TODAY(), which changes on every opening and can’t reproduce last week’s report.

The real lesson is the definition. The prompt didn’t define “late order”, so the model picked its own.

### 3. Total quantity: the formula is right, the data is text

Example prompt: “Give me the total of the quantities in column C.”

Example answer: `=SUM(C2:C41)`

The formula is fine, but in the 40-row week it returns 2,880 instead of 3,090: in the ERP’s CSV export, 6 quantities arrived as text. Microsoft says SUM ignores text ([SUM](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)) and SUMPRODUCT treats non-numeric entries as zero ([SUMPRODUCT](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)), both read 16 October 2026. The model can’t know this, because it never sees your file.

To count the text cells:

`=COUNTA(C2:C41)-COUNT(C2:C41)`

COUNT doesn’t count text in a range ([Microsoft Support](https://support.microsoft.com/en-us/office/count-function-a59cd7fc-b623-4d93-87a4-d23bf411294c)) and COUNTA does, so anything other than 0 means the column holds text. Here the result is 6. Microsoft gives two fixes: choose Convert to Number from the error indicator, or write `=VALUE(C2)` in a helper column and paste the results back as values ([Microsoft Support](https://support.microsoft.com/en-US/excel/convert-numbers-stored-as-text-to-numbers-in-excel), read 16 October 2026).

Amounts and dates suffer the same way. A CSV amount like “1,200.50” or a date like “2026-11-03” can stay as text when the file is opened under different regional settings; totals then come out short and date comparisons go wrong. Run the same check on columns D and E: a date is a number in Excel, so the difference should be 0. For dropped leading zeros in codes, see how a key column fixes them in [comparing two Excel lists](https://oguzerol.com/en/comparing-and-matching-two-excel-lists/).

### 4. Working days left: a function that doesn’t exist

Example prompt: “Work out how many working days are left until each open order is due.”

Example answer: `=WORKDAYDIFF($K$1,E2)`

Excel has no WORKDAYDIFF function; the formula returns #NAME?. Microsoft lists a typo in the function name first among the causes of #NAME? ([Microsoft Support](https://support.microsoft.com/en-us/office/how-to-correct-a-name-error-b6d54e31-a743-4d7d-9b61-40002a7b4286), read 16 October 2026); a name that never existed gives the same error. It shows on screen, so it’s easy to catch. State your Excel version too, or you may get a function your version lacks.

The correct version:

`=IF(F2="",NETWORKDAYS($K$1,E2),"")`

NETWORKDAYS counts both the start and the end day. Microsoft’s page doesn’t say so in a sentence, but when I counted its example, both ends are included ([Microsoft Support](https://support.microsoft.com/en-us/office/networkdays-function-48e717bf-a7a3-495f-969e-5005e3eb18e7)). On the test table, Monday to Friday of the same week (T-04) returns 5, and to Wednesday (T-08) 3. For a past-due order the result is negative: −3 for T-03, due the Thursday before. A holiday list as the third argument removes those days.

In the 40-row week, 3 of the 12 open orders come out negative, matching the gap in formula 2 (7 − 4 = 3). Two independent formulas showing the same fact is the strongest check after the test table.

### How do these errors show up, and how do you catch them?

| error | on screen | how to catch it |
| --- | --- | --- |
| Wrong separator | rejected or #NAME? | state the separator in the prompt |
| Mixed-language function names | rejected or #NAME? | state your Excel language |
| Relative reference shift | no warning, short total | summary total = raw data total |
| Fixed range | no warning, new rows missing | Table reference, row-count check |
| Function that doesn’t exist | #NAME? | check the name in the formula bar |
| Blank cell counted as zero | no warning, short count | a blank row in the test table |
| Number stored as text | no warning, short total | COUNTA − COUNT |

The four error types with no warning are the real risk: a formula that shows an error gets fixed, while one that quietly returns a wrong number ends up in a report.

## Uploading a file for analysis: what to send, and how to check the result

Most AI chat services let you attach a file by dragging it in or using the attach button; accepted types and sizes vary, and I didn’t test this flow for this page. Whether your data is used to train the model also depends on the service and your account settings; check that setting before uploading.

A formula prompt needs two or three fake rows; for analysis, send a masked copy, not the raw file.

| data | send it? |
| --- | --- |
| Column names, data types, row range | yes |
| Sample rows that carry the edge cases | yes |
| Customer and supplier names | masked (Customer-A, Customer-B) |
| Personal names, ID numbers, phone numbers, salaries | no |
| Unit cost, customer-specific discounts, quoted prices | no, or replaced using a fixed multiplier |
| The whole raw file | no |

To mask, put a code next to each real name on a separate “Mapping” sheet, and in the copy you send, pull the code in with XLOOKUP. Microsoft says XLOOKUP is not available in Excel 2016 and 2019; it is in Microsoft 365 and Excel 2021 or later ([Microsoft Support](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929), read 16 October 2026). The mapping sheet stays on your computer.

**A short analysis example.** Say you upload the shop’s 40 rows with amounts multiplied by a fixed factor, and ask: “What is the total amount per customer, and which customer has the most late orders?” The script’s answer, without the factor: on amount, Customer A leads clearly (205,500), followed by Customer C (138,650). On late orders there’s no single leader: C, D and E are tied at 2 each, A has 1, B none. The ranking and late-order counts don’t change with the factor; amounts grow by it (585,700 × factor). A one-name answer hides that tie. Define “late” in the question too.

**How to check the result.** First the cross-total: the customer totals must add up to the amount column total (585,700 times the factor in the masked file), and late orders per customer must add up to 7. Then reproduce it in Excel with a PivotTable or the formulas above. Most tools that run code let you open that code, though this varies by service and I didn’t test it; without it you can’t see the calculation. Don’t report an AI total you haven’t reproduced.

If the data includes personal data, data-protection law applies; check the account and data terms your company has approved for that service first. I don’t give legal advice.

**In Turkey:** Excel uses Turkish function names and usually a semicolon, and sending personal data to an AI service abroad falls under Article 9 of the KVKK ([Law No. 7499, Art. 34](https://www.resmigazete.gov.tr/eskiler/2024/03/20240312-1.htm), in force since 1 June 2024).

## What does Excel’s own AI (Copilot) change?

Copilot works inside Excel on the open file; the test table and cross-total still apply. I didn’t test its output in a non-English Excel. According to the Microsoft page I read on 16 October 2026 ([Copilot in Excel FAQ](https://support.microsoft.com/en-us/Excel/copilot/frequently-asked-questions-about-copilot-in-excel)), whether you can use it depends on your Microsoft 365 subscription and, for organisations, tenant configuration; ask your IT team. The same page says:

- Copilot can only edit the workbook when Calculation Options are set to Automatic.
- Saved changes are visible to anyone with access to the file, including co-authors.
- Copilot can make mistakes and its output should be verified; trained mostly in English, it may perform less well in other languages.

One dated change: the COPILOT function, used by typing `=COPILOT(...)` into a cell, is no longer available in Excel from 14 September 2026, according to Microsoft. Results calculated earlier stay in the workbook as cached values, but the cell returns #NAME? when it recalculates ([Microsoft Support](https://support.microsoft.com/en-us/office/copilot-function-5849821b-755d-4030-a38b-9e20be0cbf62), read 16 October 2026). If a file relies on it, convert those results to values or replace them with ordinary formulas.

## What changes when you ask for a VBA macro?

A macro prompt needs the same information, plus two rules: run it only on a copy, because Undo doesn’t reverse a macro, and ask the model to explain each block. A prompt that doesn’t name the sheets, conditions and folder path gets a macro built on assumptions. I cover when a macro is enough in [Excel macro and VBA development](https://oguzerol.com/en/excel-macro-vba-development/).

## If the report repeats monthly with several people, where should the formula live?

Don’t have AI rewrite the same report formula every month; write the rule once and keep it somewhere permanent. Each rewrite risks a new definition: “late orders” gave 4 under one and 7 under another.

| situation | where the rule lives |
| --- | --- |
| One-off question, one person | a formula from a chat service plus a test table |
| Same weekly report, same file layout | a template file with Table references; the formula is written once |
| New export every month, same cleaning steps | Power Query: steps are stored in the query and rerun with Refresh |
| Several people enter, correct and approve data | an app with defined roles and permissions, plus an audit trail |

In the first three rows Excel is enough; I wouldn’t recommend an app. Power Query keeps cleaning steps in the query, and a Refresh generally reapplies them to next month’s file.

In the fourth row the problem is who changed what: pushing a due date later quietly cuts the late count, and Excel doesn’t ask who did it or why.

In the structure I recommend for this case, the late-order definition is written once in code and applied as data is entered, so everyone sees the same number. Suggested roles:

- **Sales:** enters orders and sees their own customers’ orders.
- **Shipping:** enters only the ship date.
- **Planning:** revises due dates; a revision isn’t saved without a reason.
- **Finance and management:** see amounts and reports, change nothing.

The **audit trail** is an undeletable row for every change: order, field, old and new value, who, when, why. Here AI’s job isn’t formulas but text: summarising the delay reasons planning writes, or flagging a delay cause that keeps recurring. A person decides. The model can be set up to see only records the user is allowed to see.

The most common mistake I see in automation projects is picking the most complex process for the first automation, so the first version starts with just the order list, the late-orders report and due-date revisions. The server (a VPS) can be in your company’s name, the data is yours to export to Excel or CSV, a small or mid-sized team pays no per-user licence, and the source code sits in your repository; more on [custom web apps for your team](https://oguzerol.com/en/custom-web-apps-for-your-team/).

If AI rewrites one of your reports every month, send last month’s file, names masked, through the [contact page](https://oguzerol.com/en/contact/), and we’ll see whether the rule belongs in Excel, Power Query or an app. The first meeting is paid.

## Frequently Asked Questions

### If the formula is wrong, how do I go back to the model?

Instead of saying “it didn’t work”, give the gap: paste the formula, the result you expected on the test table and the result you got, then ask why a specific row wasn’t counted. For example: “Expected 4, got 2; why weren’t T-03 and T-06 counted?” That points the model to the blank-cell case. Retest the fix on the same test table, so both results stay comparable.

### Do the same errors happen in Google Sheets?

Shifting ranges, blank cells and text numbers behave the same. Language differs: according to Google’s help page, function names can show in English by default; unchecking “Always use English function names” under File > Settings shows them in your display language. Each spreadsheet’s locale is set in its own File > Settings and changes currency, date and number formatting ([Google Docs Editors Help](https://support.google.com/docs/answer/58515), read 16 October 2026). The page doesn’t mention separators. Test with an English name so any error comes from the separator, not the name: try `=SUM(1,2)`, then `=SUM(1;2)`, and put the one returning 3 in your prompt.

### How should I document an AI-written formula in the file?

I recommend a sheet called “Formulas” with, for each important formula, the prompt, the date, the test-table result and one sentence on what it does. If an untested case turns up, add that row to the test table first, then change the formula.

[Back to top ↑](https://oguzerol.com/en/chatgpt-for-excel/#icerik)

## You may also find these useful

[Excel Automation: Which Job Needs Formulas, Power Query, VBA, Office Scripts or a Web App?Formulas, Power Query, VBA, Office Scripts or a web app: which one fits your repeating Excel job? A four-question decision table and a worked sample month.](https://oguzerol.com/en/excel-automation/)[Excel Macro (VBA) Development: Which Jobs Suit a Macro and What Makes One RobustIs your repetitive Excel job a good fit for a macro? A decision table, a 12-region report example, annotated VBA code and the security settings.](https://oguzerol.com/en/excel-macro-vba-development/)[Excel Data Cleaning: Fixing a Messy Export With Formulas and Power QueryClean a messy ERP export in Excel with formulas and Power Query, then prove it with a row-count and total reconciliation. A 473-row worked example.](https://oguzerol.com/en/excel-data-cleaning/)

[Browse all articles →](https://oguzerol.com/en/articles/)