20 Excel Copilot Prompt Patterns — Summary, Classification, Formulas, and Insights Templates (5 Shots)

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

ItemKey Point
FrameworkWrite prompts using Goal + Context + Expectation + Source (data/range). :contentReference[oaicite:7]{index=7}
=COPILOT tipsSupports table references and spill ranges and can be combined with other formulas, such as IF and LAMBDA. :contentReference[oaicite:8]{index=8}
RequirementsAutoSave on, a table or supported range, and verified permissions/EDP. :contentReference[oaicite:9]{index=9}
Error handlingFor 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

  1. Goal: “Classify return reasons into 5 groups in a single column.”
  2. Context: “Text in Orders[Return_Reason], 2–10 words.”
  3. Expectation: “Use only values from Params[Category] for group labels.”
  4. 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

  1. =COPILOT("Summarize in 3 bullets, focus on logistics delays:", Orders[Return_Reason])
  2. =COPILOT("Create a one-paragraph weekly summary for management.", Orders[Return_Reason])
  3. =COPILOT("Extract top 5 themes with counts, return as two-column table (Theme, Count).", Orders[Return_Reason])
  4. =COPILOT("Rewrite this as a KPI note under 100 words, neutral tone.", Notes[Text])

Classification 5–8

  1. =COPILOT("Classify each reason using only these labels:", Orders[Return_Reason], "Labels:", Params[Category])
  2. =COPILOT("Tag each SKU as A/B/C based on examples:", Facts[Sales], "Examples:", Guide[ABC])
  3. =COPILOT("Detect sentiment as Positive/Neutral/Negative", CSAT[Comment])
  4. =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}

  1. =COPILOT("Propose a formula to extract 3-char SKU prefix", Orders[SKU]) → After reviewing, apply LEFT([@SKU],3)
  2. =COPILOT("Generate a formula to split 'City, State' into two columns", Addr[CityState]) (alternative: TEXTBEFORE/TEXTAFTER)
  3. =COPILOT("Give a single-cell formula to count unique SKUs", Orders[SKU]) (alternative: COUNTA(UNIQUE(...)))
  4. =COPILOT("Normalize date text to yyyy-mm-dd", Raw[DateText]) (alternative: DATEVALUE)
  5. =COPILOT("Create formula to flag late shipments (ship_date - order_date > 2)", Orders[[Order_Date]:[Ship_Date]])
  6. =COPILOT("Return a WRAPROWS table with top 10 SKUs and counts", Orders[SKU])
  7. =COPILOT("Draft a LAMBDA that standardizes courier names", Ship[Carrier])

Shot 5 — Patterns 16–20 (Insights, KPIs, and Quality/Error Handling)

  1. =COPILOT("Explain which 3 features best predict returns; suggest columns to engineer", Orders[[Qty]:[Return_Reason]])
  2. =COPILOT("Propose weekly KPI bullets for OTD, Return Rate, CSAT under 80 words", KPI[Notes])
  3. =COPILOT("Draft a short 'What changed vs last week?' summary", Weekly[Metrics])
  4. =COPILOT("List data quality issues found in this table (duplicates, blanks, mixed types)", Orders[#All])
  5. =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}

Note: =COPILOT is being rolled out starting with Insider/Beta channels (announced for Windows 2509+ and Mac 16.101+), and it can be used alongside existing formulas. :contentReference[oaicite:22]{index=22}

Leave a Reply

Your email address will not be published. Required fields are marked *