Help me analyze an Excel workbook for the decision below. First inspect the table structure I provide and identify fields, data types, grain, date range, missing values, duplicates, and likely data-quality risks. Do not assume a column means something it does not, and do not fabricate results from data I have not supplied. [ANALYSIS QUESTION] [WORKBOOK AND SHEET DETAILS] [COLUMN LIST OR SAMPLE ROWS] [METRIC DEFINITIONS] [FILTERS AND BUSINESS RULES] [EXCEL VERSION OR TOOL] Return five sections. 1. Analysis plan: define the unit of analysis, numerator and denominator for each metric, required filters, and comparison period. 2. Data checks: list exact checks to run, including duplicate keys, blank dates, text stored as numbers, inconsistent categories, divide-by-zero risk, and totals that should reconcile. 3. Excel build instructions: give either exact formulas with cell/table references or PivotTable and Power Query steps, based on my tool. Use structured references when I provide an Excel Table name. For formulas, explain where to place them and how to fill them down. 4. Results layout: specify the summary table, chart type only if it answers the question, and the three most useful cuts of the data. 5. Interpretation template: explain what would count as a meaningful finding, what cannot be concluded, and what to investigate next. If I provide data, calculate only what can be calculated and show the arithmetic or formula logic. If the analysis involves forecasts, projections, or financial decisions, separate historical facts from assumptions and recommend appropriate professional review. Before answering, self-check that formulas use the right denominator, ranges include all intended rows, and dates are treated as dates. Ask up to 3 clarifying questions only if a required input is missing.
Fill in
| Placeholder | What to enter | Example |
|---|---|---|
| [ANALYSIS QUESTION] | State the decision or business question the workbook should answer. | Which service category has the strongest repeat purchase rate, and has it changed versus last quarter? |
| [WORKBOOK AND SHEET DETAILS] | Name the relevant sheets, table names, header rows, and approximate data size. | Sheet Orders has an Excel Table named tblOrders with 18,400 rows; Sheet Lookup maps service codes to categories. |
| [COLUMN LIST OR SAMPLE ROWS] | Paste column names and a small redacted sample that shows formats and values. | OrderID, CustomerID, OrderDate, ServiceCode, NetRevenue; sample: 10482, C-188, 2026-07-04, CLN, 145.00. |
| [METRIC DEFINITIONS] | Define each metric, including what counts in its numerator and denominator. | Repeat purchase rate = customers with two or more completed orders in a category divided by customers with at least one completed order in that category. |
| [FILTERS AND BUSINESS RULES] | List exclusions, date rules, category mappings, and other rules the analysis must follow. | Exclude refunded orders and internal test customers; compare Q2 2026 with Q3 2026 to date. |
| [EXCEL VERSION OR TOOL] | State whether you are using Excel Copilot, desktop Excel, Excel for web, Power Query, or another tool. | Microsoft 365 desktop Excel with Power Query available. |
How to use
- Paste the headers and a few representative rows before asking for formulas; redact names, emails, and account numbers.
- Define the metric in business terms so the formula has a defensible numerator and denominator.
- Run the suggested quality checks before accepting a chart or summary table.
- Follow up with: “Convert the repeat-purchase calculation into a Power Query workflow that preserves the original tblOrders table.”
Variations
Formula troubleshooting
Use this when an Excel formula returns an error or a result you do not trust.
Diagnose this Excel formula problem using [FORMULA], [ERROR OR WRONG RESULT], [COLUMN HEADERS], [SAMPLE VALUES], and [EXPECTED LOGIC]. Explain the most likely cause in plain English, then provide a corrected formula and two alternatives if appropriate. Specify whether references should be absolute, relative, or structured. Include a small worked example using my sample values. Do not assume blanks, dates, or text numbers behave correctly without checking. Self-check parentheses, range sizes, criteria syntax, and denominator handling. Ask up to 3 questions only if required context is missing.
PivotTable report
Use this when you need a repeatable summary for a manager or recurring meeting.
Design an Excel PivotTable report for [BUSINESS QUESTION], using [TABLE NAME AND COLUMNS], [METRIC DEFINITIONS], [REQUIRED FILTERS], and [AUDIENCE]. Give exact field placement for Rows, Columns, Values, Filters, slicers, and any calculated fields or helper columns needed. Include refresh steps and a compact dashboard layout with only charts that answer the decision question. Explain how to validate grand totals against the source table. Do not calculate metrics from unsupported assumptions. Self-check aggregation type, distinct-count requirements, and date grouping. Ask up to 3 questions only if a required input is missing.
Data cleaning plan
Use this before analysis when exports are messy or multiple files need combining.
Create a Power Query data-cleaning plan for [SOURCE FILES], [SHEET OR TABLE DETAILS], [KEY COLUMNS], [KNOWN ISSUES], and [FINAL ANALYSIS NEED]. List transformations in order: import, type assignment, trimming, normalization, null handling, duplicate handling, joins, error checks, and load destination. For each step, state what it changes and how to test it. Preserve raw data and never silently delete records; recommend an exception table for rejected rows. Include M code only if I supply the exact column names. Self-check that joins will not multiply rows unexpectedly. Ask up to 3 questions only if required inputs are missing.
Tips
- Write the business definition of a rate before asking for a formula; the same label can mean orders, customers, dollars, or active accounts.
- Check totals before and after filters, joins, or Power Query merges, because row multiplication is a common source of convincing but wrong output.
- Use a helper column or Power Query transformation for messy dates and categories rather than hiding complexity inside one long formula.
- For charts, show the denominator or sample size near percentage comparisons so a small group does not look more important than it is.
FAQ
Can Copilot analyze an Excel workbook accurately on its own?
It can suggest useful steps, but you should verify table scope, metric definitions, formulas, and reconciliations against the source data.
Should I use formulas, PivotTables, or Power Query?
Use formulas for visible row-level calculations, PivotTables for interactive summaries, and Power Query for repeatable cleaning or combining data.
Can I paste confidential spreadsheet data into an AI tool?
Follow your organization’s approved data-handling policy and remove sensitive personal, financial, and customer information unless the tool is authorized for it.