Summary

Shot 1 — Prerequisites and Limitations

• Saving and AutoSave are required (OneDrive/SharePoint). It does not work in unsaved local files.
• Data format: Reliability increases with tables/supported ranges. Avoid duplicate headers and merged cells.
• =COPILOT usage/errors: 100 calls per 10 minutes, #CONNECT/#VALUE, and more.
• Accuracy and reproducibility warning: Not recommended for work requiring accuracy and reproducibility, such as finance or legal work.

Shot 2 — Defining the Boundary (Decision Matrix)

Define the boundary using determinism (the same input always produces the same output) and reproducibility (recorded and repeatable steps) as the key criteria.

• Copilot: May be nondeterministic (variations can occur even with the same prompt) → best for drafts, ideas, and explanations.
• Formulas: Deterministic, traceable at the cell level, and easy to audit → essential for official KPIs and reports.
• Power Query: Click and code steps are permanently recorded, making it well suited for reproducibility and large datasets.

Related tutorials: Complete XLOOKUP Guide · Power Query Basics · Print and Export Reports

Shot 3 — Same Problem, Different Solutions (Side-by-Side Comparison)

Problem A: Summarizing Return Reasons

1. Copilot (exploratory)
   =COPILOT("Summarize top 3 themes:", Orders[Return_Reason])
2. Formulas (deterministic keyword extraction)
   =LET(t,LOWER(Orders[Return_Reason]), UNIQUE(TOCOL(TEXTSPLIT(t," "),1)))
3. Power Query (reproducible preprocessing and frequency table)
   Transformation steps: lowercase → split by spaces → remove stop words → group by (CountRows).

Problem B: Classifying Reasons Into 5 Groups

1. Copilot
   =COPILOT("Classify with only Params[Category] labels:", Orders[Return_Reason], "Labels:", Params[Category])
2. Formulas (deterministic mapping)
   =XLOOKUP([@Return_Reason], Mapping[Key], Mapping[Group], "Other", 1)
3. Power Query (merge and reapply a rules table)

Problem C: Auto-Filling Formulas

Select “Suggest column formulas” in the Copilot pane to insert formulas along with an explanation. If needed, use View explanation to verify the logic.

Shot 4 — Validation and Tracking (Practical Workflow)

1. Validate a sample: Create an answer key for a 30-row sample, then compare it with Copilot output for accuracy.
2. Freeze values: Before including results in a report, use Paste Values to prevent variation.
3. Handle usage limits and errors: If you exceed 100 calls in 10 minutes and receive #CONNECT, wait or reduce the range. Complex prompts can cause #VALUE, so simplify or split them.
4. For work requiring accuracy and reproducibility, reproduce Copilot drafts with formulas/Power Query before distribution. There has been an official warning against using Copilot directly for tasks that require accuracy and reproducibility.

Shot 5 — Deployment Checklist (One-Page Decision Guide)

• Is this work deterministic or subject to audit? → Yes: use formulas/Power Query.
• Exploration or writing? → Create a draft with Copilot, then review it.
• Repeated ETL? → Build a Power Query pipeline.
• Check EDP/security (commercial customer data is not used for training).
• Check AutoSave/tables/errors (OneDrive/SharePoint, 100 calls per 10 minutes).

Sample Data (Orders/Returns)

Orders table: Order_ID, Date, SKU, Qty, Return_Reason, Channel

Params: Category (Size, Defect, ChangeMind, LateDelivery, Other)

Related Tutorials (ExcelJump Internal Links)

• Complete XLOOKUP Guide
• Power Query Basics: Automate Repeated ETL
• Print and Export Excel Reports