Module 07: Copilot in Excel Foundations
Lesson 07.4: Creating and Explaining Formulas
Lesson Promise
Use Copilot to create and understand formulas while avoiding silent calculation errors.
Real-World Scenario
A finance assistant needs a margin, refund rate, and status column but is not confident writing formulas from scratch.
Core Concept
Copilot can help generate formula columns and explain formula logic, but formulas need careful testing because small reference errors can ripple through the workbook.
Give Copilot the exact column names, expected result, edge cases, and examples of correct output. Then ask it to explain the formula in plain English.
A formula is not finished until you test it on a normal row, a blank or zero row, an unusual row, and a row you calculate manually.
Step-By-Step Workflow
- Describe the business meaning of the formula, not just the math.
- List the exact input columns and expected output column.
- Ask Copilot for the formula plus a plain-English explanation.
- Ask for edge cases and possible failure modes.
- Test several rows manually.
- Label the new column clearly and document the formula logic.
Prompt Lab
Bad Prompt
Make a formula for profit.
Better Prompt
Create a formula column for gross margin percentage using Revenue and Cost. Explain the formula and handle zero revenue safely.
Expert Prompt
Create an Excel formula column named Gross Margin %. Use Revenue and Cost. If Revenue is blank or zero, return [CHECK] instead of dividing by zero. Explain the formula in plain English, list edge cases, and give me four test rows I should verify manually before using it in a report.
Hands-On Exercise
Create one formula column with Copilot, then ask Copilot to explain it to a non-technical teammate and list tests.
Deliverable
A formula column, formula explanation, edge-case list, and manual test log.
Excel Review Checklist
Common Mistakes
- Asking one broad analysis question and accepting the first answer.
- Using messy data with duplicate headers, blank columns, hidden assumptions, subtotals, or merged cells.
- Treating Copilot's formula, chart, or insight as correct before manual verification.
- Mixing private or regulated data into an unapproved workflow.
- Promising a feature before checking the learner's license, account, app version, file state, and tenant settings.
Quiz / Checkpoint
Why ask Copilot to explain a formula it creates?
The explanation helps you verify whether the formula logic matches the business meaning and catches edge cases before the formula is trusted.
Official Sources To Verify
- Get started with Copilot in Excel
- Format data for Copilot in Excel
- Frequently asked questions about Copilot in Excel
- Generate formula rows and columns with Copilot in Excel
- Understand formulas with Copilot in Excel
- Visualize your data with Copilot in Excel
- Get insights about numerical data with Copilot in Excel
- Edit with Copilot in Excel
- Copilot Chat in Microsoft 365 apps
- What Copilot license do I have
Want your AI product explained to a large AI-native audience?
Kingy AI helps AI companies turn complex products into clear, useful YouTube videos that drive awareness, product understanding, demos, clicks, and search visibility.
