2.3Copilot-generated formulas
The essentials in 30 seconds
- Describe, don't code: exact columns + logic + expected result in English — precise English makes an accurate formula.
- Systematic “Explain”: have every generated formula detailed — Copilot as tutor, not dispenser.
- Test on a known case: one row with a mastered result before adopting — plausible does not mean correct.
Running mini-case — Hicham and the commission column
Hicham must add a commission column to “Ventes2025”: 5% above 10 000 DZD, 3% below. Instead of looking up the IF syntax, he describes the calculation with the exact column names; Copilot proposes =SI([@Montant]>10000;[@Montant]*0,05;[@Montant]*0,03) as a calculated column, with an explanation. Hicham tests on a row at exactly 10 000 — strict or inclusive threshold? — then has an old formula inherited from a predecessor explained. In 2.4, this column will feed the chart; in 2.5, Python will reuse it for statistics.
Continuation of the 2.2 running thread: one of Hicham's observations becomes an understood and tested formula — and continues in 2.4 and 2.5.
1. Describe the need, not the syntax
The revolution: you describe the calculation in English, Copilot writes the formula. “A column that shows YES if the amount exceeds 1000 and the region is North” — and it proposes the correct IF/AND. No need to memorise the syntax. Copilot adds a clean calculated column to the Table, often with the adapted modern function (LET, XLOOKUP, FILTER…) that many users do not know. Hicham thus levels up on formulas without learning them by heart.
2. The explanation that teaches
Key reflex: always ask “explain this formula”. Copilot details each part. You do not just get a result, you learn — the AI becomes a permanent Excel tutor. Facing an existing formula you do not understand, the reverse also works: paste it and ask “what does this formula do?”. Hicham thus translates his predecessor's inherited formula into clear English, line by line.
3. Verify on a known case
Absolute safeguard: test the formula on a row whose result you know. A formula can be plausible and wrong (wrong range, inverted condition). A single check on a mastered case protects you. And for debugging: paste a formula in error (#REF!, #VALUE!) and ask “why this error and how to fix it?”. Copilot diagnoses and proposes the fix — Hicham's built-in Excel troubleshooter.
🔎 Concrete example — 🔎 Concrete example — A commission column described in one sentence
Hicham wants 5% above 10 000, 3% below, per salesperson:
Prompt: “Add a Commission column: 5% of the amount if Montant > 10000, else 3%. Explain the formula.”
→ Copilot proposes: =SI([@Montant]>10000;[@Montant]*0,05;[@Montant]*0,03)Reading: the line-by-line explanation teaches the logic of the IF.
Lesson: check on a row at exactly 10 000 — is the threshold strict or inclusive? Adjust if needed.
Section vocabulary
- Natural-language formula
- Describe the calculation in English with the exact column names; Copilot writes the matching Excel formula (IF, AND, XLOOKUP…).
Simple example: Hicham describes his commission and receives the correct IF.
What it is not: syntax learned by heart before calculating.
Reflex: need = column names + logic.
- Calculated column
- New column of the Table where Copilot inserts the formula, applied to all rows, often with a modern function.
Simple example: Hicham's commission extends on its own to each row.
What it is not: a formula copied by hand row by row.
Reflex: Copilot formula = Table column.
- “Explain this formula”
- Ask for the piece-by-piece detail: turns Copilot into a tutor and reveals logic errors before adoption.
Simple example: Hicham has his predecessor's inherited formula explained.
What it is not: a formula adopted without being understood.
Reflex: generated or inherited = explained.
- Test on a known case
- Check the formula on a row whose expected result you know, edge case included: the absolute safeguard.
Simple example: Hicham tests the row at exactly 10 000 to decide > versus >=.
What it is not: adoption on plausibility alone.
Reflex: adopt = test first.
In practice — In practice — Generate, understand and make a formula reliable
- On Hicham's “Ventes2025” Table (or yours), describe in English a real calculation (condition, threshold, category).
- Let Copilot propose the calculated column, then ask “explain this formula”.
- Test the result on at least one row whose expected value you know, edge case included.
- Debugging bonus: paste a #REF!/#VALUE! formula and ask for the diagnosis and the fix.
Ready-to-say script — justify testing before adopting (30 s)
“For Hicham's commission, Copilot proposed the IF in ten seconds — but we did not adopt it as is. We asked “explain”, then tested on three rows including one at exactly 10 000: the strict threshold excluded the tier, so we moved to >=. Plausible does not mean correct: describe, explain, test — in that order.”
Say while showing the formula, the explanation and the test row.
Copy-paste template — 4 valid formulas to adapt
=SI([@Montant]>10000;[@Montant]*0,05;[@Montant]*0,03)
=SI(ET([@Montant]>1000;[@Region]="Nord");"OUI";"NON")
=SOMME.SI.ENS(Ventes2025[Montant];Ventes2025[Region];"Nord";Ventes2025[Mois];"Mai")
=RECHERCHEX([@Commercial];Ventes2025[Commercial];Ventes2025[Region])Simple segmentation
Hicham classifies: =SI([@Montant]>500;"Grosse vente";"Standard") — tested on two known rows.
Double condition
“YES if Montant > 1000 AND Region = North”: the AND described in English, verified row by row.
Conditional total
Northern turnover in May via SOMME.SI.ENS: Hicham has each criterion explained before keeping it.
Inherited file
A formula from the predecessor pasted to Copilot: “what does it do, line by line?” then test.
✓ Best practices
- Systematically ask “explain this formula”: you progress instead of depending on the AI.
- Use Copilot as a troubleshooter: paste an error (#REF!, #VALUE!) and ask for the cause and the fix.
- Let Copilot introduce you to modern functions (XLOOKUP, LET, FILTER) more robust than VLOOKUP.
✗ Common pitfalls
- Reusing a formula without testing it on a known case: plausible does not mean correct.
- Never asking for the explanation: you stay dependent and miss logic errors.
- Ignoring threshold subtleties (strict vs inclusive): a > condition instead of >= changes the result.
Key takeaways
- Describe the calculation in English: Copilot writes the formula (IF, AND, XLOOKUP…) — no need for the syntax.
- Always ask “explain this formula”: Copilot becomes a permanent Excel tutor.
- Test the formula on a row with a known result: a plausible formula can be wrong.
- Copilot also debugs: paste an error (#REF!) and ask for the cause and the fix.
48h challenge — apply tomorrow
On your real Table: generate 1 calculated column described in English, have it explained, test it on 3 known rows including 1 edge case, and have 1 inherited formula explained.
- I can describe a calculation with the exact column names.
- I know how to have any formula explained (generated or inherited).
- I have tested each formula on a known case before adoption.
Frequently asked questions
Does Copilot write formulas in the old style (VLOOKUP) or the modern one (XLOOKUP)?
It often favours modern functions (XLOOKUP, FILTER, LET), which are more robust. You can explicitly ask it for a version compatible with an older Excel edition.
Can I have a formula written by someone else explained?
Yes, that is one of the best uses: paste it and ask “what does this formula do, line by line?”. Copilot translates it into clear English.
Which reflex makes a Copilot-generated formula reliable?