Build a dynamic Excel dashboard from my raw data
Plans a refreshable one-page Excel dashboard for management, with tab layout, KPIs, pivot charts, slicers, and the formulas behind them.
The prompt
Help me build a dynamic management dashboard in Excel from my raw data.
Raw data: [PASTE headers and 20 sample rows, plus the total row count and date range]
Who will use it and what they decide: [e.g. "sales director, reviews weekly to reallocate reps"]
Questions it must answer, in priority order: [e.g. "are we on target this month?", "which region is slipping?"]
Targets or budget available: [yes - paste / no]
How the data gets updated: [e.g. "new export pasted each Monday"]
My tool: [Excel 365 / older Excel]
Design:
1. Workbook structure: Data tab (formatted as an Excel Table so new rows flow through), Lookups/Targets tab, Calculations tab, Dashboard tab. Explain why each is separate.
2. KPIs: 4-6 headline numbers with exact definitions and the formula for each (SUMIFS, COUNTIFS, AVERAGEIFS, or dynamic arrays), each compared with target and prior period.
3. Visuals: for each question, the chart type and why, built from a pivot table or a formula range. No 3D, no pie charts with many slices, no dual axes unless essential.
4. Interactivity: slicers or a timeline connected to all pivots, or a dropdown cell driving the formulas; default view on open.
5. Layout: a grid sketch of the one-page dashboard, reading order top-left to bottom-right, with conditional formatting rules for on / off track.
6. Refresh steps: what someone does each period, in 5 steps or fewer, plus a check that totals match the raw data.
Return the structure, the KPI table (KPI | Definition | Formula | Comparison), the layout sketch, and a build checklist in order. Mark any metric definition you are inferring as [CONFIRM].
Highlighted [brackets] are blanks to replace with your own details.
Fill in the blanks and use this promptMore Data & Analysis prompts
What to fill in
- e.g. "sales director, reviews weekly to reallocate reps"
- yes - paste / no
- e.g. "new export pasted each Monday"
- Excel 365 / older Excel
- CONFIRM
How this prompt is built
This prompt follows the Few-Shot structure (Few-shot prompting). A few-shot prompt includes one or more worked examples of the input and the output you want, so the model can copy the pattern instead of guessing it.
Related prompts
- Find what my dashboard is not telling meReviews a dashboard for blind spots, missing comparisons, vanity metrics, and misleading views, and suggests what to add.
- Compare two Excel sheets and flag every differenceMatches records between two spreadsheets or lists, flags missing, extra, and changed rows, and gives formulas to reconcile them.
- Audit an Excel workbook for broken, inconsistent, and hard-coded formulasChecks your spreadsheet for formula errors, broken references, inconsistent formulas across rows, and hard-coded numbers that should be formulas.
- Explain my Excel model to a non-technical executiveTurns a complex spreadsheet or model into a plain-English walkthrough an executive can follow, trust, and question.