
20 Excel Copilot Prompt Patterns — Summary, Classification, Formulas, and Insights Templates (5 Shots)
Copilot can summarize and classify data in Excel, and help suggest formulas and generate insights through the =COPILOT function or pane. Before using it, save the file to OneDrive or SharePoint, turn on AutoSave, and ensure your data is formatted as a table or supported range. Organizational users should review Enterprise Data Protection (EDP) policies. :contentReference[oaicite:6]{index=6}
Key Takeaways
| Item | Key Point |
|---|---|
| Framework | Write prompts using Goal + Context + Expectation + Source (data/range). :contentReference[oaicite:7]{index=7} |
| =COPILOT tips | Supports table references and spill ranges and can be combined with other formulas, such as IF and LAMBDA. :contentReference[oaicite:8]{index=8} |
| Requirements | AutoSave on, a table or supported range, and verified permissions/EDP. :contentReference[oaicite:9]{index=9} |
| Error handling | For errors such as #CONNECT and #VALUE, simplify the prompt or reduce the range based on the error message. :contentReference[oaicite:10]{index=10} |
Shot 1 — Environment Setup (Required Checklist)
- File: Save to OneDrive or SharePoint and turn AutoSave ON. :contentReference[oaicite:11]{index=11}
- Data: Convert to an Excel table (unique headers, no merged cells, and no blank rows). :contentReference[oaicite:12]{index=12}
- Permissions and security: Commercial customer data is processed, encrypted, and tenant-isolated within the Microsoft 365 boundary. :contentReference[oaicite:13]{index=13}
Standardizing table cleanup and ETL first with Power Query Basics improves Copilot performance and reproducibility.
Shot 2 — The Four-Part Prompt Framework
- Goal: “Classify return reasons into 5 groups in a single column.”
- Context: “Text in Orders[Return_Reason], 2–10 words.”
- Expectation: “Use only values from Params[Category] for group labels.”
- Source: Specify the reference range (table/column). :contentReference[oaicite:14]{index=14}
Also see the XLOOKUP Guide and Print and Report Settings.
Shot 3 — Patterns 1–8 (Summary, Classification, and Formatting)
Summary 1–4
=COPILOT("Summarize in 3 bullets, focus on logistics delays:", Orders[Return_Reason])=COPILOT("Create a one-paragraph weekly summary for management.", Orders[Return_Reason])=COPILOT("Extract top 5 themes with counts, return as two-column table (Theme, Count).", Orders[Return_Reason])=COPILOT("Rewrite this as a KPI note under 100 words, neutral tone.", Notes[Text])
Classification 5–8
=COPILOT("Classify each reason using only these labels:", Orders[Return_Reason], "Labels:", Params[Category])=COPILOT("Tag each SKU as A/B/C based on examples:", Facts[Sales], "Examples:", Guide[ABC])=COPILOT("Detect sentiment as Positive/Neutral/Negative", CSAT[Comment])=COPILOT("Map courier names to standardized values", Ship[Carrier])
※ Results may expand as a spilled array. :contentReference[oaicite:15]{index=15}
Shot 4 — Patterns 9–15 (Formulas and Transformations)
Copilot can generate formulas for new columns or rows to fill a table, and it can be combined with existing formulas. :contentReference[oaicite:16]{index=16}
=COPILOT("Propose a formula to extract 3-char SKU prefix", Orders[SKU])→ After reviewing, applyLEFT([@SKU],3)=COPILOT("Generate a formula to split 'City, State' into two columns", Addr[CityState])(alternative:TEXTBEFORE/TEXTAFTER)=COPILOT("Give a single-cell formula to count unique SKUs", Orders[SKU])(alternative:COUNTA(UNIQUE(...)))=COPILOT("Normalize date text to yyyy-mm-dd", Raw[DateText])(alternative:DATEVALUE)=COPILOT("Create formula to flag late shipments (ship_date - order_date > 2)", Orders[[Order_Date]:[Ship_Date]])=COPILOT("Return a WRAPROWS table with top 10 SKUs and counts", Orders[SKU])=COPILOT("Draft a LAMBDA that standardizes courier names", Ship[Carrier])
Shot 5 — Patterns 16–20 (Insights, KPIs, and Quality/Error Handling)
=COPILOT("Explain which 3 features best predict returns; suggest columns to engineer", Orders[[Qty]:[Return_Reason]])=COPILOT("Propose weekly KPI bullets for OTD, Return Rate, CSAT under 80 words", KPI[Notes])=COPILOT("Draft a short 'What changed vs last week?' summary", Weekly[Metrics])=COPILOT("List data quality issues found in this table (duplicates, blanks, mixed types)", Orders[#All])=COPILOT("If #CONNECT or #VALUE occurs, suggest how to simplify/resize prompt to resolve", Help[Msg])— Reference: Error Guide. :contentReference[oaicite:17]{index=17}
For sensitive documents or metrics that must be accurate and reproducible, reproduce Copilot suggestions through human review → native formulas/Power Query. :contentReference[oaicite:18]{index=18}
One-Page Guide for Workplace Rollout
- Framework: Goal, Context, Expectation, and Source (keep wording brief and specific). :contentReference[oaicite:19]{index=19}
- Setup: AutoSave, tables, permissions/EDP, and sample validation. :contentReference[oaicite:20]{index=20}
- 20 patterns: 4 summaries, 4 classifications, 7 formulas, and 5 insights (copy-and-paste guide).
- Errors: #CONNECT, #VALUE → Simplify the prompt, reduce the range, or reduce document size. :contentReference[oaicite:21]{index=21}