Help me design and build an Excel dashboard from this workbook context. Business question and users: [DECISIONS AND AUDIENCE] Source tables or sheets, including column names and grain: [SOURCE DATA] Metrics, definitions, targets, and reporting period: [METRICS AND RULES] Excel version and constraints: [EXCEL ENVIRONMENT] Current problem or desired layout: [CURRENT STATE] First, identify the data model: name the fact table, lookup tables, unique record key, date field, and the level of detail of each row. Point out any metric that cannot be calculated reliably from the fields provided. Then propose a one-screen dashboard with a KPI row, no more than three decision-useful charts, and filters that match the audience’s real questions. Choose chart types deliberately; do not use pie charts for comparisons or dual axes unless the scales are genuinely comparable.
Provide a build plan in this exact format: 1. Assumptions and data-quality risks. 2. Recommended table names, helper columns, and relationships or Power Query steps. 3. Exact Excel formulas using structured references, with a one-sentence explanation of each. Use SUMIFS, COUNTIFS, XLOOKUP, PivotTables, Power Pivot, or Power Query only where appropriate. 4. Dashboard layout, chart fields, slicers, and number formats. 5. A five-item reconciliation checklist comparing dashboard KPIs with source totals. 6. Plain-English instructions for a non-technical colleague. Do not fabricate column names, targets, formulas, or refresh behavior. Mark assumptions clearly. Before answering, check that each formula matches the stated row grain and that percentages have a stated numerator, denominator, and treatment for zero denominators. Ask up to 3 clarifying questions only if a required input is missing.
Fill in
| Placeholder | What to enter | Example |
|---|---|---|
| [DECISIONS AND AUDIENCE] | Describe who will use the dashboard and the decisions they need to make. | The regional sales director needs a weekly view of pipeline health by territory and owner, and needs to spot coverage gaps before month end. |
| [SOURCE DATA] | List sheets or tables, their columns, and what one row represents in each. | Table Deals: DealID, CreatedDate, CloseDate, Territory, Owner, Stage, Amount, IsClosedWon; one row per opportunity. Table Targets: Month, Territory, RevenueTarget; one row per territory-month. |
| [METRICS AND RULES] | Define the metrics, targets, date range, and any exclusions or business rules. | Show open pipeline, closed-won revenue, win rate, pipeline coverage, and average deal size for the current month. Coverage is open pipeline divided by monthly revenue target. Exclude test opportunities where Owner is blank. |
| [EXCEL ENVIRONMENT] | State your Excel version and whether you can use Power Query, Power Pivot, or external connections. | Microsoft 365 desktop; Power Query and PivotTables are allowed, but no Power Pivot. |
| [CURRENT STATE] | Describe the existing workbook or the layout and issue you want to solve. | Deals are pasted into a sheet every Monday. The current dashboard uses manual SUMIF formulas and a pie chart by territory. |
How to use
- Paste the table names, column headers, and the meaning of one row for every source; this prevents formulas from mixing deals, invoices, and targets.
- Define each metric in words before asking for formulas, especially date rules and exclusions.
- Build the helper columns or Power Query cleanup first, then reconcile each KPI against a filtered source-table total.
- Follow up with: “Write the exact formulas for the KPI row using the table names above and a selected month in Dashboard!B2.”
Variations
Fix a formula
Use this when a dashboard formula returns an error or the wrong total.
Diagnose this Excel dashboard formula: [FORMULA]. It is intended to calculate [METRIC] from [TABLE AND COLUMNS]. One source row represents [ROW GRAIN], and the expected result for this test case is [EXPECTED RESULT]. The actual result is [ACTUAL RESULT]. Explain the likely cause, provide a corrected formula using structured references where possible, and give two test rows that would expose a duplicate, blank, or date-boundary error. Do not assume fields I did not list. Check that the numerator and denominator use the same filters. Ask up to 3 questions only if required information is missing.
Executive dashboard
Use this for a leadership view focused on decisions rather than operational detail.
Design an Excel executive dashboard for [AUDIENCE] to monitor [BUSINESS GOAL]. The available data is [SOURCE TABLES], and the reporting cadence is [CADENCE]. Recommend up to five KPIs, three charts, and the exact questions each answers. Separate leading indicators from lagging outcomes and identify which metric needs a benchmark or target. Give a one-screen wireframe in text, data-refresh ownership, and validation checks. Avoid vanity metrics and charts that require hovering to understand. Do not invent targets or data fields. Ask up to 3 questions only if required information is missing.
Power Query cleanup
Use this when pasted exports need reliable preparation before reporting.
Create a Power Query cleanup plan for this Excel export: [COLUMNS AND SAMPLE ISSUES]. The destination table should have one row per [ROW GRAIN] and support [DASHBOARD USE]. Specify the transformations in order, including data types, trimmed text, null handling, duplicate rules, date parsing, and joins to [LOOKUP TABLES]. Provide M code only for transformations supported by the information given, with comments for each step. Include a refresh and exception-check procedure. Do not silently delete records; flag ambiguous duplicates. Ask up to 3 questions only if required information is missing.
Tips
- Write down the grain of every source table before creating any formula; a one-row-per-deal table and a one-row-per-product-line table cannot be summed interchangeably.
- Put business definitions beside the dashboard, especially the date basis for pipeline, revenue, and conversion rates.
- Use a reconciliation cell during development that compares dashboard totals with source totals under the same filters; hide it only after it passes.
- Keep slicers limited to dimensions users can act on, such as period, territory, or owner, rather than exposing every available field.
FAQ
Can Copilot create the dashboard directly in Excel?
It can help create elements depending on your version and permissions, but you should still validate formulas, chart ranges, and refresh behavior against the source data.
Should I use formulas or PivotTables?
Use PivotTables for flexible grouped summaries and formulas for stable KPI cells or calculations that need to react to dashboard selections. Power Query is usually the right first step for recurring cleanup.
Why do dashboard totals differ from the source export?
Common causes are duplicate records, mismatched date fields, hidden filters, blank categories, and totals calculated at different levels of detail.