Skip to main content
← Back to course

MCM-XL-1.1

Turn a Spreadsheet into an Executive Dashboard

This video uses AI-generated narration.

Most Copilot demos start with a blank sheet. This one starts with something you probably have: a 6-deal sales pipeline tracked by hand, and a single plain-English request to make sense of it.

The request: "Summarize this data using PivotTables or charts." No macro, no formula, no VLOOKUP.

What Copilot does, step by step:

  • Reads the existing table and works out what it's for on its own.
  • Plans before it touches anything: what to group by, what to calculate, what story the numbers tell. You can watch it reason, and you can ask it to explain its reasoning afterward. In this lesson it lays out all 13 steps it took.
  • Builds a new sheet instead of cluttering your existing one: a "Pipeline Summary" with four live KPI tiles (Total Pipeline, Weighted Pipeline, Average Probability, Open Opportunities), two PivotTables, and two charts.
  • Uses real formulas for the KPIs (=SUM(SalesPipeline[Deal Value]), not typed-in numbers), so the dashboard updates itself the next time the data changes.

Why this matters more than a basic "build me a table" demo: everything above happened in a live Microsoft 365 session; nothing was staged or mocked up afterward. You're watching the product do the work, not a slide describing what it could do.

Before you try it: the setup that works everywhere

Copilot in Excel works on Windows, Mac, and the web. Three habits prevent most "Copilot is grayed out" or "Copilot can't see my data" problems:

  1. Save the workbook as .xlsx to OneDrive or SharePoint and keep AutoSave on. Microsoft has long required this. Some newer builds are more relaxed, but a cloud-saved file works in every version and also gives you version history to roll back to.
  2. Put your data in a Table (click inside it and press Ctrl+T). A Table gives Copilot clear headers and boundaries, and its formulas use readable names like SalesPipeline[Deal Value] instead of D2:D7.
  3. Leave calculation on Automatic (Formulas → Calculation Options). Microsoft lists this as a requirement for Copilot to edit your workbook.

Copilot can work in three ways. Chat answers questions without changing anything. Plan proposes the steps and waits for your approval. Edit works directly in the workbook; Microsoft first called this Agent Mode. Edits Copilot makes are highlighted on the grid, and changed sheets get a marker on their tab, so you can see exactly what it touched.

A stronger version of the demo prompt

The demo prompt works because the data is small and tidy. On real workbooks, a vague request makes Copilot guess at your priorities. This version tells it what to build, which formulas to use, and what to check before it starts. Change the column names to match yours.

Engineered prompt
Using the SalesPipeline table on this sheet, build a pipeline summary on a new sheet named Pipeline Summary. Do not change the original table.

1. KPI cells, each a live formula that references the table (no typed numbers): Total Pipeline (sum of Deal Value), Weighted Pipeline (Deal Value multiplied by Probability, summed), Average Probability, and Open Opportunities (count of deals not marked Closed Won or Closed Lost).
2. A PivotTable of Deal Value by Stage, with stages in pipeline order, not alphabetical.
3. A bar chart of Weighted Pipeline by Sales Rep, largest first.

Before you build anything, list the columns you will use for each item and any assumptions you are making, such as how you are treating closed deals. Then build it.

Why it's better: "Weighted Pipeline" is defined, so Copilot can't quietly use an average instead of a SUMPRODUCT. "Open" is defined, so closed deals don't inflate the count. Asking for assumptions before the build is the cheapest quality check there is: if Copilot misreads a column, you find out in one sentence instead of after it builds four objects.

Current limitations· as of November 2026
  • Small, clean data flatters every demo. Six tidy rows is the best case. On thousands of rows with inconsistent labels, expect Copilot to need explicit column names and definitions, and expect to check its work.
  • It decides what "summarize" means. Without guidance, Copilot picks the groupings and charts it finds interesting, which may not be the ones your leadership team asks about.
  • Results vary run to run. The same prompt can produce a different layout, chart type, or sheet name next time. If you need a repeatable report, save the workbook it built, not just the prompt.
  • Availability depends on your license and admin settings. Business plans that include Copilot Chat get a basic version of Copilot in Excel; the paid Microsoft 365 Copilot license adds priority access and extras such as choosing the AI model. What you see can differ from this video.
  • Very large data is a different job. Microsoft's guidance has cited roughly two million cells as the ceiling for Copilot's analysis (sort, filter, highlight, and formula columns aren't bound by it). Past a few hundred thousand rows, summarize in Power Query or a PivotTable first.

▶️ Try this

Pick a spreadsheet you already track by hand, open Copilot, and ask the same question: "Summarize this data using PivotTables or charts." Then run the stronger prompt above, adapted to your columns. Compare the two results, and notice what the second one got right that the first one guessed at.

Key takeaways
  • Copilot can build a full summary sheet (KPIs, PivotTables, charts) from one request, and it uses live formulas so the summary stays current.
  • Save to OneDrive or SharePoint, format as a Table, and keep calculation on Automatic. Those three habits prevent most setup problems.
  • Define the metrics you care about, and ask Copilot to state its assumptions before it builds.
  • Check the KPI formulas yourself. A dashboard that looks polished can still be summing the wrong column.