Module 08: Copilot in Excel Advanced + Analyst
Lesson 08.4: Building PivotTables, Charts, and Dashboards with Copilot Help
Lesson Promise
Use Copilot to design PivotTables, charts, and dashboard views that answer executive questions.
Real-World Scenario
A marketing director needs a weekly dashboard showing budget, spend, revenue, campaign type, region, and return signals.
Core Concept
A PivotTable or chart should answer a decision question. If the audience cannot tell what action the visual supports, the visual is not ready.
Copilot can help create PivotTables and charts where available, but Microsoft notes chart support does not include every chart type. Ask for chart rationale and alternatives.
Dashboard design is analysis design: choose metrics, dimensions, filters, comparisons, and warning indicators before arranging visuals.
Step-By-Step Workflow
- Name the dashboard audience and decision.
- Choose the top metrics and dimensions.
- Ask Copilot for PivotTable and chart recommendations.
- Request why each visual is useful and what it could mislead.
- Build one visual at a time and verify labels, filters, and totals.
- Write a dashboard interpretation note for the executive audience.
Prompt Lab
Bad Prompt
Make a dashboard.
Better Prompt
Suggest PivotTables and charts for a marketing dashboard comparing spend, revenue, and campaign type by region.
Expert Prompt
Design an executive marketing dashboard from this table. Recommend PivotTables, charts, filters, and warning indicators for budget, spend, revenue, campaign type, region, and ROI signal. For each visual, include the decision it supports, required fields, possible misinterpretation, and manual verification step.
Hands-On Exercise
Ask Copilot for three dashboard visuals, then build or mock one and verify its totals.
Deliverable
A dashboard plan with metrics, visuals, filters, rationale, risks, and verification checks.
Executive Analysis Review Checklist
Common Mistakes
- Turning one spreadsheet question into a confident executive recommendation without checking the data.
- Asking for forecasts without naming assumptions, time horizon, inputs, and uncertainty.
- Using charts or PivotTables that look polished but do not answer the decision question.
- Treating Analyst, Python, file upload, or advanced editing features as universally available.
- Sharing analysis that does not separate observed facts, assumptions, hypotheses, and recommendations.
Quiz / Checkpoint
What makes a dashboard executive-ready?
It answers a decision question with verified metrics, clear visuals, useful filters, and concise interpretation.
Official Sources To Verify
- Get started with Analyst in Microsoft 365 Copilot
- Get started with Copilot in Excel
- Frequently asked questions about Copilot in Excel
- Get insights about numerical data with Copilot in Excel
- Get insights from text-based data with Copilot in Excel
- Create PivotTables with Copilot in Excel
- Create charts with Copilot in Excel
- Edit with Copilot in Excel
- File formats supported by Microsoft 365 Copilot
- Copilot Chat licensing explanation
Want your AI product explained to a large AI-native audience?
Kingy AI helps AI companies turn complex products into clear, useful YouTube videos that drive awareness, product understanding, demos, clicks, and search visibility.
